4 Replies Latest reply: Dec 4, 2012 9:20 AM by Max Seleznev RSS

    Use of indexes

    977911
      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
          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
            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
              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
                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