is index use in outer join?

[pbmistry]

Hi to all,

I have one problem regarding the query.

Can index use in outer join condition if not can i use hint for using that index.

Thanks a lot in advance.
Regards,
Mistry Pradip.

[jmodic]

Of course index can be used in outer join, why not?

[slimdave]

It might depend whether you are talking about an index on the left or the right side of the join. A query with an outer-join is driven from the non-augmented table, so even if you had a very selective predicate on the augmented table an index on that column might not get used.

[jmodic]

True. However index on non-augmented table can of course be used for *joining predicates*. Question was very general, and general answer is: indexes on both tables involved in outer join can be used.

Simple example on traditional SCOTT schema:

select d.dname, e.ename
from dept d, emp e
where d.deptno = e.deptno(+)
and d.deptno = 10;

If you have DEPTNO columns indexed on both tables (and if optimizer finds both indexes usable for the query) they will both be used in execution of this query. Of course index on DEPT will be used on the driving side, but index on EMP.DEPTNO will be used for outer join predicate.