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):
[sourcecode language=”sql” highlight=”15,17″]
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.
[/sourcecode]
[sourcecode language=”sql” highlight=”19,21″]
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.
[/sourcecode]
But it’s interesting that correlated subquery can reference now to a column from parent tables more
than one level above:
[sourcecode language=”sql” highlight=”10,11″]
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
[/sourcecode]
[sourcecode language=”sql”]
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
[/sourcecode]