This discussion is archived
9 Replies Latest reply: Jan 21, 2010 5:46 AM by user12068799 RSS

Transitive Closure Behaviour

Yasu Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    Check this:

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

    HTH
    -Anantha
  • 2. Re: Transitive Closure Behaviour
    Yasu Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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
    CharlesHooper Expert
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Words can't be used to say thanks for you....

    Thanks a lot..

    -Yasser
  • 9. Re: Transitive Closure Behaviour
    user12068799 Newbie
    Currently Being Moderated
    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.

Legend

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