Figure 7-8. Shown is an example of a text hashing and bucket assignment function written in Perl. The hash function is the modulus (%) operator.

Figure 7-9 shows multiple runs of the hash_text.pl script. Look very closely at the differences in the SQL text. While each statement is syntactically identical, there is a single character difference in each statement. The second statement has a capital T, and the third statement simply has an additional space after the select. Based on this hash value function and the hash function, this seemingly insignificant difference was enough to produce different hash values and also different hash bucket assignments. This is why Oracle SQL must be textually exact, or a new cursor will be created for each textually unique statement. This is important for developers to understand, because unless they are told, it is highly unlikely they would assume even the statement spacing must be the same.

Figure 7-9. Shown is the result of three syntactically exact SQL statements, which yield different hash values and also hash bucket assignments.