Oracle Scratchpad

January 1, 2007

Transitive Closure

Filed under: CBO,Execution plans,Tuning — Jonathan Lewis @ 6:52 pm BST Jan 1,2007

I have mentioned transitive closure in the past, and the comments on this blog item go into some depth about some of the effects of transitive closure.  However, a recent incoming link has prompted me to point out a couple of further details about the appearance (or non-appearance) of transitive closure.

We start with three (virtually) identical tables, created and populated as follows:

create table t1 (
	col1	number,
	v1	varchar2(200)
);           

insert into t1
select
	mod(rownum,100),
	rpad('x',200)
from
	all_objects
where
	rownum <= 2000
;           

commit;

The t2 and t3 tables are identical, with the obvious changes to column names. As a first test, we run the following query with autotrace enabled (using 10gR2 to get the dbms_xplan version of the output):

select t1.*, t2.*, t3.*
from
	t1, t2, t3
where
	t2.col2 = t1.col1
and	t3.col3 = t2.col2
; 

Execution Plan
----------------------------------------------------------
Plan hash value: 1573120526
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |   800K|   464M|   220 |
|*  1 |  HASH JOIN          |      |   800K|   464M|   220 |
|   2 |   TABLE ACCESS FULL | T3   |  2000 |   396K|     7 |
|*  3 |   HASH JOIN         |      | 40000 |    15M|    25 |
|   4 |    TABLE ACCESS FULL| T1   |  2000 |   396K|     7 |
|   5 |    TABLE ACCESS FULL| T2   |  2000 |   396K|     7 |
------------------------------------------------------------          

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T3"."COL3"="T2"."COL2")
   3 - access("T2"."COL2"="T1"."COL1")

Check the predicate list. Although transitive closure tells us that Oracle can take “col1 = col2 and col2 = {constant}” and infer that “col1 = {constant}”, it has not used our two predicates in the query above to infer that “t1.col1 = t3.col3″. Transitive closure is relevant only if there are some constants in the mixture. So let’s demonstrate this by adding one more predicate to our original query – change the where clause to the following and check the new execution plan:


where
	t1.col1 = 99
and	t2.col2 = t1.col1
and	t3.col3 = t2.col2
;     

Execution Plan
----------------------------------------------------------
Plan hash value: 1573120526
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  8000 |  4757K|    24 |
|*  1 |  HASH JOIN          |      |  8000 |  4757K|    24 |
|*  2 |   TABLE ACCESS FULL | T3   |    20 |  4060 |     7 |
|*  3 |   HASH JOIN         |      |   400 |   158K|    16 |
|*  4 |    TABLE ACCESS FULL| T1   |    20 |  4060 |     7 |
|*  5 |    TABLE ACCESS FULL| T2   |    20 |  4060 |     7 |
------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T3"."COL3"="T2"."COL2")
   2 - filter("T3"."COL3"=99)
   3 - access("T2"."COL2"="T1"."COL1")
   4 - filter("T1"."COL1"=99)
   5 - filter("T2"."COL2"=99)

The plan (and its plan_hash_value) hasn’t changed of course, though if we had had some indexes in place we might have seen them coming into play. But notice how adding the one predicate “col1 = 99″ has allowed Oracle to generate two more predicates – for col2 and col3 respectively. Note also how the estimated number of rows has dropped from 800K to 8,000.

Let’s try a second test: since the optimizer did not generate the predicate “t1.col1 = t3.col3″ let’s see what happens if we add it by hand.

where
	t2.col2 = t1.col1
and	t3.col3 = t2.col2
and	t1.col1 = t3.col3
;   

Execution Plan
----------------------------------------------------------
Plan hash value: 1573120526
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  8000 |  4757K|   144 |
|*  1 |  HASH JOIN          |      |  8000 |  4757K|   144 |
|   2 |   TABLE ACCESS FULL | T3   |  2000 |   396K|     7 |
|*  3 |   HASH JOIN         |      | 40000 |    15M|    25 |
|   4 |    TABLE ACCESS FULL| T1   |  2000 |   396K|     7 |
|   5 |    TABLE ACCESS FULL| T2   |  2000 |   396K|     7 |
------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T3"."COL3"="T2"."COL2" AND "T1"."COL1"="T3"."COL3")
   3 - access("T2"."COL2"="T1"."COL1")

