JL Computer Consultancy

A quirk in partition elimination

Pre- Aug 1997


This note is an addendum to an article published in Relate (the magazine of the UK Oracle User Group) in Summer 1998. If you want to see the article, the introduction is an html located at PVs or PTs.

If you execute query against a partitioned table with a predicate as follows, then ALL the partitions between A and B are examined, and proper partition elimination does not take place. (This is a 'bug' that should be fixed in 8.1 apparently, and is not a problem with partition views in Oracle 7).

    where    partition_column = A
    or       partition_column = B

On the other hand, in my original article comparing partition views and partition tables, I had pointed out the need in retail-based data warehouses for query that, say, compared the 7 days before Christmas this year with the 7 days before Christmas last year - which would require a predicate of the form:

    where    sale_date between A and B
    or       sale_date between C and D

Now, after checking it again very carefully both with and without the parallel query option running , I can confirm that partitioned tables CAN cope with this query despite the fact that it is apparently more complex that the first one.

Partition Views, on the other hand fail completely to elimate any partitions at all on this query unless it is re-written as:

    where    sale_date between A and D
    and      (sale_date <= B or sale_date >= C)

In which case elimination takes place for partitions outside the extreme A and D values. Don't expect this to be fast though, the amount of data comparison that takes place is large, and Oracle wastes a lot of CPU with date comparisons (I have heard that 8.1 or 8.2 will be introducing a 'native date' type that will make date comparisons much faster)..

Workaround for the first case - instead of writing:

    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

Back to Main Index of Topics