Database Reference
In-Depth Information
Disadvantage
The disadvantage of using bind variables in
WHERE
clauses for performance is that, in some situations, crucial
information is hidden from the query optimizer. In fact, for the query optimizer, it's better to have literals instead
of bind variables. With literals, the query optimizer is always able to make the best possible estimations. This is
especially true for range comparison predicates (for example, predicates based on
BETWEEN
, greater-than, or less-
than comparison conditions), for checking whether a value is outside the range of available values (that is, lower
than the minimum value or higher than the maximum value stored in the column), and when histograms are used.
To illustrate, let's take a table with 1,000 rows that stores, in the
id
column, all the integer values between 1 (the
minimum value) and 1,000 (the maximum value):
SQL> SELECT count(id), count(DISTINCT id), min(id), max(id) FROM t;
COUNT(ID) COUNT(DISTINCTID) MIN(ID) MAX(ID)
---------- ----------------- ---------- ----------
1000 1000 1 1000
When a user selects all rows that have an id of less than 990, the query optimizer knows (thanks to object
statistics) that about 99% of the table is selected. Therefore, it chooses an execution plan with a full table scan. Also
notice how the estimated cardinality (
Rows
column in the execution plan) corresponds almost exactly to the number
of rows returned by the query:
SQL> SELECT count(pad) FROM t WHERE id <
990
;
COUNT(PAD)
----------
989
-------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS FULL| T |
990
|
-------------------------------------------
When another user selects all rows that have an id of less than 10, the query optimizer knows that only about 1%
of the table is selected. Therefore, it chooses an execution plan with an index scan. Also in this case, notice the very
good estimation:
SQL> SELECT count(pad) FROM t WHERE id <
10
;
COUNT(PAD)
----------
9
