Oracle Scratchpad

January 2, 2014

Conditional SQL – 4

Filed under: Conditional SQL,Execution plans,Oracle — Jonathan Lewis @ 6:14 pm BST Jan 2,2014

This is one of those posts where the investigation is left as an exercise – it’s not difficult, just something that will take a little time that I don’t have, and just might end up with me chasing half a dozen variations (so I’d rather not get sucked into looking too closely). It comes from an OTN question which ends up reporting this predicate:

WHERE ( LENGTH ( :b7) IS NULL OR
         UPPER (TRIM (CODFSC)) = UPPER (TRIM ( :b8)) or
         UPPER (TRIM (CODUIC)) = UPPER (TRIM ( :b9)))
       AND STATE = 0;

The three bind variables all hold the same value; there is a function-based index on upper(trim(codfsc)), and another on upper(trim(coduic)). The execution plan for this query is a full tablescan, but if you eliminate the first predicate Oracle can do a concatenation of two index range scans. This variation doesn't surprise me, the optimizer's ability to introduce concatenation is limited; however, I did wonder whether some small variation in the SQL would allow the optimizer to get just a little more clever.

Would you get concatenation if you changed the first predicate to (:b7 is null); if not, would a similar query that didn't depend on function-based indexes do concatenation; if not is there any rewrite of this query that could do a tablescan ONLY for the case where :b7 was null ?

Demonstrations of any levels of success can be left in the comments if anyone's interested. To get a fixed font that preserves space start the code with "sourcecode" and end with "/sourcecode" (removing the quotation marks and replacing them with square brackets).

