Re: Bind Variables and Partition Elimination

Oracle's ability to eliminate partitions is extremely version dependent - 8.17 is far better at finding options for elimination than 8.1.5, for example, even to the extent of being able to do partition elimination on nested loop joins into the partition key.

There are two main strategies you can use to test -

1. Set event 10128, level 7 and read the information about partitions selected and partitioning strategy
2. Put partitions into tablespaces that you can put off line, and see if queries crash.

Use these strategies to test the types of query that you want to execute. Remember that execution paths can change with data volume and parallelism, and that different paths for the same query MAY NOT eliminate in the same way.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Martin Pammer wrote in message ...


>Hi all

>

>I am trying to find some solid background information on

>using bind variables and partition elimination. The sparse

>information I found by querying the web is confusing me.

>

>Some informations seem to indicate that partition elimination does not

>occur with the use of bind variables while others state the oposite.

>

>For example on

>http://www.ixora.com.au/tips/design/partitioning.htm

>the author writes:

>

>"Partition Elimination

>Another benefit of range partitioning, is automatic partition

>elimination during the formulation of query execution plans. The

>optimizer compares the WHERE clause predicates with the partitioning

>range for each partition and eliminates from the execution plan those

>partitions which can be guaranteed not to contain rows satisfying the

>query. Of course, partition elimination normally relies on not using

>bind variables, so that the optimizer can determine which partitions

>can be eliminated. However, in the case of equality predicates on the

>partitioning key, the optimizer is content in the knowledge that at

>most one partition will be required"

>

>Can someone shed some light on this issue ?

>

>With best regards

>

>Martin Pammer

>VRZ Informatik

>Dornbirn

>Austria

>m p a m m e r <at> v r z . n e t