Jonathan Lewis has recently posted a good example of CBO not good enough in transforming specific query types. A recent thread on the SQL.ru Oracle forum reminded me of this issue.
Basically OP has a query with disjuncted (OR-ed) predicate which started to fail after 11.2.0.3 upgrade with ORA-01790: expression must have same datatype as corresponding expression. Here is a test case (I’ve renamed column and table names cause I’ve used to such naming):
drop table t1 cascade constraints purge; drop table t2 cascade constraints purge; create table t1 ( x int not null, y varchar2(13) ); insert into t1 values(0,100); insert into t1 values(0,100); insert into t1 values(0,100); create table t2 ( x number, y number(15) ); insert into t2 values(1,100); insert into t2 values(2,100); insert into t2 values(1,100); insert into t2 values(1,100); commit; exec dbms_stats.gather_table_stats(user, 't1', no_invalidate=>false) exec dbms_stats.gather_table_stats(user, 't2', no_invalidate=>false)
Now let’s try to run following query:
SQL> select * 2 from t2 3 where not exists 4 (select 1 5 from t1 6 where (t1.y = t2.y) 7 or (t1.x = t2.y)); or (t1.x = t2.y)) * ERROR at line 7: ORA-01790: expression must have same datatype as corresponding expression
Oops. But if you change second condition with swapping left and right parts it is executed OK:
SQL> ed Wrote file afiedt.buf 1 select * 2 from t2 3 where not exists 4 (select 1 5 from t1 6 where (t1.y = t2.y) 7* or (t2.y = t1.x)) SQL> / no rows selected
And the plan is FILTER-based
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 | 7 (0)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL| T2 | 4 | 24 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| T1 | 1 | 6 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( NOT EXISTS (SELECT 0 FROM "T1" "T1" WHERE "T1"."X"=:B1 OR TO_NUMBER("T1"."Y")=:B2)) 3 - filter("T1"."X"=:B1 OR TO_NUMBER("T1"."Y")=:B2)
So why first query error-ed? Let’s look at the critical lines of 10053 trace file (which is incomplete due to error):
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest. Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing. SU: Considering subquery unnest on query block SEL$1 (#1). SU: Checking validity of unnesting subquery SEL$2 (#2) SU: Passed validity checks. Branch query block:******* UNPARSED QUERY IS ******* SELECT 1 "1" FROM "TIM"."T1" "T1" WHERE "T1"."Y"="T2"."Y" OR "T1"."X"="T2"."Y" Branch query block:******* UNPARSED QUERY IS ******* SELECT 1 "1" FROM "TIM"."T1" "T1" WHERE "T1"."Y"="T2"."Y" OR "T1"."X"="T2"."Y" Set query block:******* UNPARSED QUERY IS ******* (SELECT 1 "1" FROM "TIM"."T1" "T1" WHERE "T1"."Y"="T2"."Y") UNION ALL (SELECT 1 "1" FROM "TIM"."T1" "T1" WHERE "T1"."X"="T2"."Y") Registered qb: SET$E74BECDC 0xd0e9a0c8 (COMPLEX SUBQUERY UNNEST SEL$2) --------------------- QUERY BLOCK SIGNATURE --------------------- signature (): qb_name=SET$E74BECDC nbfros=1 flg=0 fro(0): flg=0 objn=0 hint_alias="NULL_HALIAS"@"SET$E74BECDC" Query block after disjuncts converted to set:******* UNPARSED QUERY IS ******* SELECT "T2"."X" "X","T2"."Y" "Y" FROM "TIM"."T2" "T2" WHERE NOT EXISTS ( (SELECT 1 "1" FROM "TIM"."T1" "T1" WHERE "T1"."Y"="T2"."Y") UNION ALL (SELECT 1 "1" FROM "TIM"."T1" "T1" WHERE "T1"."X"="T2"."Y")) SU: Unnesting subquery query block SET$E74BECDC (#0)Registered qb: SET$7FD77EFD 0xd0e9a0c8 (SUBQ INTO VIEW FOR COMPLEX UNNEST SET$E74BECDC) ... SELECT "T2"."X" "X","T2"."Y" "Y" FROM ( (SELECT "T1"."Y" "ITEM_1" FROM "TIM"."T1" "T1") UNION ALL (SELECT "T1"."X" "ITEM_2" FROM "TIM"."T1" "T1")) "VW_SQ_1","TIM"."T2" "T2" WHERE "VW_SQ_1"."VW_COL_1"="T2"."Y"
Basically Oracle tries to transform the query to replace disjunct predicate with a set (UNION ALL) of two subqueries, which allows it to unnest the resulting subquery and then join it. It’s not merged though due to UNION ALL presence.
And since for reasons unknown to me Oracle throws an exception for a query block:
select 'x' from dual union all select 2 from dual
original query fails. It can be avoided with an explicit type conversion which will still allow unnesting to happen:
SQL> explain plan for 2 select * 3 from t2 4 where not exists 5 (select 1 6 from t1 7 where (to_number(t1.y) = t2.y) 8 or (t1.x = t2.y)); Explained. SQL> @xp PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 1288684780 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 19 | 10 (10)| 00:00:01 | |* 1 | HASH JOIN ANTI | | 1 | 19 | 10 (10)| 00:00:01 | | 2 | TABLE ACCESS FULL | T2 | 4 | 24 | 3 (0)| 00:00:01 | | 3 | VIEW | VW_SQ_1 | 6 | 78 | 6 (0)| 00:00:01 | | 4 | UNION-ALL | | | | | | | 5 | TABLE ACCESS FULL| T1 | 3 | 12 | 3 (0)| 00:00:01 | | 6 | TABLE ACCESS FULL| T1 | 3 | 6 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("VW_COL_1"="T2"."Y")
But if you don’t need unnesting, then by using hidden _optimizer_unnest_disjunctive_subq parameter you can turn off the transformation (for me it does so, but be careful with the hidden stuff):
explain plan for select /*+ opt_param('_optimizer_unnest_disjunctive_subq', 'false') */ * from t2 where not exists (select 1 from t1 where (t1.y = t2.y) or (t1.x = t2.y)); @xp --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 | 7 (0)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL| T2 | 4 | 24 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| T1 | 1 | 6 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( NOT EXISTS (SELECT 0 FROM "T1" "T1" WHERE "T1"."X"=:B1 OR TO_NUMBER("T1"."Y")=:B2)) 3 - filter("T1"."X"=:B1 OR TO_NUMBER("T1"."Y")=:B2)
And in Jonathan’s case it’s also possible to invoke such optimization. All that is needed is a simple modification of a predicate to move 1000 to the left, which will allow Oracle to hash join the resulting view without a problem:
SQL> explain plan for 2 select 3 * 4 from 5 t2 6 where 7 not exists ( 8 select /*+ unnest */ 9 null 10 from t3 11 where n3a = n2a 12 and n3b = n2b 13 and (id3 = id2 or id3 - 1000 = id2) 14 ) 15 ; Explained. SQL> @xp PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------- Plan hash value: 3482347739 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5000 | 776K| 33 (4)| 00:00:01 | |* 1 | HASH JOIN RIGHT ANTI | | 5000 | 776K| 33 (4)| 00:00:01 | | 2 | VIEW | VW_SQ_1 | 10000 | 380K| 10 (0)| 00:00:01 | | 3 | UNION-ALL | | | | | | | 4 | INDEX FAST FULL SCAN| T3_PK | 5000 | 60000 | 5 (0)| 00:00:01 | | 5 | INDEX FAST FULL SCAN| T3_PK | 5000 | 60000 | 5 (0)| 00:00:01 | | 6 | TABLE ACCESS FULL | T2 | 5000 | 585K| 22 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("VW_COL_1"="ID2" AND "VW_COL_2"="N2A" AND "VW_COL_3"="N2B")
What’s in total:
Recent comments
1 year 45 weeks ago
2 years 5 weeks ago
2 years 9 weeks ago
2 years 10 weeks ago
2 years 15 weeks ago
2 years 36 weeks ago
3 years 4 weeks ago
3 years 34 weeks ago
4 years 18 weeks ago
4 years 19 weeks ago