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
-- //www.freelists.org/webpage/oracle-l
