Forum Stats

  • 3,781,103 Users
  • 2,254,481 Discussions
  • 7,879,567 Comments

Discussions

Why is Index not used ?

437473
437473 Member Posts: 32 Bronze Badge
edited Aug 27, 2009 12:20PM in General Database Discussions
Hi all,

I have a table with a simple index on a char column called vecka_id like:

CREATE INDEX NGDALA.DIM_DAG_TEST_VEID_IX ON NGDALA.DIM_DAG_TEST
(VECKA_ID)
LOGGING
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

-----------------------
When running this query the index IS used:

SELECT vecka_id
FROM dim_dag_test
WHERE vecka_id BETWEEN '2009W30' AND '2009W35'

Plan
SELECT STATEMENT ALL_ROWS
Cost: 2 Bytes: 756 Cardinality: 42
1 INDEX RANGE SCAN INDEX NGDALA.DIM_DAG_TEST_VEID_IX Search Columns: 1
Cost: 2 Bytes: 756 Cardinality: 42


-----------------------

When changing the selected column and running this query the index is NOT used:

SELECT dag_datum
FROM dim_dag_test
WHERE vecka_id BETWEEN '2009W30' AND '2009W35'

Plan
SELECT STATEMENT ALL_ROWS
Cost: 23 Bytes: 1,134 Cardinality: 42
1 TABLE ACCESS FULL TABLE NGDALA.DIM_DAG_TEST
Cost: 23 Bytes: 1,134 Cardinality: 42
-----------------------

My question is why the index isn´t used when the indexed column is not in the select statement ?

Regards
Samuel

Answers

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond
    In the case of the first query, the Optimizer saw that it could read all the required values of VECKA_ID from the index and that it would not need to go to the table at all (because the query's SELECT clause is for columns in the index).

    In the case of the second query, even after retrieving ROWIDs from the Index (for the qualifying VECKA_IDs), Oracle would still have to go to the table (because the SELECT clause specifies a column DAG_DATUM not present in the index). It then computes the effort to read the index for ROWIDs and then to read the Table for each individual row. It estimates that option A is more expensive than option B
    Option A : That it will have to make many single block read requests to the OS to get the index and the table blocks
    Option B : that it would have to make some multiblock read requests to the OS to read the whole table as a FullTableScan
    (It also computes an estimate for the CPU effort to filter the rows).

    Given the number of rows and the number of blocks it thinks it has to read, Oracle estimates a FullTableScan to be a cheaper operation.

    Hemant K Chitale
    http://hemantoracledba.blogspot.com
  • 437473
    437473 Member Posts: 32 Bronze Badge
    Thanks Hemant for your quick answer.

    //Samuel
  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    Helmant's answer is correct but it appears, from what you posted, you are using TOAD to create your explain plans.

    Toad does a notoriously bad job of explain planning as compared with Oracle's built-in, and free, DBMS_XPLAN.
    It does not show CPU, it does not accurately reflect a lot of information such as temp tablespace usage and, looking
    at your specific plan, I have real doubts about its the accuracy of this statement:
    "Cost: 23 Bytes: 1,134 Cardinality: 42"
    It seems highly unlikely.
  • 437473
    437473 Member Posts: 32 Bronze Badge
    Yes, I'm using "Explain Plan" in TOAD.

    Which is the simplest approach to use DBMS_XPLAN ?

    SELECT * FROM TABLE(dbms_xplan.display);

    returns "Error: cannot fetch last explain plan from PLAN_TABLE "

    Thanks
    /Sam
  • 491099
    491099 Member Posts: 57
    Have you created the plan table, and have you created the plan ? (ie run

    EXPLAIN PLAN for
    <your query>
    /


    cheers,

    graham
  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    To create the plan table in Oracle for versions 10.2 or higher run $ORACLE_HOME/rdbms/admin/catplan.sql. For earlier versions run utlxplan.sql from the same directory.
    SQL> @?/rdbms/admin/catplan.sql
    it is demonstrated at the link I already posted above.

    A quick note to everyone ... compare the plans created by DBMS_XPLAN and TOAD. The people at Quest have a lot of work to do. Hopefully they will get with it for their next release. Until then their plans aren't worth the screen real-estate they consume.
This discussion has been closed.