Re: Subquery partition pruning
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: amonte <ax.mount@xxxxxxxxx>
- Date: Sun, 4 Apr 2010 22:21:59 -0700
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
