Here is a nice example of what Oracle 11.2.0.2 is able to do with a subquery inside an expression. It can unnest it – that is a new 11.2.0.2 feature of the transformation part of the CBO.
Let’s see what’s going on in the 10053 trace of that query:
drop table tests cascade constraints purge; create table tests (id number); insert into tests values(1); insert into tests values(2); commit; exec dbms_stats.gather_table_stats(user, 'tests'); @53 SELECT /*+ qb_name(qb1) */ * FROM tests a WHERE id = NVL ( (SELECT /*+ qb_name(qb2) */ MAX (b.id) FROM tests b WHERE b.id > 2 AND a.id = b.id), 1); @53off
SU: Considering subquery unnesting in query block QB1 (#0) ******************** Subquery Unnest (SU) ******************** SU: Checking validity of unnesting subquery QB2 (#0) SU: Heuristic checks passed. SU: Unnesting subquery query block QB2 (#0)SU: Heuristic checks passed. Subquery removal for query block QB2 (#0) RSW: Not valid for subquery removal QB2 (#0) Subquery unchanged. Registered qb: SEL$135008A8 0xa6aee900 (SUBQ INTO VIEW FOR COMPLEX UNNEST QB2) --------------------- QUERY BLOCK SIGNATURE --------------------- signature (): qb_name=SEL$135008A8 nbfros=1 flg=0 fro(0): flg=0 objn=20318 hint_alias="B"@"QB2" Registered qb: SEL$10521F9C 0xa6b368e8 (VIEW ADDED QB1) --------------------- QUERY BLOCK SIGNATURE --------------------- signature (): qb_name=SEL$10521F9C nbfros=2 flg=0 fro(0): flg=0 objn=20318 hint_alias="A"@"QB1" fro(1): flg=5 objn=0 hint_alias="VW_SQ_1"@"SEL$10521F9C" Registered qb: SEL$A3D198B0 0xa6b368e8 (SUBQUERY UNNEST SEL$10521F9C; QB2) ... Final query after transformations:******* UNPARSED QUERY IS ******* SELECT /*+ QB_NAME ("QB1") */ "A"."ID" "ID" FROM (SELECT /*+ QB_NAME ("QB2") */ MAX("B"."ID") "MAX(B.ID)","B"."ID" "ITEM_0" FROM "TIM"."TESTS" "B" WHERE "B"."ID">2 GROUP BY "B"."ID") "VW_SQ_1","TIM"."TESTS" "A" WHERE "A"."ID"=NVL("VW_SQ_1"."MAX(B.ID)",1) AND "A"."ID"="VW_SQ_1"."ITEM_0" kkoqbc: optimizing query block SEL$135008A8 (#0)
So the transformed query looks like this:
select /*+ qb_name (qb1) */ a.id id from (select /*+ qb_name (qb2) */ max(b.id) "max(b.id)", b.id item_0 from tests b where b.id > 2 group by b.id) vw_sq_1 , tests a where a.id = nvl(vw_sq_1."max(b.id)", 1) and a.id = vw_sq_1.item_0;
and is not equivalent to the original query. This is definitely a bug associated with the new 11.2.0.2 feature
SQL> @bug unnest Opt BUGNO VALUE SQL_FEATURE DESCRIPTION features ---------- ----- ---------------------------------------- ---------------------------------------------------------------- ---------- 3834770 1 QKSFM_TRANSFORMATION_3834770 Lift restriction on unnest subquery with a view 8.0.0 4872602 0 QKSFM_TRANSFORMATION_4872602 Disable unnesting of SQ under certain conditions 6138746 1 QKSFM_ACCESS_PATH_6138746 Consider only simple column preds in subquery unnest heuristic 10.2.0.5 6438752 1 QKSFM_CBQT_6438752 do not store cost annotations for branch QBs in unnested subquer 11.1.0.7 6681545 1 QKSFM_PARTITION_6681545 Enable unnesting of correlated subquery containing tbl$ predicat 11.1.0.7 7032684 1 QKSFM_TRANSFORMATION_7032684 Allow non-well-formed correlated predicates in unnesting 11.2.0.1 6669103 1 QKSFM_TRANSFORMATION_6669103 an operand of OPTTNN is null-safe for query unnesting 10.2.0.5 7215982 1 QKSFM_UNNEST_7215982 unnest subquery embedded inside an expression 11.2.0.2 8214022 1 QKSFM_UNNEST_8214022 perform additional CBQT phase for subquery unnesting 11.2.0.2 9143856 1 QKSFM_TRANSFORMATION_9143856 uncorrelated OR-ed unary predicates are OK for unnesting 11.2.0.2
If it’s disabled, then everything works fine:
SQL> SELECT /*+ qb_name(qb1) opt_param('_fix_control' '7215982:off') */ * 2 FROM tests a 3 WHERE id = NVL ( (SELECT /*+ qb_name(qb2) */ MAX (b.id) 4 FROM tests b 5 WHERE b.id > 2 AND a.id = b.id), 6 1); ID -------------------- 1
I’ve seen this “feature” in a little bit different situation, where I’ve got a strange plan with a subquery unnested from an expression resulted in a full table scan of a huge table. I was unable to reproduce it with a simple test case. Here you can see a different (wrong results) issue, but with the same cause – too smart optimizer
Filed under: CBO, Oracle Tagged: 11.2.0.2, CBQT, subquery unnesting
Recent comments
2 years 51 weeks ago
3 years 11 weeks ago
3 years 15 weeks ago
3 years 16 weeks ago
3 years 21 weeks ago
3 years 42 weeks ago
4 years 10 weeks ago
4 years 40 weeks ago
5 years 24 weeks ago
5 years 24 weeks ago