Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: What does "push a join predicate down" mean ?

Re: What does "push a join predicate down" mean ?

From: Nejjari <nejjari_at_yahoo.fr>
Date: 13 Jul 2004 09:02:28 -0700
Message-ID: <28e0d9.0407130802.464ef36f@posting.google.com>


Hi,

For example: You have a simple Select col_1, col_2 from some_table defined as a view named base_view. If you issue the following query Select count(*) from base_view where col_1 = A and col_2 = B and there is an index on the 2 columns, the Optimizer will merge the use of the two columns into the view and the EXPLAIN_PLAN will reflect this along with the use of the Indexes on the individual tables.

It's not the same if you use multiple tables and a set operator, in this case a UNION ALL :

Now say the following view is created: Select col_1, col_2 from some_table UNION ALL Select col_1, col_2 from another_table, again named base_view. When You issue exactly the same query, the Where clause is not merged into either of the Selects. The UNION ALL is first processed and then, since there is now no index available, a horribly performing scan of the result set is done.

Youssef

spendius_at_muchomail.com (Spendius) wrote in message news:<aba30b75.0407120224.443227ab_at_posting.google.com>...
> I read this stuff several times. And there is this
> hidden param. _PUSH_JOIN_UNION_VIEW that's specifically
> been set up to "enable pushing join predicate inside a
> union view" as X$KSPPI's doc. states it.
>
> But I understand *nothing* to the sense of "pushing a
> join down"..., and found nothing in the forums to enlighten
> me.
>
> Thanks.
> Spendius
Received on Tue Jul 13 2004 - 11:02:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US