1 2 Previous Next 15 Replies Latest reply: Sep 22, 2010 3:12 PM by Charles Hooper Go to original post RSS
      • 15. Re: OR in my where clause makes CBO not use index
        Charles Hooper
        Randolf,

        I had not considered that Oracle might need to visit the table to determine if 'BOSS' = ''.

        I modified my test table like this:
        ALTER TABLE T1 MODIFY (C200000020 NOT NULL); 
        Then I confirmed that the execution plan could use an index if hinted:
        ALTER SYSTEM FLUSH SHARED_POOL;
         
        SET AUTOTRACE TRACEONLY EXPLAIN
         
        SELECT
          *
        FROM
          T1
        WHERE
          C200000020 LIKE 'BOSS' || '%'
          OR 'BOSS' = '';
         
        Execution Plan
        ----------------------------------------------------------
        Plan hash value: 3617692013
         
        --------------------------------------------------------------------------
        | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
        --------------------------------------------------------------------------
        |   0 | SELECT STATEMENT  |      |   100K|    20M| 82353   (1)| 00:16:29 |
        |*  1 |  TABLE ACCESS FULL| T1   |   100K|    20M| 82353   (1)| 00:16:29 |
        --------------------------------------------------------------------------
         
        Predicate Information (identified by operation id):
        ---------------------------------------------------
           1 - filter('BOSS'='' OR "C200000020" LIKE 'BOSS%')
        SELECT /*+ INDEX(T1 IND_T1) */
          *
        FROM
          T1
        WHERE
          C200000020 LIKE 'BOSS' || '%'
          OR 'BOSS' = '';
         
        Execution Plan
        ----------------------------------------------------------
        Plan hash value: 3418867520
         
        --------------------------------------------------------------------------------------
        | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
        --------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT            |        |   100K|    20M|  9980K  (1)| 33:16:04 |
        |*  1 |  TABLE ACCESS BY INDEX ROWID| T1     |   100K|    20M|  9980K  (1)| 33:16:04 |
        |   2 |   INDEX FULL SCAN           | IND_T1 |    10M|       | 31855   (1)| 00:06:23 |
        --------------------------------------------------------------------------------------
         
        Predicate Information (identified by operation id):
        ---------------------------------------------------
           1 - filter('BOSS'='' OR "C200000020" LIKE 'BOSS%')
        Note the increase in the estimated time using the index, and that the filter is applied at the table level, so essentially every table block will be visited, probably multiple times.

        For reference, the extents for my segments were created as follows in an ASSM AUTOALLOCATE tablespace:
        SEGMENT       EXTENTS EXT_SIZE_KB   TOTAL_MB
        ---------- ---------- ----------- ----------
        IND_T1             16          64          1
        IND_T1             63        1024         63
        IND_T1             24        8192        192
        T1                 16          64          1
        T1                 63        1024         63
        T1                120        8192        960
        T1                 21       65536       1344
        Now, let's check the performance with the following script:
        SET AUTOTRACE OFF
        SET TIMING ON
         
        ALTER SYSTEM FLUSH BUFFER_CACHE;
        ALTER SYSTEM FLUSH BUFFER_CACHE;
         
        SELECT
          *
        FROM
          T1
        WHERE
          C200000020 LIKE 'BOSS' || '%'
          OR 'BOSS' = '';
         
        ALTER SYSTEM FLUSH BUFFER_CACHE;
        ALTER SYSTEM FLUSH BUFFER_CACHE;
         
        SELECT /*+ INDEX(T1 IND_T1) */
          *
        FROM
          T1
        WHERE
          C200000020 LIKE 'BOSS' || '%'
          OR 'BOSS' = '';
         
        ALTER SYSTEM FLUSH BUFFER_CACHE;
        ALTER SYSTEM FLUSH BUFFER_CACHE;
         
        SELECT /*+ INDEX(T1 IND_T1) */
          *
        FROM
          T1
        WHERE
          C200000020 LIKE 'BOSS' || '%';
        Here is the output:
        SQL> SELECT
          2    *
          3  FROM
          4    T1
          5  WHERE
          6    C200000020 LIKE 'BOSS' || '%'
          7    OR 'BOSS' = '';
         
        C200000020
        --------------------
        PADDING
        ---------------------------------------
        BOSS3000000
        AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
        AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
         
        BOSS6000000
        AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
        AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
         
        BOSS9000000
        AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
        AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
         
        Elapsed: 00:00:04.49
        SQL>
        SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
         
        System altered.
         
        Elapsed: 00:00:00.06
        SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
         
        System altered.
         
        Elapsed: 00:00:00.06
        SQL>
        SQL> SELECT /*+ INDEX(T1 IND_T1) */
          2    *
          3  FROM
          4    T1
          5  WHERE
          6    C200000020 LIKE 'BOSS' || '%'
          7    OR 'BOSS' = '';
         
        C200000020
        --------------------
        PADDING
        ---------------------------------------
        BOSS3000000
        AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
        AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
         
        BOSS6000000
        AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
        AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
         
        BOSS9000000
        AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
        AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
         
        Elapsed: 00:02:32.81
        SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
         
        System altered.
         
        Elapsed: 00:00:00.15
        SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
         
        System altered.
         
        Elapsed: 00:00:00.09
        SQL>
        SQL> SELECT /*+ INDEX(T1 IND_T1) */
          2    *
          3  FROM
          4    T1
          5  WHERE
          6    C200000020 LIKE 'BOSS' || '%';
         
        C200000020
        --------------------
        PADDING
        ---------------------------------------
        BOSS3000000
        AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
        AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
         
        BOSS6000000
        AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
        AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
         
        BOSS9000000
        AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
        AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
         
        Elapsed: 00:00:00.07
        4.49 seconds to perform the full table scan, 2 minutes and 32.81 seconds for the index full scan, and 0.07 seconds to do it the correct way.

        It appears that it is time for the developer to correct the code.

        Charles Hooper
        Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
        http://hoopercharles.wordpress.com/
        IT Manager/Oracle DBA
        K&M Machine-Fabricating, Inc.
        1 2 Previous Next