outer join query optimisation
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: outer join query optimisation

  1. #1
    Join Date
    Apr 2001
    Posts
    1

    Wink

    How can i force oracle not to do a full table scan on a query with an outer join?

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Provide the query, the explain plan and the version of Oracle and I'll show you.

    Basically, however, an outer join, by itself, does not mean that a table scan will be done. There are many ways to solve an outer join, and it will certainly use an index when it makes sense.

    - Chris

  3. #3
    Join Date
    Apr 2001
    Posts
    37
    Example: There are 2 indexes on standard scott's tables:
    1) PK_DEPT on dept(deptno), 2)EMP_FK on emp(deptno)

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

    SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=90)
    NESTED LOOPS (OUTER) (Cost=2 Card=5 Bytes=90)
    TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=1 Card=1 Bytes=11)
    INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
    TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=14 Bytes=98)
    INDEX (RANGE SCAN) OF 'EMP_FK' (NON-UNIQUE)

    You see that outer join does not perform full scan, it uses index on EMP(deptno).

    Note that the driving table is DEPT. In outer joins driving table is always
    the table without (+) sign, so outer join can not use index on the driving table DEPT(deptno).
    inosov
    Brainbench MVP for Oracle DBA

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Everything there was correct except that last statement. I suspect a typo . As you can see from the plan inosov provided - the index on dept_no on *both* tables was, indeed, used.

    - Chris

  5. #5
    Join Date
    Apr 2001
    Posts
    37
    In the example, the index on dept(deptno) was actually used due to
    predicate 'dept.deptno=40' but not for outer join itself.

    The main idea was that the index on dept(deptno) is useless for outer join execution,
    because emp can not be chosen as driving table.

    sorry for some inaccuracy.
    inosov
    Brainbench MVP for Oracle DBA

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Ahhhhhhh,

    Hadn't seen where you were going with that is all

    Objection over-ruled... the verdict stands!

    - Chris



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