Bug 13984324 wrong result with UNION ALL of similar queries / in DBA_TAB_MODIFICATIONS

Problemes : SQL> select  *  from  dba_tab_modifications; no rows selected SQL> select  count(1)   from  dba_tab_modifications;   COUNT(1) ----------        618 Solution : alter session set "_optimizer_join_factorization"=false; select  *  from  dba_tab_modifications;  works  fine. Bug 13984324 - wrong result with UNION ALL of similar queries / in DBA_TAB_MODIFICATIONS [ID 13984324.8]
		Modified Tue Jul 17 12:40:56 CDT 2012     Type PATCH     Status PUBLISHED

Bug 13984324  wrong result with UNION ALL of similar queries / in DBA_TAB_MODIFICATIONS

 This note gives a brief overview of bug 13984324.
 The content was last updated on: 17-JUL-2012
 Click here for details of each of the sections below.

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions >= 11.2 but BELOW 12.1
Versions confirmed as being affected	11.2.0.3
Platforms affected	Generic (all / most platforms affected)

Fixed:

This issue is fixed in

11.2.0.4 (Future Patch Set)

Symptoms:	Related To:
Wrong Results	Optimizer dba_tab_modifications _optimizer_join_factorization Optimizer Feature JF

Description

Wrong result are possible with :
 
1) at least 3 branches in UNION ALL
2) at least 2 join factorization predicates
3) Predicate order in branches is different
4) Correct result with "_optimizer_join_factorization"=false;
 
select /*+ FACTORIZE_JOIN(@SET$1(M@SEL$1 M@SEL$2 M@SEL$3)
            (U@SEL$1 U@SEL$2 U@SEL$3)) */ 1 name
from t1_13984324 m, t3_13984324 o,t2_13984324 u
where o.col1 = m.col1 and o.col2 = u.col2 and o.type#=21
union all
select 2 name
from t1_13984324 m, t3_13984324 o,t2_13984324 u
where o.col2 = u.col2 and o.col1 = m.col1 and o.type#=19    <== different order of join predicates than the other 2.
union all
select 3 name
from t1_13984324 m, t3_13984324 o,t2_13984324 u
where o.col1 = m.col1 and o.col2 = u.col2 and o.type#=20;
 
This bug specially affects queries over DBA_TAB_MODIFICATIONS
 
Workaround
"_optimizer_join_factorization"=false
 

HOOKS cbo:JF parameter:_optimizer_join_factorization view:dba_tab_modifications LIKELYAFFECTS XAFFECTS_11.2.0.1 XAFFECTS_V11020001 AFFECTS=11.2.0.1 XAFFECTS_11.2.0.2 XAFFECTS_V11020002 AFFECTS=11.2.0.2 XAFFECTS_11.2.0.3 XAFFECTS_V11020003 AFFECTS=11.2.0.3 XPRODID_5 PRODUCT_ID=5 PRODID-5 RDBMS XCOMP_RDBMS COMPONENT=RDBMS TAG_CBO TAG_WRONGRES CBO WRONGRES FIXED_11.2.0.4

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.

References

Bug:13984324 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article