5 Comments »

  1. Latest update – on the thread – by Franck Pachot.

    The “use_concat” hint has been extended to include an “or_predicates(N)” parameter – the N identifies which of the query block predicates should be expanded, so it’s a little fragile, as well as being undocumented. This allows the original query to do the necessary expansion.

    I keep forgetting that OR-expansion requires (by default) that there is an indexed-access option for each generated branch; and I also keep forgetting that the special “:bind is null” concatenation appears only (by default) for the special case of “columnX = nvl(:b1, columnX)”.

    Comment by Jonathan Lewis — January 3, 2014 @ 11:47 am BST Jan 3,2014 | Reply

    • Hi Jonathan,

      I’ve never been able to understand exactly how predicates in or_predicates() are numbered. For example:

        select /*+ use_concat */ count(*) from TEST where ( d=1 ) and ( a=:v or b=:v or c=:v )

      generates the following outline when we have an index access for a, b and c:

         USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(2))

      but

        select /*+ use_concat */ count(*) from TEST where ( d=1 or e=1 ) and ( a=:v or b=:v or c=:v )

      generates

        USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(4))

      And there is also another use_concat attribute when predicates are reordered during optimization:

        select /*+ use_concat */ count(*) from TEST where ( d=1 and e=1 or f=1  ) and ( a=:v or b=:v or c=:v )

      generates

        USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(6) PREDICATE_REORDERS((5 2) (2 3) (3 4) (4 5)))

      Anyway, I'll never use that in production. Rewriting the query so that each branch has an index access (such as with nvl or decode) is probably the best way when possible.

      Regards,
      Franck.

      Comment by Franck Pachot — January 3, 2014 @ 2:44 pm BST Jan 3,2014 | Reply

      • Franck,

        I’m guessing you have single column indexes on a, b, and c.

        I’ve only ever had to play with “simple” examples – and then I’ve usually done “union all” rewrites rather than mess with undocumented hints, so I haven’t tried to work out exactly what the numbers mean.

        Have you also seen examples the include this type of thing “OR_PREDICATES(6 18 43)” inside the use_concat() hint ?

        Comment by Jonathan Lewis — January 3, 2014 @ 3:36 pm BST Jan 3,2014 | Reply

        • Jonathan,
          Right I’ve single column indexes on a, b, and c

          Yes I’ve seen such or_predicates with complex queries. For example USE_CONCAT(@”SEL$1″ 8 OR_PREDICATES(1 8 18)) there:

          
             SQL_ID btyty029db4gz, child number 0
             -------------------------------------
             select /*+ use_concat */ count(*) from TEST where ( a=:u or b=:u ) and ( a=:v or b=:v )
          
             Plan hash value: 2201984993
          
             -------------------------------------------------------
             | Id  | Operation                             | Name  |
             -------------------------------------------------------
             |   0 | SELECT STATEMENT                      |       | 
             |   1 |  SORT AGGREGATE                       |       | 
             |   2 |   CONCATENATION                       |       |
             |   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TEST  |
             |*  4 |     INDEX RANGE SCAN                  | TESTB |
             |*  5 |    TABLE ACCESS BY INDEX ROWID BATCHED| TEST  |
             |*  6 |     INDEX RANGE SCAN                  | TESTA |
             |*  7 |    TABLE ACCESS BY INDEX ROWID BATCHED| TEST  |
             |*  8 |     INDEX RANGE SCAN                  | TESTA |
             |*  9 |    TABLE ACCESS BY INDEX ROWID BATCHED| TEST  |
             |* 10 |     INDEX RANGE SCAN                  | TESTA |
             -------------------------------------------------------
          
             Query Block Name / Object Alias (identified by operation id):
             -------------------------------------------------------------
          
             1 - SEL$1
             3 - SEL$1_1 / TEST@SEL$1
             4 - SEL$1_1 / TEST@SEL$1
             5 - SEL$1_2 / TEST@SEL$1_2
             6 - SEL$1_2 / TEST@SEL$1_2
             7 - SEL$1_3 / TEST@SEL$1_3
             8 - SEL$1_3 / TEST@SEL$1_3
             9 - SEL$1_4 / TEST@SEL$1_4
             10 - SEL$1_4 / TEST@SEL$1_4
          
             Outline Data
             -------------
          
             /*+
             BEGIN_OUTLINE_DATA
             IGNORE_OPTIM_EMBEDDED_HINTS
             OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
             DB_VERSION('12.1.0.1')
             ALL_ROWS
             OUTLINE_LEAF(@"SEL$1")
             OUTLINE_LEAF(@"SEL$1_1")
             USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1 8 18))
             OUTLINE_LEAF(@"SEL$1_2")
             OUTLINE_LEAF(@"SEL$1_3")
             OUTLINE_LEAF(@"SEL$1_4")
             OUTLINE(@"SEL$1")
             INDEX_RS_ASC(@"SEL$1_1" "TEST"@"SEL$1" ("TEST"."B"))
             BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1_1" "TEST"@"SEL$1")
             INDEX_RS_ASC(@"SEL$1_2" "TEST"@"SEL$1_2" ("TEST"."A"))
             BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1_2" "TEST"@"SEL$1_2")
             INDEX_RS_ASC(@"SEL$1_3" "TEST"@"SEL$1_3" ("TEST"."A"))
             BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1_3" "TEST"@"SEL$1_3")
             INDEX_RS_ASC(@"SEL$1_4" "TEST"@"SEL$1_4" ("TEST"."A"))
             BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1_4" "TEST"@"SEL$1_4")
             END_OUTLINE_DATA
             */
          
             Predicate Information (identified by operation id):
             ---------------------------------------------------
          
             4 - access("B"=:U)
                 filter("B"=:V)
             5 - filter(("B"=:U AND LNNVL("B"=:V)))
             6 - access("A"=:V)
             7 - filter(("B"=:V AND LNNVL("B"=:U)))
             8 - access("A"=:U)
             9 - filter((LNNVL("B"=:V) AND LNNVL("B"=:U)))
             10 - access("A"=:U)
                  filter("A"=:V)
          

          Comment by Franck Pachot — January 3, 2014 @ 4:01 pm BST Jan 3,2014

  2. This is the model

    CREATE TABLE T1 (
          n1     number,
          CODFSC varchar2(12),
          CODUIC varchar2(12),
          STATE  NUMBER
       );
    
     INSERT INTO
          T1
        SELECT
            ROWNUM
            ,dbms_random.string('s',12)
            ,dbms_random.string('s',12)
            ,trunc((rownum -1)/3)
        FROM
            DUAL
         CONNECT BY
           LEVEL <= 1e4
      ;
    
    CREATE INDEX ind_fsc ON T1(upper(trim(codfsc)));
    CREATE INDEX ind_uic ON T1(upper(trim(CODUIC)));
    
    exec dbms_stats.gather_table_stats(user, 't1'
                                      ,cascade => true
    					 ,method_opt => 'for all hidden columns'
    					 ,estimate_percent => dbms_stats.auto_sample_size
    					 );
    

    Nothing is noticeable when I changed the first predicate to (:b7 is null)

    explain plan for
        select *
        from t1
        WHERE ( :b7 IS NULL OR
                 UPPER (TRIM (CODFSC)) = UPPER (TRIM ( :b8)) or
                 UPPER (TRIM (CODUIC)) = UPPER (TRIM ( :b9)))
               AND STATE = 0;
    
    select * from table(dbms_xplan.display);
    
    Plan hash value: 3617692013
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |     5 |   300 |    31   (4)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| T1   |     5 |   300 |    31   (4)| 00:00:01 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter("STATE"=0 AND (:B7 IS NULL OR
                  UPPER(TRIM("CODFSC"))=UPPER(TRIM(:B8)) OR
                  UPPER(TRIM("CODUIC"))=UPPER(TRIM(:B9))))
    

    When I take out the first predicate from the query I got the following plan (in 10.2.0.4 and 11.2.0.3)

    explain plan for
        select  *
        from t1
        WHERE (
                 UPPER (TRIM (CODFSC)) = UPPER (TRIM ( :b8)) or
                 UPPER (TRIM (CODUIC)) = UPPER (TRIM ( :b9)))
               AND STATE = 0;
    
    Plan hash value: 1700791370
    
    --------------------------------------------------------------------------------------------
    | Id  | Operation                        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                 |         |     1 |    60 |     2   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS BY INDEX ROWID     | T1      |     1 |    60 |     2   (0)| 00:00:01 |
    |   2 |   BITMAP CONVERSION TO ROWIDS    |         |       |       |            |          |
    |   3 |    BITMAP OR                     |         |       |       |            |          |
    |   4 |     BITMAP CONVERSION FROM ROWIDS|         |       |       |            |          |
    |*  5 |      INDEX RANGE SCAN            | IND_FSC |       |       |     1   (0)| 00:00:01 |
    |   6 |     BITMAP CONVERSION FROM ROWIDS|         |       |       |            |          |
    |*  7 |      INDEX RANGE SCAN            | IND_UIC |       |       |     1   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter("STATE"=0)
       5 - access(UPPER(TRIM("CODFSC"))=UPPER(TRIM(:B8)))
       7 - access(UPPER(TRIM("CODUIC"))=UPPER(TRIM(:B9)))
    

    When I did a manual concatenation I got the following plan

    explain plan for
      SELECT * from t1
        WHERE STATE = 0
        AND LENGTH (:b7) IS NULL
      UNION ALL
       SELECT * from t1
        WHERE STATE = 0
        AND LENGTH (:b7) IS NOT NULL
        AND
         (UPPER (TRIM (CODFSC)) = UPPER (TRIM ( :b8))
          OR
          UPPER (TRIM (CODUIC)) = UPPER (TRIM ( :b9))
         );
    
    select * from table(dbms_xplan.display);
    
    Plan hash value: 50362709
    
    ----------------------------------------------------------------------------------------------
    | Id  | Operation                          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                   |         |   102 |  6120 |    41  (27)| 00:00:01 |
    |   1 |  UNION-ALL                         |         |       |       |            |          |
    |*  2 |   FILTER                           |         |       |       |            |          |
    |*  3 |    TABLE ACCESS FULL               | T1      |   100 |  6000 |    31   (4)| 00:00:01 |
    |*  4 |   FILTER                           |         |       |       |            |          |
    |*  5 |    TABLE ACCESS BY INDEX ROWID     | T1      |     2 |   120 |    11   (0)| 00:00:01 |
    |   6 |     BITMAP CONVERSION TO ROWIDS    |         |       |       |            |          |
    |   7 |      BITMAP OR                     |         |       |       |            |          |
    |   8 |       BITMAP CONVERSION FROM ROWIDS|         |       |       |            |          |
    |*  9 |        INDEX RANGE SCAN            | IND_FSC |       |       |     1   (0)| 00:00:01 |
    |  10 |       BITMAP CONVERSION FROM ROWIDS|         |       |       |            |          |
    |* 11 |        INDEX RANGE SCAN            | IND_UIC |       |       |     1   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter(LENGTH(:B7) IS NULL)
       3 - filter("STATE"=0)
       4 - filter(LENGTH(:B7) IS NOT NULL)
       5 - filter("STATE"=0)
       9 - access(UPPER(TRIM("CODFSC"))=UPPER(TRIM(:B8)))
      11 - access(UPPER(TRIM("CODUIC"))=UPPER(TRIM(:B9)))
    

    Best regards

    Comment by hourim — January 3, 2014 @ 12:02 pm BST Jan 3,2014 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,873 other followers