This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Sep 22, 2010 1:12 PM by CharlesHooper RSS

OR in my where clause makes CBO not use index

719222 Newbie
Currently Being Moderated
I remember reading something about this.

I tried in both 10g and 11g with same issue. Our crazy 3rd party app uses the below sql on any column it searches on.
If I take OR ('BOSS' = '')) out, CBO creates execution plan using the index, if I put it back, it does a FULL table scan. I was curious if there was anything I could from DB side?

The table has 8mill records and if it uses the index, takes only milliseconds to return the 3 records, with full table scan over 3mins.


( (T131.C200000020 LIKE ('BOSS' || '%')) OR ('BOSS' = ''))

Thanks
  • 1. Re: OR in my where clause makes CBO not use index
    674955 Newbie
    Currently Being Moderated
    This condition is never TRUE, is this the actual code you have ?? Why will some one what to add a condition which can never be true??
    'BOSS' = ''
  • 2. Re: OR in my where clause makes CBO not use index
    719222 Newbie
    Currently Being Moderated
    I plan to have the app sme to submit a bug, because it is crazy. Just curious on anything on the DB side.
  • 3. Re: OR in my where clause makes CBO not use index
    CharlesHooper Expert
    Currently Being Moderated
    I think that we need a test case to see what is happening. First, we will create a simple table with our column of interest and a large column that should help to discourage full table scans:
    CREATE TABLE T1 (
      C200000020 VARCHAR2(20),
      PADDING VARCHAR2(250));
    Next, we will insert 10,000,000 rows into the table such that an index built on the column C200000020 will have a very high clustering factor, and 3 rows will have a value that begins with BOSS:
    INSERT INTO
      T1
    SELECT
      DECODE(MOD(ROWNUM,3000000),0,'BOSS'||ROWNUM,
      CHR(90-MOD(ROWNUM-1,26))||
      CHR(75+MOD(ROWNUM,10))||
      CHR(80+MOD(ROWNUM,5))||
      'S'||ROWNUM) C200000020,
      RPAD('A',200,'A') PADDING
    FROM
      (SELECT
        ROWNUM RN
      FROM
        DUAL
      CONNECT BY
        LEVEL<=10000) V1,
      (SELECT
        ROWNUM RN
      FROM
        DUAL
      CONNECT BY
        LEVEL<=1000) V2;
    Now to create the index and collect statistics:
    CREATE INDEX IND_T1 ON T1(C200000020);
     
    EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)
    Let's take a look at the execution plans:
    SET AUTOTRACE TRACEONLY EXPLAIN
     
    SELECT
      *
    FROM
      T1
    WHERE
      C200000020 LIKE 'BOSS' || '%';
     
    Plan hash value: 634656657
     
    --------------------------------------------------------------------------------------
    | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |        |     1 |   213 |     5   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |   213 |     5   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | IND_T1 |     1 |       |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("C200000020" LIKE 'BOSS%')
           filter("C200000020" LIKE 'BOSS%')
    An index access, just like we had hoped. The optimizer is predicting a single row to be retrieved. Let's try the other query:
    SELECT
      *
    FROM
      T1
    WHERE
      C200000020 LIKE 'BOSS' || '%'
      OR 'BOSS' = '';
      
    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%')
    A full table scan, just like you saw. Notice that the optimizer is now predicting that 100,000 rows (1% of the rows) will be retrieved. Repeating, 1% of the rows and a full table scan. Let's generate a 10053 trace for the SQL statement:
    ALTER SYSTEM FLUSH SHARED_POOL;
     
    SET AUTOTRACE OFF
     
    ALTER SESSION SET TRACEFILE_IDENTIFIER = 'T1_10053';
    ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
     
    SELECT
      *
    FROM
      T1
    WHERE
      C200000020 LIKE 'BOSS' || '%'
      OR 'BOSS' = '';
     
    ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';
    Inside the 10053 trace, my 11.2.0.1 test database produced the following:
    ***************************************
    BASE STATISTICAL INFORMATION
    ***********************
    Table Stats::
      Table: T1  Alias: T1
        #Rows: 10000000  #Blks:  303031  AvgRowLen:  213.00
    Index Stats::
      Index: IND_T1  Col#: 1
        LVLS: 2  #LB: 32323  #DK: 9939968  LB/K: 1.00  DB/K: 1.00  CLUF: 10120176.00
    Access path analysis for T1
    ***************************************
    SINGLE TABLE ACCESS PATH 
      Single Table Cardinality Estimation for T1[T1] 
      Table: T1  Alias: T1
        Card: Original: 10000000.000000  Rounded: 100001  Computed: 100001.02  Non Adjusted: 100001.02
      Access Path: TableScan
        Cost:  82352.89  Resp: 82352.89  Degree: 0
          Cost_io: 82073.00  Cost_cpu: 7150017105
          Resp_io: 82073.00  Resp_cpu: 7150017105
      ****** trying bitmap/domain indexes ******
      ****** finished trying bitmap/domain indexes ******
      Best:: AccessPath: TableScan
             Cost: 82352.89  Degree: 1  Resp: 82352.89  Card: 100001.02  Bytes: 0
     
    ***************************************
     
    
    OPTIMIZER STATISTICS AND COMPUTATIONS
    ***************************************
    GENERAL PLANS
    ***************************************
    Considering cardinality-based initial join order.
    Permutations for Starting Table :0
    Join order[1]:  T1[T1]#0
    ***********************
    Best so far:  Table#: 0  cost: 82352.8851  card: 100001.0195  bytes: 21300213
    ***********************
    (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000
     
    *********************************
    Number of join permutations tried: 1
    *********************************
    The unknown result of the constant in the WHERE clause ('BOSS' = '') caused Oracle to predict that the cardinality will be (1 row) + (1% of the rows) = 100001. With a clustering factor of 10,120,176 the optimizer is convinced that it will need to perform single block physical reads of a large number of table blocks to read the 100,001 rows that it expects to retrieve, so it decided that a full table scan would complete faster. But the situation is worse than that - it did not even consider an index access path. As a demonstration, I will manually set the index's clustering factor to a low value and check the execution plan again:
    EXEC DBMS_STATS.SET_INDEX_STATS(OWNNAME=>USER,INDNAME=>'IND_T1',CLSTFCT=>10000)
     
    SET AUTOTRACE TRACEONLY EXPLAIN
     
    ALTER SYSTEM FLUSH SHARED_POOL;
     
    SELECT
      *
    FROM
      T1
    WHERE
      C200000020 LIKE 'BOSS' || '%'
      OR 'BOSS' = '';
     
    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%')
    Still a full table scan. If we had generated a 10053 trace, we would see that the clustering factor for the index was indeed adjusted:
    ***************************************
    BASE STATISTICAL INFORMATION
    ***********************
    Table Stats::
      Table: T1  Alias: T1
        #Rows: 10000000  #Blks:  303031  AvgRowLen:  213.00
    Index Stats::
      Index: IND_T1  Col#: 1
        LVLS: 2  #LB: 32323  #DK: 9939968  LB/K: 1.00  DB/K: 1.00  CLUF: 10000.00
    Access path analysis for T1
    ***************************************
    SINGLE TABLE ACCESS PATH 
      Single Table Cardinality Estimation for T1[T1] 
      Table: T1  Alias: T1
        Card: Original: 10000000.000000  Rounded: 100001  Computed: 100001.02  Non Adjusted: 100001.02
      Access Path: TableScan
        Cost:  82352.89  Resp: 82352.89  Degree: 0
          Cost_io: 82073.00  Cost_cpu: 7150017105
          Resp_io: 82073.00  Resp_cpu: 7150017105
      ****** trying bitmap/domain indexes ******
      ****** finished trying bitmap/domain indexes ******
      Best:: AccessPath: TableScan
             Cost: 82352.89  Degree: 1  Resp: 82352.89  Card: 100001.02  Bytes: 0
    Let's force the execution plan with an index hint to see what happens:
    SELECT /*+ INDEX(T1 IND_T1) */
      *
    FROM
      T1
    WHERE
      C200000020 LIKE 'BOSS' || '%'
      OR 'BOSS' = '';
      
    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%')
    Note that the optimizer could not obey the hint. It decided to apply the 'BOSS'='' first. Let's try a hint to force the optimizer to apply the predicates in order, rather than based on cost:
    SELECT /*+ ORDERED_PREDICATES INDEX(T1 IND_T1) */
      *
    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("C200000020" LIKE 'BOSS%' OR 'BOSS'='')
    The predicate section of the execution plan changed, but the optimizer still will not consider an index access path for the SQL statement. There is a chance that you could do something by hacking a stored outline for the query, but my guess is that the restriction on the C200000020 column changes from time to time, so an outline likely will not work. It appears that you could try to file an Oracle bug report because the optimizer completely ignored the index access paths, but a better course of action would be to have the application fixed.

    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.
  • 4. Re: OR in my where clause makes CBO not use index
    CharlesHooper Expert
    Currently Being Moderated
    For comparison, let's see what happens when we add a space between the two ' characters:
    SET AUTOTRACE TRACEONLY EXPLAIN
     
    ALTER SESSION SET TRACEFILE_IDENTIFIER = 'T1_10053-3';
    ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
     
    SELECT
      *
    FROM
      T1
    WHERE
      C200000020 LIKE 'BOSS' || '%'
      OR 'BOSS' = ' ';
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 634656657
    
    --------------------------------------------------------------------------------------
    | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |        |     1 |   213 |     4   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |   213 |     4   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | IND_T1 |     1 |       |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("C200000020" LIKE 'BOSS%')
           filter("C200000020" LIKE 'BOSS%')
     
    ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';
    Notice by looking at the Predicate Information section that Oracle removed the nonsensical OR clause. The 10053 trace file showed this:
    ***************************************
    BASE STATISTICAL INFORMATION
    ***********************
    Table Stats::
      Table: T1  Alias: T1
        #Rows: 10000000  #Blks:  303031  AvgRowLen:  213.00
    Index Stats::
      Index: IND_T1  Col#: 1
        LVLS: 2  #LB: 32323  #DK: 9939968  LB/K: 1.00  DB/K: 1.00  CLUF: 10000.00
    Access path analysis for T1
    ***************************************
    SINGLE TABLE ACCESS PATH 
      Single Table Cardinality Estimation for T1[T1] 
      Table: T1  Alias: T1
        Card: Original: 10000000.000000  Rounded: 1  Computed: 1.03  Non Adjusted: 1.03
      Access Path: TableScan
        Cost:  82255.34  Resp: 82255.34  Degree: 0
          Cost_io: 82073.00  Cost_cpu: 4658017105
          Resp_io: 82073.00  Resp_cpu: 4658017105
    kkofmx: index filter:"T1"."C200000020" LIKE 'BOSS%'
    
      Access Path: index (RangeScan)
        Index: IND_T1
        resc_io: 4.00  resc_cpu: 29226
        ix_sel: 0.000000  ix_sel_with_filters: 0.000000 
        Cost: 4.00  Resp: 4.00  Degree: 1
      Best:: AccessPath: IndexRange
      Index: IND_T1
             Cost: 4.00  Degree: 1  Resp: 4.00  Card: 1.03  Bytes: 0
    So, it appears that if the optimizer is presented with a 0 length VARCHAR being compared with another VARCHAR in the WHERE clause it could cause unexpected cases were index access paths will not be used.

    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.
  • 5. Re: OR in my where clause makes CBO not use index
    user503699 Expert
    Currently Being Moderated
    Charles Hooper wrote:
    So, it appears that if the optimizer is presented with a 0 length VARCHAR being compared with another VARCHAR in the WHERE clause it could cause unexpected cases were index access paths will not be used.
    Charles,

    Thanks a lot for this fantastic explaination with a complete test case.
    Is it possible that when optimizer is presented with a zero length VARCHAR being compared with another VARCHAR in the WHERE clause, it treats the xero length varchar as NULL ? So the following condition
    'BOSS' = ''
    becomes
    'BOSS' = NULL
    As the result of the revised predicate is UNKNOWN, optimizer is not able to ignore the predicate (like it does with a space between quotes) and probably switches to full table scan (as it would do when multiple predicates on table columns are combined with OR).

    Edited by: user503699 on Sep 22, 2010 11:43 AM
  • 6. Re: OR in my where clause makes CBO not use index
    WhiteHat Expert
    Currently Being Moderated
    oracle doesn't distinguish between zero-length strings and nulls so perhaps you're right.
  • 7. Re: OR in my where clause makes CBO not use index
    ToonKoppelaars Employee ACE
    Currently Being Moderated
    Your crazy 3rd party app should use bind variables...
    And use 'IS NULL' when trying to check whether a value is the empty string...
    But they probably don't do both of the above, because they are "DBMS-independent".

    I bet that a where clause such as:
    ( (T131.C200000020 LIKE (:B0 || '%')) OR (:B0 IS NULL))
    Would also have pruned the FALSE disjunct and then go for the index-scan.
    It would also save your database server a lot of hard parses.
  • 8. Re: OR in my where clause makes CBO not use index
    CharlesHooper Expert
    Currently Being Moderated
    I agree with Toon regarding the use of bind variables. Unfortunately, it does not look like bind variables improve the situation, at least in my test case.

    I cannot use AUTOTRACE, so I will use DBMS_XPLAN.DISPLAY_CURSOR along with a GATHER_PLAN_STATISTICS hint in the SQL statement. First the statistics collection and bind variable setup:
    EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)
     
    VARIABLE B0 VARCHAR2(50)
    VARIABLE B1 VARCHAR2(50)
    VARIABLE B2 VARCHAR2(50)
     
    EXEC :B0 := 'BOSS'
    EXEC :B1 := 'BOSS'
    EXEC :B2 := ''
     
    SET AUTOTRACE OFF
    Since we do not know the intention of the developer, I will try a couple of combinations to see what happens:
    SELECT /*+ GATHER_PLAN_STATISTICS */
      *
    FROM
      T1
    WHERE
      C200000020 LIKE :B0 || '%'
      OR :B1 = :B2;
     
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
     
    SQL_ID  b47qzqbb6wymu, child number 0
    -------------------------------------
    SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 WHERE   C200000020
    LIKE :B0 || '%'   OR :B1 = :B2
     
    Plan hash value: 3617692013
     
    ------------------------------------------------------------------------------------
    | Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |      1 |        |      3 |00:00:00.34 |     303K|
    |*  1 |  TABLE ACCESS FULL| T1   |      1 |    100K|      3 |00:00:00.34 |     303K|
    ------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter((:B1=:B2 OR "C200000020" LIKE :B0||'%'))
    The above resulted in the same full table scan that we saw earlier.
    SELECT /*+ GATHER_PLAN_STATISTICS */
      *
    FROM
      T1
    WHERE
      C200000020 LIKE :B0 || '%'
      OR :B0 = :B2;
     
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
     
    SQL_ID  1vdyc7t6wazhz, child number 0
    -------------------------------------
    SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 WHERE   C200000020
    LIKE :B0 || '%'   OR :B0 = :B2
     
    Plan hash value: 3617692013
     
    ------------------------------------------------------------------------------------
    | Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |      1 |        |      3 |00:00:00.34 |     303K|
    |*  1 |  TABLE ACCESS FULL| T1   |      1 |    100K|      3 |00:00:00.34 |     303K|
    ------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter((:B0=:B2 OR "C200000020" LIKE :B0||'%'))
    Specifying the B0 bind variable twice did not help.
    SELECT /*+ GATHER_PLAN_STATISTICS */
      *
    FROM
      T1
    WHERE
      C200000020 LIKE :B0 || '%'
      OR :B0 IS NULL;
     
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
     
    SQL_ID  8n1bg0z9j0001, child number 0
    -------------------------------------
    SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 WHERE   C200000020
    LIKE :B0 || '%'   OR :B0 IS NULL
     
    Plan hash value: 3617692013
     
    ------------------------------------------------------------------------------------
    | Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |      1 |        |      3 |00:00:00.34 |     303K|
    |*  1 |  TABLE ACCESS FULL| T1   |      1 |    500K|      3 |00:00:00.34 |     303K|
    ------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter((:B0 IS NULL OR "C200000020" LIKE :B0||'%'))
    Specifying that the B0 bind variable IS NULL did not help either, but notice the change in the predicted cardinality.

    Let's try an index hint:
    SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1 IND_T1) */
      *
    FROM
      T1
    WHERE
      C200000020 LIKE :B0 || '%'
      OR :B0 IS NULL;
     
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
     
    SQL_ID  a241dy7mvudtk, child number 0
    -------------------------------------
    SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1 IND_T1) */   * FROM   T1
    WHERE   C200000020 LIKE :B0 || '%'   OR :B0 IS NULL
     
    Plan hash value: 3617692013
     
    ------------------------------------------------------------------------------------
    | Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |      1 |        |      3 |00:00:00.34 |     303K|
    |*  1 |  TABLE ACCESS FULL| T1   |      1 |    500K|      3 |00:00:00.34 |     303K|
    ------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter((:B0 IS NULL OR "C200000020" LIKE :B0||'%'))
    The optimizer still did not use the index.

    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.
  • 9. Re: OR in my where clause makes CBO not use index
    user503699 Expert
    Currently Being Moderated
    Charles Hooper wrote:
    The optimizer still did not use the index.
    Charles,

    The PLAN_HASH_VALUE for all your plans is the same (3617692013), which appears to suggest that the plan was generated only first time (due to hard parse) and reused by subsequent SQLs (a soft parse).
    Or am I missing something?
  • 10. Re: OR in my where clause makes CBO not use index
    CharlesHooper Expert
    Currently Being Moderated
    user503699 wrote:
    Charles Hooper wrote:
    The optimizer still did not use the index.
    Charles,

    The PLAN_HASH_VALUE for all your plans is the same (3617692013), which appears to suggest that the plan was generated only first time (due to hard parse) and reused by subsequent SQLs (a soft parse).
    Or am I missing something?
    Good question. The PLAN_HASH_VALUE does not take into account that the Predicate Information section is different for each SQL statement - it simply states that the same operations, in the same order, appear in the execution plan - thus the execution plan is the same . The displayed SQL_ID (and child number combination) did change indicating that the SQL statements were hard parsed (a241dy7mvudtk, 8n1bg0z9j0001, 1vdyc7t6wazhz, b47qzqbb6wymu)

    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.
  • 11. Re: OR in my where clause makes CBO not use index
    user503699 Expert
    Currently Being Moderated
    Charles Hooper wrote:
    Good question. The PLAN_HASH_VALUE does not take into account that the Predicate Information section is different for each SQL statement - it simply states that the same operations, in the same order, appear in the execution plan - thus the execution plan is the same . The displayed SQL_ID (and child number combination) did change indicating that the SQL statements were hard parsed (a241dy7mvudtk, 8n1bg0z9j0001, 1vdyc7t6wazhz, b47qzqbb6wymu)
    Charles,

    Thanks for the clarification. out of curiosity, what plan do you get when you don't use bind variables in following sql?
    SELECT /*+ GATHER_PLAN_STATISTICS */
      *
    FROM
      T1
    WHERE
      C200000020 LIKE 'BOSS%'
      OR 'BOSS' IS NULL;
    If the above uses index then I am confused as a hard-parse should result in a bind peeking, which should mean both SQLs (with and without bind variables) should use same plan. If you are testing on 11g, does adaptive cursor sharing spoil the party here?
  • 12. Re: OR in my where clause makes CBO not use index
    CharlesHooper Expert
    Currently Being Moderated
    SELECT /*+ GATHER_PLAN_STATISTICS */
      *
    FROM
      T1
    WHERE
      C200000020 LIKE 'BOSS%'
      OR 'BOSS' IS NULL;
     
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
     
    SQL_ID  90arryuv87tf1, child number 0
    -------------------------------------
    SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 WHERE   C200000020
    LIKE 'BOSS%'   OR 'BOSS' IS NULL
     
    Plan hash value: 634656657
     
    ------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |        |      1 |        |      3 |00:00:00.01 |       7 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T1     |      1 |      1 |      3 |00:00:00.01 |       7 |
    |*  2 |   INDEX RANGE SCAN          | IND_T1 |      1 |      1 |      3 |00:00:00.01 |       4 |
    ------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("C200000020" LIKE 'BOSS%')
           filter("C200000020" LIKE 'BOSS%')
    In this case, the Predicate Information section shows that the nonsense OR clause was removed (because a literal/constant cannot be NULL).

    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.
  • 13. Re: OR in my where clause makes CBO not use index
    user503699 Expert
    Currently Being Moderated
    Charles Hooper wrote:
    In this case, the Predicate Information section shows that the nonsense OR clause was removed (because a literal/constant cannot be NULL).
    Charles,

    I got your results on a 10.2.0.4 database. When I generated 10053 trace for the query with bind variable, the trace showed that bind-peeking took place but it seems the optimizer was not able to "ignore" the "B0 is null" predicate in spite of that (hence resulting in full table scan). I am confused as to whether this is a limitation (or bug) or a feature of the optimizer. My understanding is the whole idea behind introducing bind peeking was to allow the optimizer to choose a better execution plan. It seems the bind-peeking helps only when table columns are involved in predicate (which is not the case with "B0 is null" predicate).
  • 14. Re: OR in my where clause makes CBO not use index
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    Thanks Charles, for the effort with this test case.

    Interesting discussion - first of all I would say the application logic is broken with Oracle.

    The only explanation I can see for this kind of predicate:
    ( (T131.C200000020 LIKE ('BOSS' || '%')) OR ('BOSS' = ''))
    is to cover the case where the search term equals '' and to show all rows in that case.

    So we would end up with a clause like this:
    ( (T131.C200000020 LIKE ('' || '%')) OR ('' = ''))
    Since Oracle cannot use the index in certain scenarios (I'll come to that later) the column C200000020 is defined as NULLABLE, however above statement will still filter out any NULL values in C200000020. I assume that the column is probably never NULL, however since it is defined as NULLABLE this is certainly an application design flaw:
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL>
    SQL> drop table t;
    
    Table dropped.
    
    SQL>
    SQL> purge table t;
    
    Table purged.
    
    SQL>
    SQL> create table t
      2  as
      3  select
      4          decode(
      5                  mod(rownum, 1000)
      6                , 0, 'BOSS' || rownum
      7                , 1, null
      8                , dbms_random.string('X', trunc(dbms_random.value(1, 20)))
      9                ) as col1,
     10          rpad('x', 100) as vc_small
     11  from
     12          dual
     13  connect by
     14          level <= 10000;
    
    Table created.
    
    SQL>
    SQL> exec dbms_stats.gather_table_stats(null, 't')
    
    PL/SQL procedure successfully completed.
    
    SQL>
    SQL> create index t_idx on t (col1);
    
    Index created.
    
    SQL>
    SQL> select count(*) from t;
    
      COUNT(*)
    ----------
         10000
    
    SQL>
    SQL> select count(*) from t where col1 is null;
    
      COUNT(*)
    ----------
            10
    
    SQL>
    SQL> select count(*) from t where col1 like '' || '%';
    
      COUNT(*)
    ----------
          9990
    
    SQL>
    SQL> select count(*) from t where '' = '';
    
      COUNT(*)
    ----------
             0
    
    SQL>
    SQL> select count(*) from t where col1 like '' || '%' or '' = '';
    
      COUNT(*)
    ----------
          9990
    So now to the question why the optimizer refuses to use the index. If we change the column to mandatory or generate a function-based index that covers also NULL values since my test case includes NULL values, we can see why:
    SQL> drop index t_idx;
    
    Index dropped.
    
    SQL>
    SQL> create index t_idx on t (col1, 0);
    
    Index created.
    
    SQL>
    SQL> explain plan for
      2  select /*+ index(t t_idx) */ * from t where col1 like '' || '%' or '' = '';
    
    Explained.
    
    SQL> select * from table(dbms_xplan.display);
    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------------------------------------------------------------
    
    Plan hash value: 3778778741
    
    -------------------------------------------------------------------------------------
    | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |       |  9980 |  1091K|  9959   (1)| 00:02:00 |
    |*  1 |  TABLE ACCESS BY INDEX ROWID| T     |  9980 |  1091K|  9959   (1)| 00:02:00 |
    |   2 |   INDEX FULL SCAN           | T_IDX | 10000 |       |    35   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("COL1" IS NOT NULL AND "COL1" IS NOT NULL AND "COL1" LIKE '%'
                  OR ''='')
    
    15 rows selected.
    It simply refuses to apply the predicate on index level and therefore needs to access all table rows (INDEX FULL SCAN). If the index doesn't cover NULL values it cannot be used for this operation.

    See also the interesting filter predicate.

    As a side note, when changing this to a COUNT(*), this will happen:
    SQL> explain plan for
      2  select /*+ index(t t_idx) */ count(*) from t where col1 like '' || '%' or '' = '';
    
    Explained.
    
    SQL> select * from table(dbms_xplan.display);
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------------------
    
    Plan hash value: 995313729
    
    --------------------------------------------------------------------------
    | Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |       |     1 |    11 |    35   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE  |       |     1 |    11 |            |          |
    |*  2 |   INDEX FULL SCAN| T_IDX |  9980 |   107K|    35   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("COL1" IS NOT NULL AND "COL1" IS NOT NULL AND "COL1" LIKE
                  '%' OR ''='')
    
    15 rows selected.
    Surprisingly the predicate was now applied at index level... Definitely these kind of predicates produce some odd behaviour in the optimizer.

    It is also true that the optimizer in this particular case treats the version with bind variables differently from the one with literals, so even the correct COL1 LIKE :1 || '%' OR :1 IS NULL predicate will refuse to evaluate at index level:
    SQL> explain plan for
      2  select /*+ index(t t_idx) */ * from t where col1 like :1 || '%' or :1 is null;
    
    Explained.
    
    SQL> select * from table(dbms_xplan.display);
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------------------------------------
    
    Plan hash value: 3778778741
    
    -------------------------------------------------------------------------------------
    | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |       |   975 |   106K|  9960   (1)| 00:02:00 |
    |*  1 |  TABLE ACCESS BY INDEX ROWID| T     |   975 |   106K|  9960   (1)| 00:02:00 |
    |   2 |   INDEX FULL SCAN           | T_IDX | 10000 |       |    35   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(:1 IS NULL OR "COL1" LIKE :1||'%')
    
    14 rows selected.
    And therefore can not make efficient usage of the index.

    How could this be implemented correctly with good performance and using bind variables?

    There is this short-cut hard-coded into the optimizer (for more details see http://jonathanlewis.wordpress.com/2007/01/09/conditional-sql/):
    SQL> explain plan for
      2  select * from t where decode(:1, NULL, col1, :1) = col1;
    
    Explained.
    
    SQL> select * from table(dbms_xplan.display);
    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------------------------------
    
    Plan hash value: 3349452719
    
    ---------------------------------------------------------------------------------------
    | Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |       |  9991 |  1092K|    49   (0)| 00:00:01 |
    |   1 |  CONCATENATION                |       |       |       |            |          |
    |*  2 |   FILTER                      |       |       |       |            |          |
    |*  3 |    TABLE ACCESS FULL          | T     |  9990 |  1092K|    46   (0)| 00:00:01 |
    |*  4 |   FILTER                      |       |       |       |            |          |
    |   5 |    TABLE ACCESS BY INDEX ROWID| T     |     1 |   112 |     3   (0)| 00:00:01 |
    |*  6 |     INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter(:1 IS NULL)
       3 - filter("COL1" IS NOT NULL)
       4 - filter(:1 IS NOT NULL)
       6 - access("COL1"=:1)
    
    21 rows selected.
    You get the same plan with NVL(:1, COL1) = COL1. This will return the correct result if COL1 is defined as mandatory otherwise NULL values will again be filtered out. Note that this transformation only happens with a simple index - the function-based index above would prevent this transformation.

    Unfortunately this transformation is really hardcoded, so any deviation from this syntax disables it:
    SQL> explain plan for
      2  select * from t where col1 like decode(:1, NULL, col1, :1 || '%');
    
    Explained.
    
    SQL> select * from table(dbms_xplan.display);
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------
    
    Plan hash value: 1601196873
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |   500 | 56000 |    47   (3)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| T    |   500 | 56000 |    47   (3)| 00:00:01 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("COL1" LIKE DECODE(:1,NULL,"COL1",:1||'%'))
    
    13 rows selected.
    But we can help ourselves and write a similar statement manually:
    SQL> explain plan for
      2  select
      3          *
      4  from
      5          t
      6  where
      7          :1 is not null
      8  and     col1 like :1 || '%'
      9  union all
     10  select
     11          *
     12  from
     13          t
     14  where
     15          :1 is null;
    
    Explained.
    
    SQL> select * from table(dbms_xplan.display);
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------
    
    Plan hash value: 3124852083
    
    ---------------------------------------------------------------------------------------
    | Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |       | 10500 |  1148K|    91  (51)| 00:00:02 |
    |   1 |  UNION-ALL                    |       |       |       |            |          |
    |*  2 |   FILTER                      |       |       |       |            |          |
    |   3 |    TABLE ACCESS BY INDEX ROWID| T     |   500 | 56000 |    45   (0)| 00:00:01 |
    |*  4 |     INDEX RANGE SCAN          | T_IDX |    90 |       |     2   (0)| 00:00:01 |
    |*  5 |   FILTER                      |       |       |       |            |          |
    |   6 |    TABLE ACCESS FULL          | T     | 10000 |  1093K|    46   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter(:1 IS NOT NULL)
       4 - access("COL1" LIKE :1||'%')
           filter("COL1" LIKE :1||'%')
       5 - filter(:1 IS NULL)
    
    21 rows selected.
    This version even handles NULL correctly and uses the index if the bind variable is not null (if the hard parse peeks a non-null bind value). The FILTER predicate in this case is actually acting as a "conditional execution" operator - which means that is gets evaluated before its child rowsources which is usually not the case:
    SQL> variable x varchar2(20)
    SQL>
    SQL> alter session set statistics_level = all;
    
    Session altered.
    
    SQL> exec :x := 'BOSS'
    
    PL/SQL procedure successfully completed.
    
    SQL> select count(vc_small) from
      2  (
      3  select
      4          *
      5  from
      6          t
      7  where
      8          :x is not null
      9  and     col1 like :x || '%'
     10  union all
     11  select
     12          *
     13  from
     14          t
     15  where
     16          :x is null
     17  );
    COUNT(VC_SMALL)
    ---------------
                 10
    
    SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    
    SQL_ID  fhjkc2yrycm3n, child number 0
    -------------------------------------
    select count(vc_small) from ( select         * from         t where
        :x is not null and     col1 like :x || '%' union all select
    * from         t where         :x is null )
    
    Plan hash value: 1740072348
    
    ---------------------------------------------------------------------------------------------------
    | Id  | Operation                       | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ---------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                |       |      1 |        |      1 |00:00:00.01 |      12 |
    |   1 |  SORT AGGREGATE                 |       |      1 |      1 |      1 |00:00:00.01 |      12 |
    |   2 |   VIEW                          |       |      1 |  10001 |     10 |00:00:00.01 |      12 |
    |   3 |    UNION-ALL                    |       |      1 |        |     10 |00:00:00.01 |      12 |
    |*  4 |     FILTER                      |       |      1 |        |     10 |00:00:00.01 |      12 |
    |   5 |      TABLE ACCESS BY INDEX ROWID| T     |      1 |      1 |     10 |00:00:00.01 |      12 |
    |*  6 |       INDEX RANGE SCAN          | T_IDX |      1 |      1 |     10 |00:00:00.01 |       2 |
    |*  7 |     FILTER                      |       |      1 |        |      0 |00:00:00.01 |       0 |
    |   8 |      TABLE ACCESS FULL          | T     |      0 |  10000 |      0 |00:00:00.01 |       0 |
    ---------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - filter(:X IS NOT NULL)
       6 - access("COL1" LIKE :X||'%')
           filter("COL1" LIKE :X||'%')
       7 - filter(:X IS NULL)
    
    SQL> exec :x := null
    
    PL/SQL procedure successfully completed.
    
    SQL> select count(vc_small) from
      2  (
      3  select
      4          *
      5  from
      6          t
      7  where
      8          :x is not null
      9  and     col1 like :x || '%'
     10  union all
     11  select
     12          *
     13  from
     14          t
     15  where
     16          :x is null
     17  );
    COUNT(VC_SMALL)
    ---------------
              10000
    
    SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------
    
    SQL_ID  fhjkc2yrycm3n, child number 0
    -------------------------------------
    select count(vc_small) from ( select         * from         t where
        :x is not null and     col1 like :x || '%' union all select
    * from         t where         :x is null )
    
    Plan hash value: 1740072348
    
    ---------------------------------------------------------------------------------------------------
    | Id  | Operation                       | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ---------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                |       |      1 |        |      1 |00:00:00.16 |     166 |
    |   1 |  SORT AGGREGATE                 |       |      1 |      1 |      1 |00:00:00.16 |     166 |
    |   2 |   VIEW                          |       |      1 |  10001 |  10000 |00:00:00.15 |     166 |
    |   3 |    UNION-ALL                    |       |      1 |        |  10000 |00:00:00.12 |     166 |
    |*  4 |     FILTER                      |       |      1 |        |      0 |00:00:00.01 |       0 |
    |   5 |      TABLE ACCESS BY INDEX ROWID| T     |      0 |      1 |      0 |00:00:00.01 |       0 |
    |*  6 |       INDEX RANGE SCAN          | T_IDX |      0 |      1 |      0 |00:00:00.01 |       0 |
    |*  7 |     FILTER                      |       |      1 |        |  10000 |00:00:00.05 |     166 |
    |   8 |      TABLE ACCESS FULL          | T     |      1 |  10000 |  10000 |00:00:00.02 |     166 |
    ---------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - filter(:X IS NOT NULL)
       6 - access("COL1" LIKE :X||'%')
           filter("COL1" LIKE :X||'%')
       7 - filter(:X IS NULL)
    You can see that the STARTS and BUFFERS column show that this part of the execution plan has been short-cut and not been executed at all.

    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    Co-author of the "OakTable Expert Oracle Practices" book:
    http://www.apress.com/book/view/1430226684
    http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points