First page Back Continue Last page Graphics
When Not to Use Bind Variables
Skewed data
- Column “x”
- 1 million rows = ‘Y’
- 100 rows = ‘N’
Histogram exists
- See “Getting to Know CBO” lesson
x = :bind_var
- Value of bind_var during hard parse dictates execution plan
- Subsequent executions may change value
- Better to use constant in this situation
Notes:
Bind variables should not be used everywhere and anywhere. Consider a situation where there is a skewed (non-uniform) distribution of values in an indexed column. For example, in indexed column X of table T, we have 1 million occurrences of character ‘Y’, yet just 100 rows containing “N”. If we need to query table T on this column, we should consider using DBMS_STATS to create a histogram (refer to the Getting to Know CBO lesson for more information on histograms). This histogram is a structure that informs CBO of the distribution of values in a column. Then, if we code a query:
select * from t where x = :bind_variable;
During hard parse, Oracle9i will peek at the contents of the variable. If it is ‘Y’, CBO will likely choose to ignore the index and use a full scan. This will be the execution plan for the life of that cursor. If subsequent executions change the value of bind_variable to ‘N’, it is too late; CBO has already chosen a full table scan and the index access will not be used.
Notes for this slide continue on the next page…