PT Quirk

PT Quirk

JL Computer Consultancy

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

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

where    partition_column = A

or       partition_column = B

On the other hand, in my original article comparing partition views andpartition tables, I had pointed out the need in retail-based data warehousesfor query that, say, compared the 7 days before Christmas this year with the 7days 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 theparallel query option running , I can confirm that partitioned tables CANcope with this query despite the fact that it isapparently more complex that the first one.

Partition Views, on the other hand fail completelyto elimate any partitions at all on this query unlessit 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 Aand D values. Don't expect this to be fast though, the amount of datacomparison that takes place is large, and Oracle wastes a lot of CPU with datecomparisons (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