9 Replies Latest reply: Jan 21, 2010 7:46 AM by user12068799 RSS

    Transitive Closure Behaviour

    Yasu
      Hi All,

      Sometimes predicates can be generated due to Transitive Closure, but is it possible to see below mentioned behaviour.
      You can even have cases like this one: if n1 > 10 and n1 < 0, then 0 > 10, which is always
      false, and therefore can short-circuit an entire branch of an execution plan.
      I found this from Jonathan Lewis CBO book, page no: 143 in Chapter: Selectivity Issues

      Is it possible to create test case for observing this behaviour??

      -Yasser
        • 1. Re: Transitive Closure Behaviour
          591186
          Check this:

          Transitive Closure
          [http://jonathanlewis.wordpress.com/2007/01/01/transitive-closure/]

          HTH
          -Anantha
          • 2. Re: Transitive Closure Behaviour
            Yasu
            Thanks...i already read it and this article does not mention any scenario as mentioned below.
            You can even have cases like this one: if n1 > 10 and n1 < 0, then 0 > 10, which is always
            false, and therefore can short-circuit an entire branch of an execution plan.
            -Yasser
            • 3. Re: Transitive Closure Behaviour
              591186
              Perhaps, below MOS notes would help.


              My(not mine ;) ) Experience With Impact of Various Settings of Parameter query_rewrite_enabled on SQL Performance - 416245.1
              Transitivity and Transitive Closure - 68979.1



              -Anantha
              • 4. Re: Transitive Closure Behaviour
                Jonathan Lewis
                YasserRACDBA wrote:
                Hi All,

                Sometimes predicates can be generated due to Transitive Closure, but is it possible to see below mentioned behaviour.
                You can even have cases like this one: if n1 > 10 and n1 < 0, then 0 > 10, which is always
                false, and therefore can short-circuit an entire branch of an execution plan.
                I found this from Jonathan Lewis CBO book, page no: 143 in Chapter: Selectivity Issues

                Is it possible to create test case for observing this behaviour??
                How much effort does it take ?

                We need a table with a column called n1, and we need a query with both those predicates.

                How about this cut-n-paste from an SQL*Plus session (running under 10.2.0.3 - which explains why the contradiction has been resoved to "null is not null"):
                SQL> create table t1 (n1 number);
                
                Table created.
                
                SQL> set autotrace traceonly explain
                SQL> select * from t1 where n1 < 0 and n1 > 10;
                
                Execution Plan
                ----------------------------------------------------------
                Plan hash value: 3332582666
                
                ---------------------------------------------------------------------------
                | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                ---------------------------------------------------------------------------
                |   0 | SELECT STATEMENT   |      |     1 |    13 |     0   (0)|          |
                |*  1 |  FILTER            |      |       |       |            |          |
                |*  2 |   TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
                ---------------------------------------------------------------------------
                
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                
                   1 - filter(NULL IS NOT NULL)
                   2 - filter("N1"<0 AND "N1">10)
                
                Note
                -----
                   - dynamic sampling used for this statement
                Regards
                Jonathan Lewis
                http://jonathanlewis.wordpress.com
                http://www.jlcomp.demon.co.uk

                To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
                {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
                fixed format
                .
                
                
                "Science is more than a body of knowledge; it is a way of thinking" 
                Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                • 5. Re: Transitive Closure Behaviour
                  Charles Hooper
                  YasserRACDBA wrote:
                  Hi All,

                  Sometimes predicates can be generated due to Transitive Closure, but is it possible to see below mentioned behaviour.
                  You can even have cases like this one: if n1 > 10 and n1 < 0, then 0 > 10, which is always
                  false, and therefore can short-circuit an entire branch of an execution plan.
                  I found this from Jonathan Lewis CBO book, page no: 143 in Chapter: Selectivity Issues

                  Is it possible to create test case for observing this behaviour??

                  -Yasser
                  I see that Jonathan has already answered your question. Here is a little more evidence:
                  CREATE TABLE T1 AS
                  SELECT
                    ROWNUM C1,
                    ROWNUM C2
                  FROM
                    DUAL
                  CONNECT BY
                    LEVEL<=100000;
                     
                  COMMIT;
                   
                  EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1')
                   
                  ALTER SYSTEM FLUSH BUFFER_CACHE;
                  ALTER SYSTEM FLUSH BUFFER_CACHE;
                  Now we have a simple table with 100,000 rows in it, and nothing in the buffer cache. Now, set STATISTICS_LEVEL to ALL and try a query:
                  ALTER SESSION SET STATISTICS_LEVEL='ALL';
                   
                  SELECT
                    *
                  FROM
                    T1
                  WHERE
                    C1<=100
                    AND C2>=10000
                    AND C1>C2;
                   
                  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
                   
                  SQL_ID  3uk9dajd5cn74, child number 0
                  -------------------------------------
                  SELECT T1.* FROM T1 WHERE     C1<=100    AND C2>=10000    AND C1>C2
                   
                  Plan hash value: 3332582666
                   
                  ---------------------------------------------------------------------------
                  | Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   |
                  ---------------------------------------------------------------------------
                  |*  1 |  FILTER            |      |      1 |        |      0 |00:00:00.01 |
                  |*  2 |   TABLE ACCESS FULL| T1   |      0 |      1 |      0 |00:00:00.01 |
                  ---------------------------------------------------------------------------
                   
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                     1 - filter(NULL IS NOT NULL)
                     2 - filter(("C1">"C2" AND "C2"<100 AND "C1"<=100 AND "C1">10000 AND 
                                "C2">=10000))
                  If you had also created a 10046 trace, you could further confirm that there were no physical reads:
                  PARSING IN CURSOR #2 len=68 dep=0 uid=31 oct=3 lid=31 tim=3008717127 hv=1515606244 ad='a775f1c8'
                  SELECT T1.* FROM T1 WHERE  
                    C1<=100 
                    AND C2>=10000 
                    AND C1>C2 
                  END OF STMT
                  PARSE #2:c=0,e=2320,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=3008717120
                  BINDS #2:
                  EXEC #2:c=0,e=123,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=3008717834
                  WAIT #2: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=3008717930
                  FETCH #2:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=3008718038
                  WAIT #2: nam='SQL*Net message from client' ela= 1777 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=3008719953
                  STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='FILTER  (cr=0 pr=0 pw=0 time=5 us)'
                  STAT #2 id=2 cnt=0 pid=1 pos=1 obj=114196 op='TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 time=0 us)'
                  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.

                  Edited by: Charles Hooper on Jan 20, 2010 2:48 PM
                  No changes, but note that this is a slightly different test case that uses 2 columns, rather than a single column.
                  • 6. Re: Transitive Closure Behaviour
                    damorgan
                    An interesting demo from one standpoint:
                    SQL> set autotrace traceonly explain
                    SQL> select * from t1 where n1 < 0 and n1 > 10;
                    
                    Execution Plan
                    ----------------------------------------------------------
                    Plan hash value: 3332582666
                    
                    ---------------------------------------------------------------------------
                    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                    ---------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT   |      |     1 |    13 |     0   (0)|          |
                    |*  1 |  FILTER            |      |       |       |            |          |
                    |*  2 |   TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
                    ---------------------------------------------------------------------------
                    
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                    
                       1 - filter(NULL IS NOT NULL)
                       2 - filter("N1"<0 AND "N1">10)
                    
                    Note
                    -----
                       - dynamic sampling used for this statement
                    when juxtaposed with this:
                    SQL> explain plan for
                      2  select * from t1 where n1 < 0 and n1 > 10;
                    
                    Explained.
                    
                    SQL> SELECT * FROM TABLE(dbms_xplan.display);
                    
                    Execution Plan
                    ----------------------------------------------------------
                    Plan hash value: 2137789089
                    
                    ---------------------------------------------------------------------------------------------
                    | Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
                    ---------------------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    21   (5)| 00:00:01 |
                    |   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |       |       |            |          |
                    ---------------------------------------------------------------------------------------------
                    • 7. Re: Transitive Closure Behaviour
                      Yasu
                      I am really sorry...it's my mistake for not understanding your idiom with the predicate "null is not null"
                      and therefore can short-circuit an entire branch of an execution plan.
                      Thanks a lot...

                      -Yasser
                      • 8. Re: Transitive Closure Behaviour
                        Yasu
                        Words can't be used to say thanks for you....

                        Thanks a lot..

                        -Yasser
                        • 9. Re: Transitive Closure Behaviour
                          user12068799
                          Sometimes Oracle uses even more "sophisticated" transitive closure. Here is a recent [funny case|http://sql.ru/forum/actualthread.aspx?tid=728055] appeared on the russian Oracle forum SQL.ru:
                          select /*+ qb_name(main) */ *
                              from
                                (
                                  select /*+ qb_name(qb_2) */ 
                                         (select /*+ qb_name(qb_1) */ dummy
                                          from DUAL
                                          where d.dummy = d.dummy
                                         ) as x
                                  from DUAL d
                                  where d.dummy in (select /*+ qb_name(sq) */ dummy from DUAL)
                               ) t
                             where t.x != t.x;
                          And here is how it looks like after CBO's transformations:
                          select /*+ QB_NAME ("QB_2") QB_NAME ("MAIN") */
                           (select /*+ QB_NAME ("QB_1") */
                             "DUAL"."DUMMY"
                              from "SYS"."DUAL" "DUAL"
                             where "$vm_view"."$vm_col_2" = "$vm_view"."$vm_col_2") "X"
                            from (select /*+ QB_NAME ("SQ") QB_NAME ("QB_2") */
                                  distinct "SYS_ALIAS_1".rowid "ROWID", "DUAL"."DUMMY" "$vm_col_1", "SYS_ALIAS_1"."DUMMY" "$vm_col_2"
                                    from "SYS"."DUAL" "DUAL", "SYS"."DUAL" "SYS_ALIAS_1"
                                   where "SYS_ALIAS_1"."DUMMY" = "DUAL"."DUMMY"
                                     AND  (SELECT /*+ QB_NAME ("QB_1") */ "DUAL"."DUMMY" FROM "SYS"."DUAL" "DUAL") !=
                                          (SELECT /*+ QB_NAME ("QB_1") */ "DUAL"."DUMMY" FROM "SYS"."DUAL" "DUAL")) "$vm_view"
                          10.2.0.4 loses last predicate somewhere and the query returns wrong results.