Partition Table elimination

A short while ago there was a brief thread on newsgroup about Oracle 8.0 failing to eliminate partitions when given a query such as

    where partition_column = A
    or partition_column = B

And indeed, all partitions between A and B are examined for this query.

This alarmed me a little, since I had recently published an article eulogising partition tables for being able to handle queries like:

    where date range between (some dates last year)     or date range between (matching date this year)

This is a very common sort of query for retail datawarehouses, and partition views don't handle it.

Was I wrong about partition table ?!
I was certainly worried for a while, but after a few tests I found that the complicate query DOES work , and only the simple query fails, so: if you want partition elimination on

    where partition_column = A
    or partition_column = B

change it to read

    where partition_column between A and A     or partition_column between B and B

The difference in EXPLAIN PLAN (and all the 10046 traces) is quite significant (especially when using parallel query).

--
Jonathan Lewis Received on Fri Jan 08 1999 - 04:27:35 CST