Databases Reference
In-Depth Information
Instead, when we execute the query in step 12 (for records where the value for the
ID
ield
equals 1, without the use of bind variables), we obtain the following results:
What happened? Without bind variables, we obtain a better execution plan, because the
optimizer—thanks to statistics and histograms—knows that almost all records in the
MY_TEST
table have a value of 1 in the
ID
field, and therefore, that accessing the table with an index
lookup would be a waste of time.
In previous recipes, we have repeatedly stated that not using bind variables is evil; in this
situation, we have seen that not using bind variables gives us better performance.
To clarify the situation, let's examine the final part of the recipe. For a better understanding
of what happens, we trace the execution of the queries. Therefore, in step 14, we connect as
SYSDBA
to the database, and in step 15, we prepare our session to generate a trace file.
In step 16, we execute the same query as in step 9, using bind variables; in step 17, we
execute the query as in step 10. In step 18, we stop tracing.
