Re: Forcing Oracle to use an order in the from clause

Amin Adatia wrote:
>
 

> Hi!
>
> If you are using optimizer_mode = RULE then the sequence of the Tables
> is the order in which the Tables are used. (It may be the other way
> around!) With COST or CHOOSE I dont think you have any choice.
>
> amit srivastava wrote:
> >
> > Can you force Oracle to use a particular table order in the
> > from clause? Esp. if you have outer joins in them?
> >

I'm afraid this is not the case.

With the RULE optimiser mode, the order in which tables are joined depends on all the other selection criteria: for example a unique key predicate will *always* be chosen over a non-unique key predicate. Only when all other considerations are equal will it use the default ordering of left to right in the FROM clause (but see below for outer joins).

With the COST optimiser you may give a hint (ORDERED), which operates, confusingly, from left to right. However, this may be overridden by the optimiser if this would result in very inefficient performance as in: select /*+ ORDERED */ ... from A, B
where A.col1 = B.col1
 and B.col2 = literal
where there is an index on B(col2,col1) but not on B(col1)

In the case of outer-joined tables, the one *without* the added null columns *must* be accessed first under either optimiser mode:

selct ... from A, B
where A.col1 (+) = B.col1

This query *must* be drivem from B, so that Oracle knows which null rows to add to A for the outer join.

Hope this helps.

Chrysalis. Received on Sun Apr 20 1997 - 00:00:00 CDT