Re: Outer join = full table scan

Outer joins do *not*, in general, result in a full-table scan unless a full join would also require one.

The optimiser may have determined (rightly or wrongly) that a full table scan is most efficient.

To check, use a hint to force the use of the index(es) on the outer-joined table (the one with the (+) against its join column(s)) Thus:

select /*+ index_asc (A x_index) */

       ...
from A, B
where A.x (+) = B.x
and ...

Remember that the access path *must* be from B to A in the above code fragment.

Hope this helps.

Chrysalis.

Allen Hadden wrote:
>
> I've noticed that using an outer join will cause
> a full table scan. It doesn't seem to matter
> that all relevant columns have indexes. Is this
> an Oracle limitation?
>
> To get around it, I've re-written the query
> (queries) using a "union". This is obviously
> a little tedious and not very elegant.
>
> Is there anything else that can be done?
>
> Thanks in advance.
>
> -Allen
>
> --
> Allen Hadden
> Systems Engineer
> Taratec Development Corporation
> ahadden_at_taratec.com
Received on Wed Jun 04 1997 - 00:00:00 CDT