| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re: Join 2 FACT Tables partitioned on same KEY column
The idea you got from Oracle Development is not the same as my idea - they are talking about a pruning subquery that could be run relatively cheaply at parse (technically optimize) time, based on using other predicates that you have on the CORE_FACT to identify the partitions that you need to visit in the core_fact (and then assume that the same partitions would be required from the PARAM_FACT since it is a partition-wise join -- at least, that's what I've been assuming).
My idea was that they would have to do what would technically be a dynamic re-optimization after the first join - checking the partition ids generated from the first join before starting the second join.
I tried the subquery factoring method (it didn't work) and a method to generate unique list of partition identifiers as an IN list, i.e.
select from
driver
fact1
fact2
fact2.pt_col in (
list of partitioning values
from joining driver and fact1
This turned into a reasonably efficient
index-only hash semi join, and went
automatically into a nested loop on the
fact2 table using partition range iterator -
but I don't think it's what you need, as
you don't seem to like the nested loop
on the second fact table.
It's an interesting problem - and one that ought to be (a) crackable, and (b) fairly generic.
What you need at present seems to be a
table between the driver table and the
fact table that acts as a join table so
that you can reference it to find the
partitioning value related to each driver
row. In other words, a pre-join between
the driver and fact1 that contains only
the driver data and the fact1 keys
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/appearances.html Public Appearances - schedule updated June 22nd 2005
> Thanks Jonathan.
>
> You definitely know what your are talking :)
>
> This is the response I got from Oracle Development
> related to a TAR open on a similar issue (I have
> replaced the actual table name here):
>
> "The only subquery pruning optimization I can see
> (that we do not support yet) is to build a subquery
> that selects ROWID from CORE_FACT based on the filter
> predicate, then generates the partition ids using the
> rowid values. This pruning subquery will be an
> index-only and thus much cheaper."
>
> Is this same as what you have mentioned?
>
> If yes, then in your opinion do you think it is
> worthwhile to request (read push) Oracle to
> incorporate it somehow (backport, next release etc.).
> BTW, we are on 9.2.0.6
>
> In terms of size CORE_FACT is 215GB (543M Rows),
> PARAM_FACT is one of 100 such tables, averaging 10GB
> (50M Rows). All queries will 'always' join a DIM to
> CORE_FACT first, and then to one of PARAM_FACT(s).
>
> Thanks,
> Deepak
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Jun 25 2005 - 12:55:13 CDT
 |
 |