Re: Subquery partition pruning
- From: amonte <ax.mount@xxxxxxxxx>
- To: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- Date: Sat, 3 Apr 2010 22:54:07 +0200
Hello
It looks like this, to simplify I got partition definitions for DATE_EID =
812, the predicate I have been using to test.
select DATE_EID from dw.d_date
where DATE_NAME = TO_DATE('20100322', 'yyyymmdd')
DATE_EID
----------
812
FACT TABLE DDL:
PARTITION BY RANGE ("DATE_EID","TIME_EID")
SUBPARTITION BY HASH ("NS_ID")
SUBPARTITIONS 8
(....................
PARTITION "P_20100322_00" VALUES LESS THAN (812, 3601)
PARTITION "P_20100322_01" VALUES LESS THAN (812, 7201)
PARTITION "P_20100322_02" VALUES LESS THAN (812, 10801)
PARTITION "P_20100322_03" VALUES LESS THAN (812, 14401)
PARTITION "P_20100322_04" VALUES LESS THAN (812, 18001)
PARTITION "P_20100322_05" VALUES LESS THAN (812, 21601)
PARTITION "P_20100322_06" VALUES LESS THAN (812, 25201)
PARTITION "P_20100322_07" VALUES LESS THAN (812, 28801)
PARTITION "P_20100322_08" VALUES LESS THAN (812, 32401)
PARTITION "P_20100322_09" VALUES LESS THAN (812, 36001)
PARTITION "P_20100322_10" VALUES LESS THAN (812, 39601)
PARTITION "P_20100322_11" VALUES LESS THAN (812, 43201)
PARTITION "P_20100322_12" VALUES LESS THAN (812, 46801)
PARTITION "P_20100322_13" VALUES LESS THAN (812, 50401)
PARTITION "P_20100322_14" VALUES LESS THAN (812, 54001)
PARTITION "P_20100322_15" VALUES LESS THAN (812, 57601)
PARTITION "P_20100322_16" VALUES LESS THAN (812, 61201)
PARTITION "P_20100322_17" VALUES LESS THAN (812, 64801)
PARTITION "P_20100322_18" VALUES LESS THAN (812, 68401)
PARTITION "P_20100322_19" VALUES LESS THAN (812, 72001)
PARTITION "P_20100322_20" VALUES LESS THAN (812, 75601)
PARTITION "P_20100322_21" VALUES LESS THAN (812, 79201)
PARTITION "P_20100322_22" VALUES LESS THAN (812, 82801)
PARTITION "P_20100322_23" VALUES LESS THAN (812, 86401)
PARTITION "P_20100322_99" VALUES LESS THAN (812, MAXVALUE)
..........
)
Thank you very much
2010/4/3 Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
> 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 <ax.mount@xxxxxxxxx> 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.
>
>
> --
> Regards,
> Greg Rahn
> http://structureddata.org
>
Other related posts:
