Re: Subquery partition pruning

From: Greg Rahn <>
Date: Sat, 3 Apr 2010 13:37:36 -0700
Message-ID: <>

Can you provide some of the partition DDL clauses for the table so we can see the range of values and size for each partition? Also describing the range of values for each partition would be useful. Ffor example:

partition by range (a,b) (
partition p00 values less than (1,1),
partition p10 values less than (2,1),

What if you use the same test case as below but use a value for TIME_EID that puts it in the 2nd lowest partition for a given DATE_EID.
For example if you use 1 hour (3600s) ranges [values less than (3600)] use 3601 as the low bounds.
Do you get the same number of partitions access as the NL plan then?

On Sat, Apr 3, 2010 at 12:43 PM, amonte <> wrote:
> I did a test and noticed that partition elimination did occur. To simplify
> the test instead of using predicate TIME_EID between 1 and 86400 I tested
> with 1 and 7200 (1 and 86400 just takes too long to finish when HJ is used).
> Noticed the following: with NL hint 2 partitions are read but with HJ 61
> partitions are read. Obviously there is something wrong.

Greg Rahn
Received on Sat Apr 03 2010 - 15:37:36 CDT

Original text of this message