2 Replies Latest reply: Mar 4, 2010 6:57 AM by Vijayaraghavan Krishnan RSS

    index not used

    652718
      Hi guys!

      I would be happy if someone could help me with my problem..

      I have a table, an index on it and a SQL statement:

      Table name:
      TEST_TBL
      Collumns:
      TEXTFIELD1
      TEXTFIELD2
      TEXTFIELD3
      TEXTFIELD4
      NUMFIELD1
      NUMFIELD2

      Index name:
      IX#TEST_TBL#1
      Collumns:
      TEXTFIELD1
      TEXTFIELD2
      NUMFIELD1

      SQL:
      SELECT * FROM TEST_TBL
      WHERE TEXTFIELD3 = 'A'
      AND TEXTFIELD1 IN ('A01', 'A02', 'A03', 'A04', 'A05', 'A06', 'A07', 'A08', 'A09', 'A10')
      AND TEXTFIELD2 = 'TEST'
      ORDER BY TEXTFIELD4 ASC, NUMFIELD2 ASC, TEXTFIELD1 ASC;

      The problem is that the SQL does not use the index.

      If I remove one or more values from this part:
      "AND TEXTFIELD1 IN ('A01', 'A02', 'A03', 'A04', 'A05', 'A06', 'A07', 'A08', 'A09', 'A10')"
      like
      "AND TEXTFIELD1 IN ('A01', 'A02', 'A03', 'A04', 'A05', 'A06', 'A07', 'A08', 'A09')"
      or
      "AND TEXTFIELD1 = 'A01'"
      then the index is used.

      In another environment with same table, index and statement, it uses the index..

      One idea that I had:
      The index was last analysed 1 year ago, could it be somehow possible that, when the content of the table changed in the meanwhile, this is the reason for not using the index?

      Does anybody have another idea what could be the reason?

      Thank you in advance!
        • 1. Re: index not used
          Richard Foote
          The TEXTFIELD1 column is the leading column of the index.

          As you have an IN clause (equivalent to OR conditions) on this column, Oracle must therefore probe the index 10 times to get the rows from the 10 possible values of interest. It can use the TEXTFIELD2 value within the index each time, but it needs to access the index 10 times.

          Doing so is deemed by the CBO to be more expensive than not using the index. But only just, because if you only have to use the index 9 or less times, then it's the cheaper alternative.

          In the other environment, the statistics or data is just different enough for the CBO to use the index.

          Depending on how many distinct values you have for TEXTFIELD2, it might be more index friendly if you had the columns the otherway around in the index.

          Cheers

          Richard Foote
          http://richardfoote.wordpress.com/
          • 2. Re: index not used
            Vijayaraghavan Krishnan
            Hi,

            >
            In another environment with same table, index and statement, it uses the index..
            >

            it is not necessary to get the same explain plan on all the servers unless you have the same statistics across the servers.

            Post the explan for your sql statements for both the cases and the last analyzed details for that.

            What is your Oracle Version.

            Regards,
            Vijayaraghavan K