1 Reply Latest reply: Feb 26, 2014 2:08 AM by Jonathan Lewis RSS

    Interesting case of difference of 11gR1 and 11gR2

    Bono

      Hi, All


      I found some interesting things recently. I want to share it everyone.

       

      What is the difference below conditions?

       

      SQL1  :   where (c1=3 and c2='A') or (c1=3)
      SQL2  :   where (c1=3) or (c1=3 and c2='A')

       

      Logically SQL1 and SQL2 are same condition this. (c1=3)
      (c1=3 and c2='A') ← This parts are useless conditions.


      You can find interesting thing on the below test. (Ver. 11gR2)


      Prepare Test Objects

       

      drop table t1 purge;

       

      create table t1
      (c1 number, c2 varchar2(2));

       

      create index t1_x01 on t1(c1);

       

      insert into t1
      select 3, 'C'
      from dual
      connect by level<=3
      union all
      select level, chr(ascii('A')+level)
      from dual
      connect by level<=5;

       

      commit;

       

      exec dbms_stats.gather_table_stats(user,'t1');


      on the ver.11gR1 ('11.1.0.6')

       

      alter session set optimizer_features_enable='11.1.0.6';

      SQL1

       

      explain plan for
      select *
      from t1
      where (c1=3 and c2='A') or (c1=3);

       

      set linesize 200
      set pagesize 9999
      select * from table(dbms_xplan.display);


      -------------------------------------------------------------------------------
      | Id  | Operation                                          | Name    |
      -------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                       |             |
      |   1 |  TABLE ACCESS BY INDEX ROWID  |  T1        |
      |*  2 |   INDEX RANGE SCAN                      |  T1_X01 |
      -------------------------------------------------------------------------------

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

         2 - access("C1"=3)

      ⇒(c1=3 and c2='A') was eliminated in the predicate information.

        
      SQL2

       

      explain plan for
      select *
      from t1
      where (c1=3) or (c1=3 and c2='A');


      set linesize 200
      set pagesize 9999
      select * from table(dbms_xplan.display);


      --------------------------------------------------------
      | Id  | Operation                      | Name |
      --------------------------------------------------------
      |   0 | SELECT STATEMENT   |           |
      |*  1 |  TABLE ACCESS FULL | T1      |
      --------------------------------------------------------

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

        1 - filter("C1"=3 OR "C2"='A' AND "C1"=3)

      ⇒(c1=3 and c2='A') wasn't eliminated in the predicate information.

       

      The reason that Useless Condition (c1=3 and c2='A') was eliminated is CSE.

       

      CSE(Common Subexpression Elimination)


      CSE is a kind of Query Tranformations.

       

      You can confirm it in the 10053 trace file.

       

      ※You need hard parsing to make 10053 trace file.

       

      SQL1

       

      alter session set tracefile_identifier='11gR1_SQL1_53';

      alter session set events  '10053 trace name context forever, level 1';

       

      select  *
      from t1
      where (c1=3 and c2='A') or (c1=3);

       

      alter session set events '10053 trace name context off';

      You can see the trace file directory with below command.

       

      show parameter user_dump_dest

       

      Trace file was made like below.

       

      XXXX_ora_XXXX_11gR1_SQL1_53.trc

       

      You can find CSE with Text Editor.

      Ctrl + F "try to generate"

      ***************************************************************************************************

      try to generate transitive predicate from check constraints for query block SEL$1 (#0)
      finally: "T1"."C1"=3 AND ("T1"."C2"='A' OR 0=0)

      ***************************************************************************************************

      Ctrl + F "after transformations"

       

      ************************************************************************************************************************************

      Final query after transformations:******* UNPARSED QUERY IS *******
      SELECT "T1"."C1" "C1","T1"."C2" "C2" FROM "XXXX"."T1" "T1" WHERE "T1"."C1"=3 AND ("T1"."C2"='A' OR 0=0)

      ************************************************************************************************************************************

      ⇒Query Transformation was successed. (OR 0=0)


      SQL2

       

      alter session set tracefile_identifier='11gR1_SQL2_53';

      alter session set events  '10053 trace name context forever, level 1';

       

      select  *
      from t1
      where (c1=3) or (c1=3 and c2='A');

       

      alter session set events '10053 trace name context off';

       

      *******************************************************************************************************************************************
      try to generate transitive predicate from check constraints for query block SEL$1 (#0)
      finally: "T1"."C1"=3 OR "T1"."C1"=3 AND "T1"."C2"='A'

       

      Final query after transformations:******* UNPARSED QUERY IS *******
      SELECT "T1"."C1" "C1","T1"."C2" "C2" FROM "XXXX"."T1" "T1" WHERE "T1"."C1"=3 OR "T1"."C1"=3 AND "T1"."C2"='A'

      *******************************************************************************************************************************************

      ⇒Query Transformation was failed.


      on the ver.11gR2 ('11.2.0.1')

       

      alter session set optimizer_features_enable='11.2.0.1';

      SQL1

       

      explain plan for
      select *
      from t1
      where (c1=3 and c2='A') or (c1=3);

       

      set linesize 200
      set pagesize 9999
      select * from table(dbms_xplan.display);


      -------------------------------------------------------------------------------
      | Id  | Operation                                         | Name     |
      -------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                       |              |
      |   1 |  TABLE ACCESS BY INDEX ROWID  | T1          |
      |*  2 |   INDEX RANGE SCAN                      | T1_X01  |
      -------------------------------------------------------------------------------

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

        2 - access("C1"=3)

      ⇒Query Transformation was successed.


      SQL2

       

      explain plan for
      select *
      from t1
      where (c1=3) or (c1=3 and c2='A');


      set linesize 200
      set pagesize 9999
      select * from table(dbms_xplan.display);

       

      ------------------------------------------------------------------------------

      | Id  | Operation                                           | Name  |

      ------------------------------------------------------------------------------

      |   0 | SELECT STATEMENT                        |            |

      |   1 |  TABLE ACCESS BY INDEX ROWID   | T1        |

      |*  2 |   INDEX RANGE SCAN                      | T1_X01 |

      ------------------------------------------------------------------------------

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

        2 - access("C1"=3)

      ⇒Query Transformation was successed.

        
        
      CSE Working!
                                                                               11gR1             11gR2
      -------------------------------------------------------------   -----------------------      --------------                                          
      SQL1 : where (c1=3 and c2='A') or (c1=3)          Working            Working

      SQL2 : where (c1=3) or (c1=3 and c2='A')        not Working       Working


      I think that CSE was improved between 11gR1 and 11gR2.

       

      In addition,

      CSE off like this

       

      alter session set "_eliminate_common_subexpr" = false;

       

      explain plan for
      select *
      from t1
      where (c1=3 and c2='A') or (c1=3);

      set linesize 200
      set pagesize 9999
      select * from table(dbms_xplan.display);

      --------------------------------------------------------
      | Id  | Operation                      | Name |
      --------------------------------------------------------
      |   0 | SELECT STATEMENT   |          |
      |*  1 |  TABLE ACCESS FULL | T1     |
      -------------------------------------------------------

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

        1 - filter("C1"=3 OR "C2"='A' AND "C1"=3)

        
      CSE is not working!  

       

      Regards eqon