Re: Remote query puzzle

Dennis Williams wrote:
> Thanks Jared, Mark, Mladen, Ben
>
> So far it seems that EXPLAIN PLAN is giving me the information that I
> need, if I use the DRIVING_SITE hint. Haven't had to pull out the
> heavy artillery (10053).
>
> I have found the text of the remote view. It involves joining three tables.
>
> So I have rewritten my query as a 4-table join with a small local
> table joined with three large remote tables. My idea is that Oracle
> should first take the local table, then use it to extract the rows it
> needs from each of the three remote tables in turn. My first try was
> to use the ORDERED hint to force the join in a specific order. Oracle
> didn't agree and joined to my table last.
>
> Any thoughts on the best way to induce Oracle to join tables in a
> specific order would be appreciated. This is 8.1.7, rule-based.
>
> Dennis Williams
>

 From an old doc I have on the RBO (circa 1991), maybe it will help.

           2)  THOU  SHALL PUT THE TABLE THAT RETURNS THE  FEWEST  ROWS
     LAST IN THE FROM LIST OF THE QUERY.

           This  is for when the oracle optimiser is stuck for  a  good
     idea. The Oracle optimiser works in the following manner. It looks
     at  each of the where clauses and assigns the tables  concerned  a
     number  based  on the type of predicate e.g. field  =  'const'  or
     field(+) = field. It then chooses the table with the lowest  score
     as  the  driving table. But, and its a big one, if more  than  one
     table has the same lowest score then it chooses the last table  in
     the from list.

-- 
Mark Bole
http://www.bincomputing.com



--
http://www.freelists.org/webpage/oracle-l