Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    153

    Unanswered: Using Index by Sql query

    Hi all,

    I am using EMP table of the user SCOTT.
    I made two indexes on ENAME and DEPTNO columns.

    Now I have the following queries :

    A. SELECT * FROM EMP
    WHERE ENAME='MARTIN'
    AND DEPTNO>30;

    B. SELECT * FROM EMP
    WHERE DEPTNO>30
    AND ENAME='MARTIN';

    for the both the caeses EXECUTION_PLAN is :

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=32)
    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=1 Byte
    s=32)

    2 1 INDEX (RANGE SCAN) OF 'E_NM' (NON-UNIQUE) (Cost=1 Card=1
    )

    Now see for both the queries Oracle is not using the Index for Dept column.

    Is there any reason for this ?

    And one more thing, when I am using OR clause it is not using ither of index column. Here is the execution plan :

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=6 Bytes=192)
    1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=6 Bytes=192)

    I would like to know this reson also.

    I need your help.

    Thanks

    JD

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    1st question :
    Code:
     Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=32)
    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=1 Byte
    s=32)
    
    2 1 INDEX (RANGE SCAN) OF 'E_NM' (NON-UNIQUE) (Cost=1 Card=1
    )
    See the optimizer expects to have only 1 row returned by an index scan on E_NM, so why would it access a second index on dept ? It just has to look with the ROWID returned by the index scan if this only record satisfies the condition on 'dept > 30' : this is obviously the less costly plan, so provided your stats are up-to-date, the optimizer is right.

    2nd question :
    Code:
     Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=6 Bytes=192)
    1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=6 Bytes=192)
    Here the optimizer expects a FTS to browse only 6 records, so it may well be that 2 index scans would be more costly than a FTS, at least the optimizer thinks so. Try inserting more records, then update your stats, and see if it reacts the same.

    HTH & Regards,

    RBARAER

Posting Permissions

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