is index use in outer join?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: is index use in outer join?

  1. #1
    Join Date
    Jul 2001
    Posts
    59

    is index use in outer join?

    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.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Of course index can be used in outer join, why not?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width