This content has been marked as final. Show 4 replies
974908 wrote:WHY MY INDEX IS NOT BEING USED
I created the table
CREATE TABLE TABLE_AAA
COLUMN1 NUMBER(19) NOT NULL
, COLUMN2 VARCHAR2(20)
, COLUMN3 VARCHAR2(20)
, CONSTRAINT TABLE_AAA_PK PRIMARY KEY
I created the index
CREATE INDEX INDEX4 ON TABLE_AAA ("COLUMN2")
if I run the query
select column1, column2, column3 from table_aaa where column2 = 'sss'
the explain plan show TABLE_AAA FULL
while if execute
select column2 from table_aaa where column2 = 'sss'
the explain plan show INDEX4 RANGE SCAN
because in the first case does not use the index?
Welcome to the forum!
In the first case (select column1, column2, column3 ... ) Oracle has to access the table to get the three columns. The optimizer finds it faster to scan the whole table rather than to scan the index and then scan the respective blocks in the tbale.
In the second case (select column2 from .. ) , Oracle can get the result without going to the table, so the optimizer finds it faster to go to the index alone rather than to visit the table (either full or after a index scan).
How many rows you have in the table?
Do you have latest statistics on table?
select last_analyzed,num_rows from dba_tables where table_name='TABLE_AAA';
select count(*) from table_aaa
Have you collected the stats on the table and index? Histograms?
The second query could be satisfied by accessing just an index because all the columns in select list could be found in the index (column2).
The first query must access the table to retrieve additional columns.
The decision to use full table scan vs index access in the first case is made based on the information (or lack o it) on predicate selectivity e.g. how many rows we could expect to be returned for the value. If that value represents significant percentage then full table scan is a better option. Also if the table is rather small e.g. could be read in one or very few read operations the distribution of values would not even matter.
Hope it helps.
Edited by: Max Seleznev on Dec 4, 2012 10:20 AM