We already know that the CBO transformation engine in 12c can unnest scalar subqueries from select-list.
So it’s not very surprising, that CBO is now able to add scalar subqueries costs to total query cost (even if “_optimizer_unnest_scalar_sq” = false):
Before 12.1
SQL> explain plan for 2 select 3 (select count(*) from XT_TEST) cnt 4 from dual; Explained. PLAN_TABLE_OUTPUT --------------------------------------------------------------------------- Plan hash value: 2843533371 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| IX_TEST_A | 90792 | 50 (0)| 00:00:01 | | 3 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------- 10 rows selected.
[collapse]
12.1
SQL> alter session set "_optimizer_unnest_scalar_sq"=false; Session altered. SQL> explain plan for 2 select 3 (select count(*) from XT_TEST) cnt 4 from dual; Explained. PLAN_TABLE_OUTPUT --------------------------------------------------------------------------- Plan hash value: 2843533371 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 52 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| IX_TEST_A | 90792 | 50 (0)| 00:00:01 | | 3 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------- 10 rows selected.
[collapse]
But it’s interesting that correlated subquery can reference now to a column from parent tables more
than one level above:
Before 12.1
SQL> with t1 as (select/*+ materialize */ 1 a from dual)
2 ,t2 as (select/*+ materialize */ 2 b from dual)
3 ,t3 as (select/*+ materialize */ 3 c from dual)
4 select
5 (select s from (select sum(b*c) s from t2,t3 where c>t1.a and c>b)) s
6 from t1;
(select s from (select sum(b*c) s from t2,t3 where c>t1.a and c>b)) s
*
ERROR at line 5:
ORA-00904: "T1"."A": invalid identifier
[collapse]
12.1
SQL> with t1 as (select/*+ materialize */ 1 a from dual)
2 ,t2 as (select/*+ materialize */ 2 b from dual)
3 ,t3 as (select/*+ materialize */ 3 c from dual)
4 select
5 (select s from (select sum(b*c) s from t2,t3 where c>t1.a and c>b)) s
6 from t1;
S
----------
6
[collapse]