Sunday, July 06, 2008

Oracle and bind variable peeking

Oracle query optimizer considers the input values also while selecting the execution plan. Consider:
Select * from order where date between ?1 and ?2
the execution plan depends on the values of ?1 and ?2 while you execute the first time. It could be a index range scan if the dates are narrow or it could be a full table scan if the dates are wider. During subsequent executions even if the date range varies considerably the execution plan remain same. A side effect of execution plan re-use. This also implies that first execution has an impact on execution plan. Would it be a good practice to initialize the queries with sample values during application start up?
What will you do if you have a query which will have varying cardinality? I would recommend two options
1) if your requirement is two different scenarios one for displaying current orders (with narrow date range and smaller cardinality) and other for statistics processing program (with wider date range and larger cardinality). You may consider writing two separate SQLs
Select * from order o_small where date between ?1 and ?2
Select * from order o_large where date between ?1 and ?2
2) If you cannot predict the cardinality, Don't use bind variable
I think this feature is available from Oracle 9i
However Oracle thin driver 9.x doesn't support bind variable peeking. You need to use thin driver 10g (refer Ask Tom )

No comments: