5 Replies Latest reply: Feb 8, 2013 12:30 PM by jgarry RSS

    Optimizer is not taking expected index

    oradba11
      Hi, i am wokring on 11.2.0.3.0 and AIX..
      We have a select query with one column in filter clause.

      example ...

      select col1,col2 from employee where col2='ABC' .......

      col2 is having index and col1 is primary key and having index.

      When we execute this query ...it is always taking primary key index ...not picking index on col2 ....and sql is runing very slow ...

      I tried to use hint to use index on col2 but optimizer is using same primary key index...

      How can we test by using col2 index and check if sql is performing better then using primary key index ...
        • 1. Re: Optimizer is not taking expected index
          Fran
          Oracle by default uses the best index for the query. If you want change it you must use hints (index in this case). Something like this:

          select /*index(col2)*/ col1,col2 from employee where col2='ABC'


          For more info:
          http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements006.htm#SQLRF50405
          oradba11     
                
                
          Handle:      oradba11  
          Status Level:      Newbie (30)
          Registered:      Sep 22, 2008
          Total Posts:      500
          Total Questions:      168 (110 unresolved)
          Name      OraDba
          Location      U.S.A.
          Occupation      Database Administrator
          Biography      Workning on Oracle Databases,having upto 5 years of experience.
          remember close your threads when your question will be solved, marking them as answered
          • 2. Re: Optimizer is not taking expected index
            Dom Brooks
            If you hint an optimizer directive and that directive is not followed, then either you've not hinted correctly or the hint cannot be obeyed.

            Regarding hinting correctly, it's pretty tricky in recent versions depending on the complexity of the query, see
            http://jonathanlewis.wordpress.com/2011/06/08/how-to-hint-1/

            Check the predicates section for implicit datatype conversions - that's one reason why your index might not be used.

            Also see:
            [url https://forums.oracle.com/forums/thread.jspa?threadID=863295]How to post a sql tuning request
            • 3. Re: Optimizer is not taking expected index
              oradba11
              ok ...let me test my sql with it ...

              thanks for advice ...
              • 4. Re: Optimizer is not taking expected index
                Paul  Horth
                oradba11 wrote:
                Hi, i am wokring on 11.2.0.3.0 and AIX..
                We have a select query with one column in filter clause.

                example ...

                select col1,col2 from employee where col2='ABC' .......

                col2 is having index and col1 is primary key and having index.

                When we execute this query ...it is always taking primary key index ...not picking index on col2 ....and sql is runing very slow ...

                I tried to use hint to use index on col2 but optimizer is using same primary key index...

                How can we test by using col2 index and check if sql is performing better then using primary key index ...
                It's not a good idea to use a hint in a production environment.

                Are you saying you have another part of the SQL that you're not showing with col1='DEF'? Then Oracle should choose the PK index on that column.

                Why do you think it should choose the index on col2?

                It is probably better to post the whole SQL here along with the explain plan.

                Edited by: Paul Horth on Feb 8, 2013 2:33 PM
                • 5. Re: Optimizer is not taking expected index
                  jgarry
                  In addition to what the others said:
                  Is col2 unique? See http://richardfoote.wordpress.com/2007/12/21/differences-between-unique-and-non-unique-indexes-part-ii/
                  Also see http://richardfoote.wordpress.com/2008/02/13/its-less-efficient-to-have-low-cardinality-leading-columns-in-an-index-right/

                  Is the optimizer deciding a skip scan of the primary is better than a range scan of the other index?

                  Also see if putting in some reference to col1 changes things.