Conditional SQL – 4 | Oracle Scratchpad

January 2, 2014

Conditional SQL – 4

Filed under: Conditional SQL,Execution plans,Oracle — Jonathan Lewis @ 6:14 pm GMT 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).

Comments (7)

7 Comments »

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 GMT 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 GMT 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 GMT 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 GMT Jan 3,2014

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 GMT Jan 3,2014 | Reply

Oracle has stopped using concatenation. I believe there is simple bug. Although conditions at line 2 and 6 are mutually exclusive, oracle counts the final cost as addition of COST for PLAN ID 3 COST for PLAN ID 7, instead of using maximal value. I have seen this in 11g before, but now we are upgrading for customer mission critical database on 12.1.0.2 and so it has passed through my hands again. As original system is Oracle 9.2, I can easily check there that oracle was smart enough in 9.2 to use concatenation without any help by hints / code rewrite.

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                         |     2 |  2410 |    84   (0)| 00:00:01 |       |       |
|   1 |  CONCATENATION                               |                         |       |       |            |          |       |       |
|*  2 |   FILTER                                     |                         |       |       |            |          |       |       |
|   3 |    PARTITION RANGE INLIST                    |                         |     1 |  1205 |    34   (0)| 00:00:01 |KEY(I) |KEY(I) |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TABLE1                  |     1 |  1205 |    34   (0)| 00:00:01 |KEY(I) |KEY(I) |
|*  5 |      INDEX RANGE SCAN                        | I1                      |     1 |       |    33   (0)| 00:00:01 |KEY(I) |KEY(I) |
|*  6 |   FILTER                                     |                         |       |       |            |          |       |       |
|   7 |    PARTITION RANGE INLIST                    |                         |     1 |  1205 |    50   (0)| 00:00:01 |KEY(I) |KEY(I) |
|*  8 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TABLE1                  |     1 |  1205 |    50   (0)| 00:00:01 |KEY(I) |KEY(I) |
|*  9 |      INDEX RANGE SCAN                        | I1                      |     1 |       |    49   (0)| 00:00:01 |KEY(I) |KEY(I) |
----------------------------------------------------------------------------------------------------------------------------------------

Comment by Pavol Babel — February 20, 2016 @ 3:47 am GMT Feb 20,2016 | Reply

[…] the most significant difference between concatenation and expansion – concatenation requires indexed access paths in all branches, or-expansion […]

Pingback by Conditional SQL – 5 | Oracle Scratchpad — March 2, 2018 @ 12:49 pm GMT Mar 2,2018 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome. Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Oracle Scratchpad

January 2, 2014