| 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
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
>
>
> Notes in-line
>
> 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
>
>
>
>
>
>
> ----- Original Message -----
> From: "Deepak Sharma" <sharmakdeep_oracle_at_yahoo.com>
> To: <oracle-l_at_freelists.org>
> Sent: Friday, June 24, 2005 4:02 AM
> Subject: Fwd: Re: Join 2 FACT Tables partitioned on
> same KEY column
>
>
>
>
>
>
> > Is the 'PARTITION RANGE ITERATOR' instead of
> > 'PARTITION RANGE SINGLE' in the plan due to the
> fact
> > that more than 1 partition of T2 are scanned?
> >
>
> That could well be the rationale, but that
> doesn't
> mean it's a good idea. At that point in the
> plan
> the row source operation IS "visit a single
> partition",
> so I would like to see that in the plan.
>
> > You are right in saying that with Nested Loop it
> is
> > possible. Is the 'PARTITION RANGE ITERATOR' on T2
> > confirming the fact that only certain (not all)
> > partitions of T2 are probed?
>
> That's why I expected to see "partition range
> single",
> it removes the ambiguity. Yes, it's confirming
> that
> not all the partitions are probed __on each
> pass__.
>
> > --
> > Is this possible with a Hash-join? Say, in your
> > example, D1 and T1 join results in 10 rows, and
> those
> > pertain to 2 partitions. Wouldn't it make sense to
> > probe only those 2 partitions of T2 'USING a
> HASH'?
> >
>
> Technically, when speaking of a hash join, the
> word
> PROBE refers to the action of using rows from
> the second table to locate data in the first
> table. i.e.
> you build the in-memory hash from the first
> table
> you probe the in-memory hash with the second
> table.
>
> It might be possible construct some SQL that did
> this
> sort of thing, but Oracle has to check all the
> partition keys
> that would be needed from the second table
> before
> optimising the query.
>
> In a two-table join (driver to t1 in my earlier
> example)
> it can do this with a parse-time "pruning query"
> against
> the first table. For the three-table query , it
> would
> have to instantiate the first part of the join
> so that it
> could run the pruning subquery against it. The
> only
> way that I can think of that might allow this to
> happen
> is to use subquery factoring ("with subquery")
> on the
> first join.
>
>
> > Since I am not seeing that with a Hash join
> between T1
> > and T2, but hypothetically speaking, should the
> plan
> > show something like:
> >
> > HASH
> > NESTED LOOP
> > TABLE ACCESS FULL DRIVER
> > PARTITION RANGE ALL
> > <T1>
> > PARTIITON RANGE ITERATOR
> > <T2>
> >
> > Thanks,
> > Deepak
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jun 24 2005 - 11:40:48 CDT
 |
 |