Re: Subquery partition pruning
- From: amonte <ax.mount@xxxxxxxxx>
- To: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- Date: Mon, 5 Apr 2010 08:40:43 +0200
Hello Greg
Time Dimension is not used as often as Day Dimenion.
Time Dimension is used for reports such as looking for data in a hourly
frame for X days but not many users are using them. Most look for days.
Thank you
2010/4/5 Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
> It appears you are hitting a scenario that currently does not support
> multi-column (MC) partition elimination/pruning.
> For hash joins (HJ), MC pruning [via subquery or bloom pruning] can
> only take place if:
> 1) all partitioning columns participate in the join
> 2) partitioning columns not in the join have equality predicates on them
>
> The query provided in the OP has neither #1 or #2 (which is why the HJ
> plan performs poorly).
>
> The challenge I see with the current design and pruing behavior is
> that you can't (assuming the plan is HJ) get #1 if date and time are
> each a dimension table and it doesn't seem likely that #2 is common as
> it results in 1s granularity (assuming data is stored with seconds
> precision, not minutes, etc). Is this otherwise a good design for the
> workload (it gets partition elimination, on most other queries w/o
> hints, etc)? How frequently is the time dimension (TIME_EID) used for
> pruning inaddition to DATE_EID?
>
> If you were on 11g I would recommend LIST/RANGE for this table if it
> was desired to get pruning on both the date and time dimensions.
>
> On Sat, Apr 3, 2010 at 1:54 PM, amonte <ax.mount@xxxxxxxxx> wrote:
> > FACT TABLE DDL:
> >
> > PARTITION BY RANGE ("DATE_EID","TIME_EID") (
> > PARTITION "P_20100322_00" VALUES LESS THAN (812, 3601)
> > ...
>
>
> --
> Regards,
> Greg Rahn
> http://structureddata.org
>
Other related posts:
