Partition Elimination

Database
Enthusiast

Partition Elimination

We have encountered a situation where using different phrasing in the WHERE clause of the select causes the optimizer to react differently.
When BETWEEN is used in the WHERE clause, the explain plans states:
3) We do an all-AMPs RETRIEVE step from 3101 partitions of dbname.t
with a condition of ("(dbname.t.AMC_PERIOD >= 0) AND....

When IN lists are used, the explain plan states:
3) We do an all-AMPs RETRIEVE step from all partitions of dbname.t
with a condition of ("((dbname.t.AMC_PERIOD = 0) OR ...

When using GREATER THAN/LESSER THAN comparisons, the explain plan states:
3) We do an all-AMPs RETRIEVE step from all partitions of dbname.t with a condition of ("(dbname.t.AMC_PERIOD < 3) AND ....

When running each of the queries, the CPU utilization for the second and third scenarios is the same or more than a full table scan of a non-PPI table with identical base structure and data.

QUESTION: FOR THE SAME PPI SCHEME, WHY DOES BETWEEN GIVE PARTITION ELIMINATION BUT IN LISTS OR COMPARISONS DO NOT?

1 REPLY
Enthusiast

Re: Partition Elimination

PPI is best suited for queries using Ranges(BETWEEN CLAUSE).For example , you have an table in which the PPI is defined on an column START_DATE , if your query uses WHERE START DATE BETWEEN 01-DEC-2006 to 31-DEC-2006 , the optimiser would pick only the partiotions having DEC'06 data, thereby eliminating other partitions. In the case of IN and COMPARISON operator the optimiser will have to compare all records in all partitions.