Why is Index not used ?

437473

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

Hemant K Chitale

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

Thanks Hemant for your quick answer.

//Samuel

damorgan

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

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

Have you created the plan table, and have you created the plan ? (ie run

EXPLAIN PLAN for
<your query>
/


cheers,

graham

damorgan

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.