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

Re: Re: Join 2 FACT Tables partitioned on same KEY column

From: Jonathan Lewis <>
Date: Sat, 25 Jun 2005 11:51:44 -0500 (EST)
Message-ID: <000401c579a5$eb903b30$6902a8c0@Primary>

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



    fact2.pt_col in (

        list of partitioning values
        from joining driver and fact1

and ...

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


Jonathan Lewis The Co-operative Oracle Users' FAQ 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
> 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

Received on Sat Jun 25 2005 - 12:55:13 CDT

Original text of this message