[20151217]12c标量子查询.txt
--我曾经写过blog，提到许多开发没有根据情况滥用子查询。
--而在12c下呢？
So starting with Oracle 12c, the CBO transformation engine can unnest some types of scalar subqueries and convert those
to Outer joins internally
--我喜欢通过例子来说明问题，看一些例子：
1.建立测试环境：
SCOTT@test01p> @ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SCOTT@test01p> create table dept2 as select * from dept;
Table created.
--建立dept2的目的，主要原始的dept上有索引，dept表太小，oracle趋向选择索引。
2.测试1：
SCOTT@test01p> alter session set statistics_level=all;
Session altered.
SCOTT@test01p> select emp.*,(select dname from dept2 where dept2.deptno=emp.deptno) dname from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- --------------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 RESEARCH
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 SALES
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 SALES
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 RESEARCH
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 SALES
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 SALES
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 ACCOUNTING
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 RESEARCH
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 ACCOUNTING
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 SALES
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 RESEARCH
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 SALES
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 RESEARCH
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 ACCOUNTING
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID afcj25qb7mbma, child number 1
-------------------------------------
select emp.*,(select dname from dept2 where dept2.deptno=emp.deptno)
dname from emp
Plan hash value: 1580873685
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 12 (100)| 14 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| DEPT2 | 3 | 1 | 3 (0)| 3 |00:00:00.01 | 6 |
| 2 | TABLE ACCESS FULL| EMP | 1 | 14 | 3 (0)| 14 |00:00:00.01 | 8 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPT2"."DEPTNO"=:B1)
--可以发现标量查询cost计算在内的，id=1的starts＝3，3+3*3=12。而buffers并没有计算在内。最终buffers=8仅仅计算了扫描emp的buffers。
SCOTT@test01p> alter session set optimizer_features_enable='11.2.0.3';
Session altered.
SCOTT@test01p> select emp.*,(select dname from dept2 where dept2.deptno=emp.deptno) dname from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- --------------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 RESEARCH
....
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID afcj25qb7mbma, child number 2
-------------------------------------
select emp.*,(select dname from dept2 where dept2.deptno=emp.deptno)
dname from emp
Plan hash value: 1580873685
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 (100)| 14 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| DEPT2 | 3 | 1 | 3 (0)| 3 |00:00:00.01 | 9 |
| 2 | TABLE ACCESS FULL| EMP | 1 | 14 | 3 (0)| 14 |00:00:00.01 | 8 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPT2"."DEPTNO"=:B1)
--咋一看上去是一样的，如果仔细看id=1,buffers=9(前面是6),总的cost＝3，没有计算了子查询的cost。
--会不会12c改进一些算法，缓存了查询的结果。
3.测试2：
--退出，取消一些参数的影响。
SCOTT@test01p> alter session set statistics_level=all;
Session altered.
SCOTT@test01p> select dept2.*,(select max(sal) from emp where emp.deptno=dept2.deptno) max_sal from dept2;
DEPTNO DNAME LOC MAX_SAL
---------- -------------- ------------- ----------
30 SALES CHICAGO 2850
20 RESEARCH DALLAS 3000
10 ACCOUNTING NEW YORK 5000
40 OPERATIONS BOSTON
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID gzuqru3da815n, child number 0
-------------------------------------
select dept2.*,(select max(sal) from emp where emp.deptno=dept2.deptno)
max_sal from dept2
Plan hash value: 443147980
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 (100)| 4 |00:00:00.01 | 10 | | | |
|* 1 | HASH JOIN OUTER | | 1 | 4 | 6 (0)| 4 |00:00:00.01 | 10 | 1321K| 1321K| 1041K (0)|
| 2 | TABLE ACCESS FULL | DEPT2 | 1 | 4 | 3 (0)| 4 |00:00:00.01 | 3 | | | |
| 3 | VIEW | VW_SSQ_1 | 1 | 3 | 3 (0)| 3 |00:00:00.01 | 7 | | | |
| 4 | HASH GROUP BY | | 1 | 3 | 3 (0)| 3 |00:00:00.01 | 7 | 1200K| 1200K| 1194K (0)|
| 5 | TABLE ACCESS FULL| EMP | 1 | 14 | 3 (0)| 14 |00:00:00.01 | 7 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ITEM_1"="DEPT2"."DEPTNO")
--可以发现查询发生了转换，变成 HASH JOIN OUTER 。
SCOTT@test01p> alter session set optimizer_features_enable='11.2.0.3';
Session altered.
SCOTT@test01p> select dept2.*,(select max(sal) from emp where emp.deptno=dept2.deptno) max_sal from dept2;
DEPTNO DNAME LOC MAX_SAL
---------- -------------- ------------- ----------
10 ACCOUNTING NEW YORK 5000
20 RESEARCH DALLAS 3000
30 SALES CHICAGO 2850
40 OPERATIONS BOSTON
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID gzuqru3da815n, child number 0
-------------------------------------
select dept2.*,(select max(sal) from emp where emp.deptno=dept2.deptno)
max_sal from dept2
Plan hash value: 652948006
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 (100)| 4 |00:00:00.01 | 4 |
| 1 | SORT AGGREGATE | | 4 | 1 | | 4 |00:00:00.01 | 28 |
|* 2 | TABLE ACCESS FULL| EMP | 4 | 5 | 3 (0)| 14 |00:00:00.01 | 28 |
| 3 | TABLE ACCESS FULL | DEPT2 | 1 | 4 | 3 (0)| 4 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMP"."DEPTNO"=:B1)
--可以发现11g查询标量子查询成本还是蛮高的，要全表扫描emp4次。我个人不建议使用标量子查询的原因，导致逻辑读很大。
--上面的sql在11g下最好改写成：
select dept2.*,a.max_sal from dept2,(select deptno,max(sal) max_sal from emp group by deptno) a where a.deptno(+)=dept2.deptno;
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4bf6ut1sp5yjk, child number 0
-------------------------------------
select dept2.*,a.max_sal from dept2,(select deptno,max(sal) max_sal
from emp group by deptno) a where a.deptno(+)=dept2.deptno
Plan hash value: 2488941779
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8 (100)| 4 |00:00:00.01 | 10 | | | |
|* 1 | HASH JOIN OUTER | | 1 | 4 | 8 (25)| 4 |00:00:00.01 | 10 | 1321K| 1321K| 1039K (0)|
| 2 | TABLE ACCESS FULL | DEPT2 | 1 | 4 | 3 (0)| 4 |00:00:00.01 | 3 | | | |
| 3 | VIEW | | 1 | 3 | 4 (25)| 3 |00:00:00.01 | 7 | | | |
| 4 | HASH GROUP BY | | 1 | 3 | 4 (25)| 3 |00:00:00.01 | 7 | 1200K| 1200K| 1197K (0)|
| 5 | TABLE ACCESS FULL| EMP | 1 | 14 | 3 (0)| 14 |00:00:00.01 | 7 | | | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DEPTNO"="DEPT2"."DEPTNO")
--可见12c在标量子查询上做了一些改进。但是我不明白为什么测试1不能转换，视乎是仅仅存在某种聚集函数时才会出现转换。
4.测试3：
--退出，取消一些参数的影响。
--看看12c增加了什么参数导致这种转变？
SYS@test01p> @hide scalar
old 10: and lower(a.ksppinm) like lower('%&1%')
new 10: and lower(a.ksppinm) like lower('%scalar%')
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
---------------------------------------- ------------------------------------------------------------------ ---------------------- ---------------------- ---------------
_optimizer_unnest_scalar_sq enables unnesting of of scalar subquery TRUE TRUE TRUE
_scalar_type_lob_storage_threshold threshold for VARCHAR2, NVARCHAR2, and RAW storage as BLOB TRUE 4000 4000
--可以猜测是隐含参数_optimizer_unnest_scalar_sq导致这种变化。
SCOTT@test01p> alter session set statistics_level=all;
Session altered.
SCOTT@test01p> alter session set "_optimizer_unnest_scalar_sq"=false;
Session altered.
SCOTT@test01p> select dept2.*,(select max(sal) from emp where emp.deptno=dept2.deptno) max_sal from dept2;
DEPTNO DNAME LOC MAX_SAL
---------- -------------- ------------- ----------
10 ACCOUNTING NEW YORK 5000
20 RESEARCH DALLAS 3000
30 SALES CHICAGO 2850
40 OPERATIONS BOSTON
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID gzuqru3da815n, child number 1
-------------------------------------
select dept2.*,(select max(sal) from emp where emp.deptno=dept2.deptno)
max_sal from dept2
Plan hash value: 652948006
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 9 (100)| 4 |00:00:00.01 | 4 |
| 1 | SORT AGGREGATE | | 4 | 1 | | 4 |00:00:00.01 | 28 |
|* 2 | TABLE ACCESS FULL| EMP | 4 | 5 | 3 (0)| 14 |00:00:00.01 | 28 |
| 3 | TABLE ACCESS FULL | DEPT2 | 1 | 4 | 3 (0)| 4 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMP"."DEPTNO"=:B1)
--可以发现执行方式回到了11G。
5.测试4：
--退出，取消一些参数的影响。
--尝试使用hint参数回到11g的执行方式
SCOTT@test01p> alter session set statistics_level=all;
Session altered.
SCOTT@test01p> select /*+ no_unnest(@a) */ dept2.*,(select /*+ qb_name(a) */ max(sal) from emp where emp.deptno=dept2.deptno) max_sal from dept2;
DEPTNO DNAME LOC MAX_SAL
---------- -------------- ------------- ----------
10 ACCOUNTING NEW YORK 5000
20 RESEARCH DALLAS 3000
30 SALES CHICAGO 2850
40 OPERATIONS BOSTON
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID gjk5nkzu2wm67, child number 0
-------------------------------------
select /*+ no_unnest(@a) */ dept2.*,(select /*+ qb_name(a) */ max(sal)
from emp where emp.deptno=dept2.deptno) max_sal from dept2
Plan hash value: 652948006
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 9 (100)| 4 |00:00:00.01 | 4 |
| 1 | SORT AGGREGATE | | 4 | 1 | | 4 |00:00:00.01 | 28 |
|* 2 | TABLE ACCESS FULL| EMP | 4 | 5 | 3 (0)| 14 |00:00:00.01 | 28 |
| 3 | TABLE ACCESS FULL | DEPT2 | 1 | 4 | 3 (0)| 4 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMP"."DEPTNO"=:B1)
--非常讨厌这种提示，no_unnest 两次否定就是肯定，就是nest的意思。老外的思维模式就是不一样。
6.测试5：
--退出，取消一些参数的影响。
--max换成count呢？
SCOTT@test01p> select dept2.*,(select count(*) from emp where emp.deptno=dept2.deptno) max_sal from dept2;
DEPTNO DNAME LOC MAX_SAL
---------- -------------- ------------- ----------
10 ACCOUNTING NEW YORK 3
20 RESEARCH DALLAS 5
30 SALES CHICAGO 6
40 OPERATIONS BOSTON 0
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2ad909b0xk6fv, child number 1
-------------------------------------
select dept2.*,(select count(*) from emp where emp.deptno=dept2.deptno)
max_sal from dept2
Plan hash value: 652948006
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 9 (100)| 4 |00:00:00.01 | 4 |
| 1 | SORT AGGREGATE | | 4 | 1 | | 4 |00:00:00.01 | 28 |
|* 2 | TABLE ACCESS FULL| EMP | 4 | 5 | 3 (0)| 14 |00:00:00.01 | 28 |
| 3 | TABLE ACCESS FULL | DEPT2 | 1 | 4 | 3 (0)| 4 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMP"."DEPTNO"=:B1)
--why？ 实际上很简单，对比上面的输出就明白了。DEPTNO=40,max_sal的输出是null，而采用记数方式输出是0。
--在做标量子查询如果没有结果，会选择NULL来输出。而count如果没有记录符合，输出是0，这样oracle无法做
--查询转换。
7.看看10053的跟踪：
--有点乱，清除shared_pool。再执行相应sql语句。
SCOTT@test01p> alter system flush shared_pool;
System altered.
...
SYS@test01p> execute dbms_sqldiag.dump_trace(p_sql_id=>'&1',p_child_number=>&2,p_component=>'Compiler',p_file_id=>'DIAG');
Enter value for 1: gzuqru3da815n
Enter value for 2: 0
PL/SQL procedure successfully completed.
--查看跟踪文件：(做了格式化处理)
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "DEPT2"."DEPTNO" "DEPTNO","DEPT2"."DNAME" "DNAME","DEPT2"."LOC" "LOC","VW_SSQ_1"."MAX(SAL)" "MAX_SAL"
FROM (
SELECT MAX("EMP"."SAL") "MAX(SAL)","EMP"."DEPTNO" "ITEM_1"
FROM "SCOTT"."EMP" "EMP"
GROUP BY "EMP"."DEPTNO") "VW_SSQ_1","SCOTT"."DEPT2" "DEPT2"
WHERE "VW_SSQ_1"."ITEM_1"(+) = "DEPT2"."DEPTNO"
SYS@test01p> execute dbms_sqldiag.dump_trace(p_sql_id=>'&1',p_child_number=>&2,p_component=>'Compiler',p_file_id=>'DIAG');
Enter value for 1: 2ad909b0xk6fv
Enter value for 2: 0
PL/SQL procedure successfully completed.
********************
Subquery Unnest (SU)
********************
SU: bypassed: Scalar subquery has null-mutating select item.
SJC: Considering set-join conversion in query block SEL$1 (#0)
--估计这部通不过，无法实现转换。
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "DEPT2"."DEPTNO" "DEPTNO","DEPT2"."DNAME" "DNAME","DEPT2"."LOC" "LOC", (
SELECT COUNT(*) "COUNT(*)"
FROM "SCOTT"."EMP" "EMP"
WHERE "EMP"."DEPTNO" = "DEPT2"."DEPTNO"
) "MAX_SAL"
FROM "SCOTT"."DEPT2" "DEPT2"
--总结：
1.12c标量子查询可以实现查询转换，仅仅出现在一些聚集函数。
2.受_optimizer_unnest_scalar_sq参数的控制。
3.并不是所有的聚集函数都会出现，比如count。
4.但是不是聚集不会转换。
5.最后一点，我个人觉得要选择合适的场合，而不是不分场合的滥用。
