Partition Pruning and Bind Variables
Posted by David Aldridge on 2006-10-28
Prompted by a question at the Dizwell Forum, here is a script to demonstrate that using bind variables in partition key predicates causes Oracle to use global (table) statistics instead of partition (or subpartition) statistics.
This is similar behaviour to Oracle’s use of global statistics in other circumstances, such as when joining on a partition key to a smaller table on which a predicate is placed. Anyway, see the forum post for other comments :D
Pete_S said
Hmm, now that is an interesting topic:
…such as when joining on a partition key to a smaller table on which a predicate is placed and worthy of further study.
Pete_S said
Another test would be to create a few more partitions (5-10) and equally populate them – this would perhaps be a touch more realistic for a DW case and see how the plan changes with binds
Partitioned tables and statistics « Pete-s random notes said
[...] Elsewhere a question popped up relating to binds on partition keys. David Aldridge ran some tests (also posted here) that indicated that binds would use global table stats rather than partition stats. Of course this is intuitive; the partition is not known at parse time and is only resolved at run time so the only sensible thing the optimiser can do is to use the global stats. But in the case of a bind be used on the partition key we would only expect 100% of the rows to be accessed to be in a single partition and not as global stats may indicate spread across many other partitions so we may well get a ‘poor’ plan. [...]
David Aldridge said
>> Hmm, now that is an interesting topic:
Ka-ching! http://oraclesponge.wordpress.com/2005/08/30/partition-pruning-and-dimension-tables/
Jonathan Lewis said
David,
I think you need to revisit your test case.
You have used the define command to set the values for your variable, but you need to say something like:
execute :l_col1 := 5
There is also the problem that explain plan treats a bind variable as an unpeekable, therefore unknown, value.
But with run-time peeking the optimizer sees the value and will generate an execution path accordingly – which can be a bit of a disaster when you run the query a second time with a different value for the bind. You probably have to check the 10053 trace to see this effect.
David Aldridge said
Oh ho — yes of course. I shall give that a run. With regard to the peeking issue, it seems to me that the predicate on the partition key must surely be flagged in some way to prevent the same exact plan being used in regard of partition pruning, as using the same pruning would likely lead to incorrect results.
Well, maybe not. If the plan stored just indicates “dynamic pruning based on the value of this bind variable” then I suppose it is not an issue. Or that is how the issue is implemented.
Moving along, I’ll just rerun the test. :(
Jonathan Lewis said
There are two different effects -
Optimization time peeking which dictates the plan.
Run-time knowledge of values which dictates the partition against which the plan is to be run. The latter including the option for not running (the bulk of) the plan at all if the incoming value is above the known high-value. There will be an example on my blog very soon.
Alex Gorbachev said
Some time ago I run into bind variable peeking with partitioning which which might be relevant – http://blog.oracloid.com/2006/07/bind-variable-peeking-with-no-histograms
Rittman Mead Consulting » Blog Archive » Partitioned tables and statistics said
[...] question popped up relating to binds on partition keys. David Aldridge ran some tests (also posted here) that indicated that binds would use global table stats rather than partition stats. Of course this [...]