The plan still hasn’t changed of course; but look what our extra predicate has done to the estimated number of rows: once again it has dropped from 800K to 8,000. Our extra predicate has introduced an extra selectivity factor of 1/100 (there are 100 distinct values for t1.col1 and t3.col3) to the formula.

You might see this as a potential advantage, of course. If you can exert some influence over the optimizer’s arithmetic by adding redundant (but logically correct) predicates, it gives you a tool to deal with some of the problems that you might meet while trouble-shooting awkward SQL statements.

However, this approach does introduce a risk: the current behaviour is a mistake – the predicate is redundant, and should not affect the selectivity. If you take advantage of this design error in the optimizer, one day you will have to pay the price when Oracle finally corrects the error and the arithmetic changes to behave as if the predicate did not exist. So if you do fix a problem by adding redundant predicates, make sure you document what you did and why, so that it’s easier for the next person to fix when it all goes wrong again.

23 Comments »

  1. That’s an excellent point, thank you – I’ve often included redundant predicates in order to allow the optimiser flexibility in choosing access paths, as well as just to let the sql be “self documenting”.

    Comment by Jeff Kemp — January 1, 2007 @ 10:19 pm BST Jan 1,2007 | Reply

  2. @Jonathan,

    Wouldn’t the appropriate fix to this issue be to have the optimizer transitively close joins as it currently does with literals?

    If the optimizer can spontaneously add b.id = k to a where clause containing A.id = b.id and a.id = k, why shouldn’t to optimizer add a.id = c.id to a where clause containing a.id = b.id and c.id = b.id? So my inclusion of this logically superfluous but optimally useful join would be truly unneeded but not in error, no?

    Comment by Mark Brady — January 31, 2007 @ 5:18 pm BST Jan 31,2007 | Reply

  3. Mark, clearly – as it would resolve a number of odd inconsistencies. But I guess it would probably require a major rewrite to the code that works out join cardinality as part of the implementation.

    Comment by Jonathan Lewis — January 31, 2007 @ 7:50 pm BST Jan 31,2007 | Reply

  4. JL, it is as simple as this, Oracle applies the transitivity only to the filter predicates and not the join predicates.

    Let us take this example

    where
    t2.col2 = t1.col1
    and t3.col3 = t2.col2

    Now in simple maths we can infer that t3.col3=t1.col1, but not in Oracle. Because this is a join predicate and functionally the optimizer has no idea if it can apply the transitivity here.

    Where the case

    where
    t2.col2 = t1.col1
    and t3.col3 = t2.col2
    and t2.col2 =99;

    this is a filter predicate and hence the optimizer can apply the rule here.

    So I dont think this is a mistake under any circumstances.

    So it is both “technical” and “functional”.

    Comment by Avanish — February 6, 2007 @ 3:54 am BST Feb 6,2007 | Reply

  5. Avanish, that argument sounds flawed to me, for two reasons. First, if I start with

    t2.colA = t1.colA
    and t3.colA = t2.colA

    I can choose to write this as

    t2.colA = t1.colA
    and t3.colA = t1.colA

    It is still logically the same query, even though I’ve just “applied transitive closure to pure join predicates”.

    Secondly, when Oracle optimizes the query and assuming the join order tested is t1 -> t2 -> t3, then the join predicate t2.colA = t2.colA becomes the access/filter predicate t2.colA = {unknown constant} for the purposes of calculation – at which point we are open for transitive closure to t3 while still obeying your restriction.

    Comment by Jonathan Lewis — February 6, 2007 @ 7:21 pm BST Feb 6,2007 | Reply

  6. Jonathan,
    Can you please explain me “applied transitive closure to pure join predicates” in the example
    given
    t2.colA = t1.colA
    and t3.colA = t2.colA

    Comment by karthik — May 10, 2007 @ 4:08 am BST May 10,2007 | Reply

  7. Karthik: transitive closure:
    if   a = b and b = c
    then a = c

    In this case, 
         t3.colA = t2.colA and t2.colA = t1.colA;
    so   t3.colA = t1.colA

    Hence I can change the original where clause to
         t3.colA = t1.colA and t2.colA = t1.colA;
    without changing the logic of the statement.

    Comment by Jonathan Lewis — May 10, 2007 @ 3:31 pm BST May 10,2007 | Reply

  8. Would you mind to have a look at this?

    I ‘ve examined both 10046 and 10053 trace files for two simple test cases.

    The 1st test case creates a table with a column that MUST be NULL (enforced
    by a check constraint) and gets queried with a predicate “… is NULL” whereas
    the 2nd test case does the “opposite” (column MUST NOT be NULL and is queried
    with a predicate “… is NULL”). There is just one row inserted in either case.
    The buffer cache has been flushed before each test run.

    As for check constraint “is NULL” (predicate “… is NOT NULL”) the trace files
    report the following:

    select NULL
    from
    MyTab where MyCol is not NULL

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 1 0.00 0.01 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 1 0.00 0.00 0 7 0 0
    ——- —— ——– ———- ———- ———- ———- ———-
    total 3 0.00 0.01 0 7 0 0

    FPD: Current where clause predicates in SEL$1 (#0) :
    “MYTAB”.”MYCOL” IS NOT NULL
    kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#0)
    constraint: “MYTAB”.”MYCOL” IS NULL
    predicates with check contraints: “MYTAB”.”MYCOL” IS NOT NULL AND “MYTAB”.”MYCOL” IS NULL
    after transitive predicate generation: “MYTAB”.”MYCOL” IS NOT NULL AND
    “MYTAB”.”MYCOL” IS NULL
    finally: “MYTAB”.”MYCOL” IS NOT NULL

    It seems as if an additional predicate is generated but not applied in the end which leads
    to some “unnecessary work” (proven by “query” blocks?).

    As opposed to that for check constraint “is NOT NULL” (predicate “… is NULL”) the trace
    files report:

    select NULL
    from
    MyTab where MyCol is NULL

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 1 0.00 0.00 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 1 0.00 0.00 0 0 0 0
    ——- —— ——– ———- ———- ———- ———- ———-
    total 3 0.00 0.00 0 0 0 0

    FPD: Current where clause predicates in SEL$1 (#0) :
    “MYTAB”.”MYCOL” IS NULL
    kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#0)
    predicates with check contraints: “MYTAB”.”MYCOL” IS NULL
    after transitive predicate generation: “MYTAB”.”MYCOL” IS NULL
    finally: “MYTAB”.”MYCOL” IS NULL

    It seems as if there is no additional predicate generated at all but “unnecessary work”
    gets avoided nevertheless (no “query” blocks reported).

    Neither 1st nor 2nd query would require any access to table data to generate a result —
    but figures seem to be inconsistent somehow …

    Or are these ovservations/assumptions just wrong?

    Comment by Jens — August 21, 2007 @ 5:01 am BST Aug 21,2007 | Reply

  9. Jens, no version number, no simple script, and not following the guidelines for posting comments to make code readable. You’re not trying to make this easy, are you ?

    I tried to repeat your test on 10.2.0.3 without success, until I changed my not null constraint to a column declaration.

    drop table t1;

    create table t1 (
            nulled       varchar2(10) check (nulled is null),
            mandatory varchar2(10) check (mandatory is not null)
    );

    execute dbms_stats.gather_table_stats(user,’t1′);

    rem
    rem     Run the next section twice, and check
    rem     the statistics on the second run.
    rem
    rem     Then repeat after doing:
    rem     alter table t1  modify (mandatory not null);
    rem

    set autotrace traceonly 

    select  null 
    from    t1
    where   nulled is not null
    ;

    select  null 
    from    t1
    where   mandatory is null
    ;

    set autotrace off

    Using the constraint syntax, the execution plan was a simple full tablescan in both cases. With the not null declaration the (slightly trimmed) execution plan in the second case changed to:

    ———————————–
    | Id  | Operation          | Name |
    ———————————–
    |   0 | SELECT STATEMENT   |      |
    |*  1 |  FILTER            |      |
    |   2 |   TABLE ACCESS FULL| T1   |
    ———————————–

    Predicate Information 
    ———————
       1 – filter(NULL IS NOT NULL)

    Don’t ask why – I don’t know the rationale. There are various changes to the optimizer’s options for handling nulls in 10g, so this might be a special case, or a bit of code that is not quite complete; who can say?

    Comment by Jonathan Lewis — August 27, 2007 @ 8:47 pm BST Aug 27,2007 | Reply

  10. Jens,
    Thank you for sending me the following confirmation from an SR, and giving me permission to publish parts of it. In particular:

    As of current state of the optimizer the constraints are not used to filter if they include NULL in their body. All other constraints are used to filter.

    The only exception is the NOT NULL specification at the time of table creation because it is recorded in the data dictionary as a column attribute in addtion to the constraint. This column attribute is the one that shows when doing a describe on the table and that attribute is included by other section of the optimier code and not on the constraint section of the code.

    Development’s opinion is that the NULL-bodied constraints can probably be included but this would be an enhancement request.

    In other words, our observations were correct, there is a rationale for them, and any change would be an enhancement, not a bug fix.

    Comment by Jonathan Lewis — September 2, 2007 @ 3:27 pm BST Sep 2,2007 | Reply

  11. Hello Jonathan,

    I was trying to test Index Fast Full Scan and Skip Scan. I was just using default HR schema in 10.2.0.1 I see following in 10053 trace file.
    ========================================
    **************************
    Predicate Move-Around (PM)
    **************************
    PM: Considering predicate move-around in SEL$1 (#0).
    PM: Checking validity of predicate move-around in SEL$1 (#0).
    PM: PM bypassed: Outer query contains no views.
    FPD: Considering simple filter push in SEL$1 (#0)
    FPD: Current where clause predicates in SEL$1 (#0) :
    “EMPLOYEES”.”LAST_NAME”=’Fay’
    kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#0)
    constraint: “EMPLOYEES”.”SALARY”>0
    predicates with check contraints: “EMPLOYEES”.”LAST_NAME”=’Fay’ AND “EMPLOYEES”.”SALARY”>0
    after transitive predicate generation: “EMPLOYEES”.”LAST_NAME”=’Fay’ AND “EMPLOYEES”.”SALARY”>0
    finally: “EMPLOYEES”.”LAST_NAME”=’Fay’
    apadrv-start: call(in-use=224, alloc=0), compile(in-use=33084, alloc=0)
    kkoqbc-start
    : call(in-use=224, alloc=0), compile(in-use=33688, alloc=0)
    =================================

    I query is as follow:

    select first_name, last_name from employees where last_name = ‘Fay’ ;

    My question is why Optimizer is trying to do Transitive Predicate ?

    Also, why There is a two Predicate Move Section in 10053 ?

    *** TRACE DUMP CONTINUED FROM FILE c:\oracle\product\10.2.0\admin\ora10201\udump\ora10201_ora_1272.trc ***

    Registered qb: SEL$1 0x4cda5e0 (PARSER)
    signature (): qb_name=SEL$1 nbfros=1 flg=0
    fro(0): flg=4 objn=51910 hint_alias=”EMPLOYEES”@”SEL$1″
    **************************
    Predicate Move-Around (PM)
    **************************
    PM: Considering predicate move-around in SEL$1 (#0).
    PM: Checking validity of predicate move-around in SEL$1 (#0).
    CBQT: Validity checks failed for gdwcnatdy97q3.
    CVM: Considering view merge in query block SEL$1 (#0)
    CBQT: Validity checks failed for gdwcnatdy97q3.
    ***************
    Subquery Unnest
    ***************
    SU: Considering subquery unnesting in query block SEL$1 (#0)
    *************************
    Set-Join Conversion (SJC)
    *************************
    SJC: Considering set-join conversion in SEL$1 (#0).
    **************************
    Predicate Move-Around (PM)
    **************************
    PM: Considering predicate move-around in SEL$1 (#0).
    PM: Checking validity of predicate move-around in SEL$1 (#0).
    PM: PM bypassed: Outer query contains no views.
    FPD: Considering simple filter push in SEL$1 (#0)
    FPD: Current where clause predicates in SEL$1 (#0) :
    “EMPLOYEES”.”LAST_NAME”=’Fay’
    kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#0)
    constraint: “EMPLOYEES”.”SALARY”>0
    predicates with check contraints: “EMPLOYEES”.”LAST_NAME”=’Fay’ AND “EMPLOYEES”.”SALARY”>0
    after transitive predicate generation: “EMPLOYEES”.”LAST_NAME”=’Fay’ AND “EMPLOYEES”.”SALARY”>0
    finally: “EMPLOYEES”.”LAST_NAME”=’Fay’
    apadrv-start: call(in-use=224, alloc=0), compile(in-use=33468, alloc=0)
    kkoqbc-start
    : call(in-use=224, alloc=0), compile(in-use=34072, alloc=0)
    ******************************************
    Current SQL statement for this session:
    select first_name, last_name from employees where last_name = ‘Fay’
    *******************************************

    Thanks

    ~Keyur

    Comment by Keyur — December 9, 2007 @ 5:17 am BST Dec 9,2007 | Reply

  12. My question is why Optimizer is trying to do Transitive Predicate ?
    Also, why There is a two Predicate Move Section in 10053 ?

    The obvious answer is “I don’t know, I didn’t write the code”.

    If you want a guess – notice that there is a section on subquery unnesting and set/join conversion before the second predicate move around. It is possible that a successful transformation could produce more opportunities for predicate moves – so it has to be tried again.

    If the guess is correct you might have expected a flag to be set so that the optimizer could see whether or not any transformations had taken place and avoid the second pass, of course.

    Comment by Jonathan Lewis — December 10, 2007 @ 10:40 am BST Dec 10,2007 | Reply

  13. Which flag is enabling/disabling that feature ?

    Thanks

    ~Keyur

    Comment by Keyur — December 10, 2007 @ 7:05 pm BST Dec 10,2007 | Reply

  14. I tried to find out if something is changing by setting false for following parameter but It didn’t change anything.
    PARAMETERS WITH ALTERED VALUES
    ******************************
    _optimizer_transitivity_retain = false
    _push_join_predicate = false

    I thought this would avoid any push join, but still it’s doing push join.

    Because It’s trying possiblities in QUERY TRANFORMATION phase and then rejecting predicates. I am thinking to disable query transformation, but trying to find out how to do that.

    Thanks

    ~ Keyur

    Comment by Keyur — December 11, 2007 @ 10:49 pm BST Dec 11,2007 | Reply

  15. Keyur, if you want to disable query transformation, you can do it at the statement level with the hint /*+ no_query_transformation */.

    If you have a general problem with an entire class of queries that behaved in version “X” but not in version “Y” then you could try running the 10053 against one of them after setting “optimizer_features_enable” to each of the two values in turn. You can then compare the section about optimizer environment values to see if you can spot the most likely parameter that is causing the change.

    _optimizer_transitivity_retain = false
    This handles the case of
    if a = b and b = c then a = c.
    Earlier versions of Oracle would take:
    where a = b and b = c
    and change it to something like
    where a = b and a = c
    discarding the b=c
    in newer versions of Oracle the new predicate is generated and the old predicate is retained – giving 3 predicates – if this parameter is set to true.

    _push_join_predicate = false
    This parameter is not about general predicat move around, it relates to Oracle ability to push join predicates into non-mergeable views; potentially allowing a large hash join to become a nested loop approach.

    Comment by Jonathan Lewis — December 12, 2007 @ 10:37 am BST Dec 12,2007 | Reply

  16. [...] Oracle always tries to transform your query before optimizing it. One of the transformation steps involves rewriting your predicates. This step is allowed to introduce new predicates from your table constraints, and may also rearrange your predicates through transitive closure. [...]

    Pingback by Predicate Problems « Oracle Scratchpad — December 3, 2008 @ 8:50 pm BST Dec 3,2008 | Reply

  17. [...] the “missing” predicate is the join predicate. You’re looking at an example of transitive closure:  if t1.id1 = 1 and t2.id1 = t1.id1 then t2.id1 = 1 So Oracle has taken the predicate from one [...]

    Pingback by Ignoring Hints – 2 « Oracle Scratchpad — February 11, 2010 @ 7:29 pm BST Feb 11,2010 | Reply

  18. [...] the appearance of an extra predicate on fgc.tcp_mpf_file_group_id. This has appeared through transitive closure. (You might also notice that the join condition between tmfg and fgc has disappeared, and in some [...]

    Pingback by Predicate (again) « Oracle Scratchpad — April 15, 2010 @ 6:18 pm BST Apr 15,2010 | Reply

  19. I agree that Transitive closure may provide good cardinality estimates, but many times i observed that execution pattern will not change. I tried many queries on EMP and DEPT table (Where EMP.DEPT_NO=DEPT.DEPT_NO AND DEPT.DEPT_NO=1) after addition redundant predicate (Where EMP.DEPT_NO=DEPT.DEPT_NO AND DEPT.DEPT_NO=1 AND EMP.DEPT_NO=1). EMP is having 5M rows with EMP_NO as Primary key and DEPT is having 1000 Rows with DEPT_NO primary key)

    Comment by Bhavik Desai — June 30, 2010 @ 12:55 pm BST Jun 30,2010 | Reply

  20. A question

    “Transitive closure is relevant only if there are some constants in the mixture” , you say

    Does this mean that it can’t be an issue if cursor_sharing = FORCE or SIMILAR ?

    Comment by Andy — March 2, 2012 @ 11:26 am BST Mar 2,2012 | Reply

    • Andy,
      Just one of many late replies, but the answer is that transitive closure can still be applied. The word “constant” covers a multitude of sins, include “constants which are on an unknown value at present”. Here’s a little demo, cut and paste, from 11.1.0.7

      
      SQL> explain plan for
        2  select t1.n2 , t2.n2
        3  from t1, t2
        4  where t1.n1 = :b1
        5  and t2.n1 = t1.n1
        6  /
      
      Explained.
      
      SQL> select * from table(dbms_xplan.display);
      
      PLAN_TABLE_OUTPUT
      -----------------------------------------------------------------------
      Plan hash value: 1167318051
      
      ----------------------------------------------------------------------
      | Id  | Operation                    | Name  | Rows  | Bytes | Cost  |
      ----------------------------------------------------------------------
      |   0 | SELECT STATEMENT             |       |   225 |  3600 |    19 |
      |*  1 |  HASH JOIN                   |       |   225 |  3600 |    19 |
      |   2 |   TABLE ACCESS BY INDEX ROWID| T1    |    15 |   120 |     2 |
      |*  3 |    INDEX RANGE SCAN          | T1_I1 |    15 |       |     1 |
      |*  4 |   TABLE ACCESS FULL          | T2    |    15 |   120 |    15 |
      ----------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         1 - access("T2"."N1"="T1"."N1")
         3 - access("T1"."N1"=TO_NUMBER(:B1))
         4 - filter("T2"."N1"=TO_NUMBER(:B1))
      
      Note
      -----
         - cpu costing is off (consider enabling it)
      
      22 rows selected.
      
      

      As you can see, the predicate has ended up being applied to both tables.
      In fact, at least in this version of Oracle – I don’t recall if it is the same for older versions, you can even replace the bind variable with a call to sys_content() and see transitive closure; the same would apply with a call to a deterministic function that returns a constant.

      Comment by Jonathan Lewis — December 13, 2012 @ 10:20 am BST Dec 13,2012 | Reply

  21. Good evening,

    FYI: in 11.2.0.3, the transitive closure effect of removing the join predicate and replacing it with filters for each table can be prevented with

    “_optimizer_filter_pushdown” = false

    So instead of:

    FPD: Considering simple filter push in query block SEL$1 (#0)
    “O”.”CUSTOMER_ID”=”C”.”CUSTOMER_ID” AND “C”.”CUSTOMER_ID”=:B1
    try to generate transitive predicate from check constraints for query block SEL$1 (#0)
    finally: “O”.”CUSTOMER_ID”=:B1 AND “C”.”CUSTOMER_ID”=:B2

    it is disabled:
    FPD: Disabled by parameter

    Best regards,
    Martin

    Comment by Martin Decker — October 18, 2012 @ 7:45 pm BST Oct 18,2012 | Reply

    • Martin,

      Thanks for the comment. In “Cost Based Oracle – Fundamentals” I mentioned (without naming it) that 10g hadintroduced a new parameter to ensure that the join predicate was retained ( _optimizer_transitivity_retain, with description “retain equi-join pred upon transitive equality pred generation” ). This one goes that little bit further. It would be useful to see how many other possible transformations could be blocked by this parameter change.

      Comment by Jonathan Lewis — December 13, 2012 @ 10:28 am BST Dec 13,2012 | 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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,268 other followers