5 Replies Latest reply: May 26, 2010 5:03 PM by user12158503 RSS

    Index not being used in access path

    user12158503
      Hi,
      I have been trying to rewrite a query which currently is taking almost 1min and 25 secs to execute. The database version is 11.1.0.7.
      The query is -
      SELECT COUNT(1)
      FROM TAB1 p
      WHERE p.ACODE = 24377
      AND NOT EXISTS (SELECT 1 FROM TAB2 ph where ph.PKey = p.AKey )
      AND NOT EXISTS (SELECT 1 FROM TAB3 phs where phs.PKey = p.AKey )
      AND p.rflag = 'Y';

      The table originally didn't have an index on p.ACODE. So, I created this index and set it to visible and set the optimizer_use_invisible_indexes parameter also to TRUE. I set the monitoring on this index too. Even though I have created the index on the ACODE column, the access path doesn't use it. Can someone please tell me why this is not being used.
      Below is the explain plan for the sql stmt and the usage of the index -

      I have changed the actual table and column names -

      SQL> SELECT COUNT(1)
      2 FROM TAB1 p
      3 WHERE p.ACODE = 24377
      4 AND NOT EXISTS (SELECT 1 FROM TAB2 ph where ph.PKey = p.AKey )
      5 AND NOT EXISTS (SELECT 1 FROM TAB3 phs where phs.PKey = p.AKey )
      6 AND p.rflag = 'Y';

      COUNT(1)
      ----------
      1

                
      SQL> explain plan for
      2 SELECT COUNT(1)
      3 FROM TAB1 p
      4 WHERE p.ACODE = 24377
      5 AND NOT EXISTS (SELECT 1 FROM TAB2 ph where ph.PKey = p.AKey )
      6 AND NOT EXISTS (SELECT 1 FROM TAB3 phs where phs.PKey = p.AKey )
      7 AND p.rflag = 'Y';

      Explained.

      Elapsed: 00:00:00.02
      SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

      PLAN_TABLE_OUTPUT
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      Plan hash value: 3942424611

      -----------------------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      -----------------------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 1 | 87 | 214K (2)| 00:42:57 |
      | 1 | SORT AGGREGATE | | 1 | 87 | | |
      |* 2 | HASH JOIN ANTI | | 1 | 87 | 214K (2)| 00:42:57 |
      |* 3 | HASH JOIN ANTI | | 1 | 60 | 209K (2)| 00:41:56 |
      |* 4 | TABLE ACCESS FULL | TAB1 | 1 | 32 | 209K (2)| 00:41:55 |
      | 5 | INDEX FAST FULL SCAN| PK_TAB3 | 29918 | 818K| 53 (0)| 00:00:01 |
      | 6 | INDEX FAST FULL SCAN | PK_TAB2 | 3199K| 82M| 5059 (1)| 00:01:01 |
      -----------------------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      2 - access("PH"."PKey"="P"."AKey")
      3 - access("PHS"."PKey"="P"."AKey")
      4 - filter(TO_NUMBER("P"."ACODE")=24377 AND "P"."rflag"='Y')

      20 rows selected.

      Elapsed: 00:00:00.03
      SQL> select index_name,VISIBILITY from user_indexes where index_name='IDX_TAB1_ACODE';

      INDEX_NAME VISIBILIT
      ------------------------------ ---------
      IDX_TAB1_ACODE VISIBLE

      Elapsed: 00:00:00.01
      SQL> show parameter visible

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      optimizer_use_invisible_indexes boolean TRUE
      SQL>



      SQL> SELECT v.index_name, v.table_name,
      v.monitoring, v.used,
      start_monitoring, end_monitoring
      FROM v$object_usage v, dba_indexes u
      WHERE v.index_name = u.index_name
      AND v.index_name = 'IDX_TAB1_ACODE';

      INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
      ------------------------------ ------------------------------ --- --- ------------------- -------------------
      IDX_TAB1_ACODE TAB1 YES NO 05/26/2010 14:13:41

      Elapsed: 00:00:00.10

      Edited by: user12158503 on May 26, 2010 1:24 PM
        • 1. Re: Index not being used in access path
          user12158503
          Moving this thread to Database General. Thanks.
          • 2. Re: Index not being used in access path
            Centinul
            There can be any number of reasons why an index may not be chosen as an access path. One for example would be that the index clustering factor is poor and therefore it would cost more to use this path vice a full table scan. Another could be bad statistics.

            However, one particular item of interest I noticed in your execution plan was the following:
            4 - filter(TO_NUMBER("P"."ACODE")=24377 AND "P"."rflag"='Y')
            Oracle has applied a TO_NUMBER() function on your ACODE column. This could be preventing an index from being used. So my assumption is that this is a VARCHAR2/CHAR column correct? If so then replace this:
            WHERE p.ACODE = 24377
            with this:
            WHERE p.ACODE = '24377'
            This will ensure the implicit type conversion doesn't happen. Interestingly enough Charles Hooper had a blog post yesterday on this topic:

            True or False – Why Isn’t My Index Getting Used?

            If you scroll down to the responses Charles posted a "bonus question" that I responded to about implicit type conversion.

            Finally, are your statistics up to date? The reason I ask is because the explain plan estimates a single row from the full table scan. This seems suspect.

            HTH!
            • 3. Re: Index not being used in access path
              user12158503
              Thanks Centinul.

              I apologize for posting in both sections. I put it in the Database General too because I thought I originally posted it in the wrong section.

              The index was not being used because it was doing the implicit type conversion. When I enclosed it in quotes, it returns the result within a second and it uses the index.(whereas without the index it takes around 1min 25 sec)

              Here is the explain plan and execution time after enclosing it in quotes -

              SQL> explain plan for
              2 SELECT COUNT(1)
              3 FROM TAB1 p
              4 WHERE p.AKey = '24377'
              5 AND NOT EXISTS (SELECT 1 FROM TAB2 ph where ph.PKey = p.AgilityKey )
              6 AND NOT EXISTS (SELECT 1 FROM TAB3 phs where phs.PKey = p.AgilityKey )
              7 AND p.rflag = 'Y';

              Explained.

              Elapsed: 00:00:00.02
              SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

              PLAN_TABLE_OUTPUT
              ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              Plan hash value: 2008452282

              ----------------------------------------------------------------------------------------------------------
              | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
              ----------------------------------------------------------------------------------------------------------
              | 0 | SELECT STATEMENT | | 1 | 87 | 5134 (1)| 00:01:02 |
              | 1 | SORT AGGREGATE | | 1 | 87 | | |
              |* 2 | HASH JOIN ANTI | | 1 | 87 | 5134 (1)| 00:01:02 |
              |* 3 | HASH JOIN ANTI | | 1 | 60 | 60 (2)| 00:00:01 |
              |* 4 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 32 | 6 (0)| 00:00:01 |
              |* 5 | INDEX RANGE SCAN | IDX_TAB1_AKey | 4 | | 1 (0)| 00:00:01 |
              | 6 | INDEX FAST FULL SCAN | PK_TAB3 | 29918 | 818K| 53 (0)| 00:00:01 |
              | 7 | INDEX FAST FULL SCAN | PK_TAB2 | 3199K| 82M| 5059 (1)| 00:01:01 |
              ----------------------------------------------------------------------------------------------------------

              Predicate Information (identified by operation id):
              ---------------------------------------------------

              2 - access("PH"."PKey"="P"."AGILITYKEY")
              3 - access("PHS"."PKey"="P"."AGILITYKEY")
              4 - filter("P"."rflag"='Y')
              5 - access("P"."AKey"='24377')

              22 rows selected.

              Elapsed: 00:00:00.02

              SQL> SELECT COUNT(1)
              2 FROM TAB1 p
              3 WHERE p.AKey = '24377'
              4 AND NOT EXISTS (SELECT 1 FROM TAB2 ph where ph.PKey = p.AgilityKey )
              5 AND NOT EXISTS (SELECT 1 FROM TAB3 phs where phs.PKey = p.AgilityKey )
              6 AND p.rflag = 'Y';

              COUNT(1)
              ----------
              1

              Elapsed: 00:00:00.52


              Can you give me some tips on where I could read about learning to understand the explain plan. I did read a few articles which did help me, but not much. I am looking for something in detail.
              • 4. Re: Index not being used in access path
                548860
                Can you give me some tips on where I could read about learning to understand the explain plan. I did read a few articles which did help me, but not much. I am looking for something in detail.

                This book is incredible:
                http://www.amazon.com/Cost-Based-Oracle-Fundamentals-Experts-Voice/dp/1590596366
                • 5. Re: Index not being used in access path
                  user12158503
                  Thanks a lot Ronnis.