First page Back Continue Last page Overview Graphics
Bind Variable “Peeking”…
CBO “peeks” at bind variable values on hard parse
- Provides ability to determine selectivity
Given table T
- 6000 rows
- Index on LASTNAME column
- 3000 rows LASTNAME = ‘ANDERSON’
- Bind variable B1 contains ‘ANDERSON’
Notes:
As of 9i Release 1, the Oracle cost-based optimizer will, during hard parse, interrogate the bind variables for values and use those values in optimization. This can produce a different and better plan (than it would if it did not know the value). As usual, subsequent query executions that reuse the cursor will use the same plan - there is no “peeking” on subsequent executions.
In this example, I populated the bind variable with the string ‘ANDERSON’. Since half of the rows in the table contain that string (in the LASTNAME column), we would hope that a full table scan would be performed. We can see that CBO peeked at the bind variable value and chose a full table scan. (In 8i, CBO would guess that the index would help and use it in the plan.)