Database Reference
In-Depth Information
-----------------------------------------------------
| Id | Operation | Name | Rows |
-----------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 9 |
| 3 | INDEX RANGE SCAN | T_PK |
9
|
-----------------------------------------------------
Whenever dealing with bind variables, the query optimizer used to ignore their values. Thus, good estimations
like in the previous examples weren't possible. To address this problem, a feature called
bind variable peeking
was
introduced in Oracle9
i
. The concept of bind variable peeking is simple. Before generating an execution plan, the
query optimizer peeks at the values of bind variables and uses them as literals. The problem with this approach is
that the generated execution plan depends on the values provided by the first execution. The following example,
which is based on the
bind_variables_peeking.sql
script, illustrates this behavior. Note that the first optimization is
performed with the value 990. Consequently, the query optimizer chooses a full table scan. It's this choice, since the
cursor is shared, that impacts the second query that uses 10 for the selection:
SQL> VARIABLE id NUMBER
SQL> EXECUTE :id :=
990
;
SQL> SELECT count(pad) FROM t WHERE id < :id;
COUNT(PAD)
----------
989
-------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS FULL| T |
990
|
-------------------------------------------
SQL> EXECUTE :id :=
10
;
SQL> SELECT count(pad) FROM t WHERE id < :id;
COUNT(PAD)
----------
9
-------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS FULL| T |
990
|
-------------------------------------------
