This discussion is archived
4 Replies Latest reply: Dec 4, 2012 7:20 AM by Max Seleznev RSS

Use of indexes

977911 Newbie
Currently Being Moderated
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
(
COLUMN1
)
ENABLE
);

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?

Thanks

Edited by: 974908 on 4-dic-2012 7.11
  • 1. Re: Use of indices
    sb92075 Guru
    Currently Being Moderated
    974908 wrote:
    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
    (
    COLUMN1
    )
    ENABLE
    );

    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?

    Thanks
    WHY MY INDEX IS NOT BEING USED
    http://communities.bmc.com/communities/docs/DOC-10031

    http://searchoracle.techtarget.com/tip/Why-isn-t-my-index-getting-used

    http://www.orafaq.com/tuningguide/not%20using%20index.html
  • 2. Re: Use of indexes
    Iordan Iotzov Expert
    Currently Being Moderated
    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).

    Iordan Iotzov
    http://iiotzov.wordpress.com/
  • 3. Re: Use of indexes
    vlethakula Expert
    Currently Being Moderated
    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
  • 4. Re: Use of indices
    Max Seleznev Explorer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points