[2016026]12c lateral语法.txt
12c 支持一种LATERAL的写法,例子:
SELECT e1.*, e3.avg_sal
FROM scott.emp e1
,LATERAL (SELECT AVG (e2.sal) avg_sal
FROM scott.emp e2
WHERE e1.deptno != e2.deptno) e3;
-- 显示emp每行,同时显示其它不是本部门的平均薪水.我当时看到以上语句我想到如果不这样写,我个人并喜欢ansi的语法,
-- 但是像上面的语句,如果要写以前11g下的语法,该如何写呢?
-- 自己一下子也想不出来.看看10053跟踪:
alter session set events '10053 trace name context forever, level 12';
SELECT e1.*, e3.avg_sal
FROM scott.emp e1
,LATERAL (SELECT AVG (e2.sal) avg_sal
FROM scott.emp e2
WHERE e1.deptno != e2.deptno) E3;
alter session set events '10053 trace name context off';
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 71hna731k6dk7, child number 0
-------------------------------------
SELECT e1.*, e3.avg_sal FROM scott.emp e1 ,LATERAL (SELECT AVG
(e2.sal) avg_sal FROM scott.emp e2
WHERE e1.deptno != e2.deptno) E3
Plan hash value: 4262987483
-----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 45 (100)| |
| 1 | NESTED LOOPS | | 14 | 714 | 45 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
| 3 | VIEW | VW_LAT_A18161FF | 1 | 13 | 3 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 7 | | |
|* 5 | TABLE ACCESS FULL| EMP | 9 | 63 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / E1@SEL$1
3 - SEL$2 / E3@SEL$1
4 - SEL$2
5 - SEL$2 / E2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("E1"."DEPTNO"<>"E2"."DEPTNO")
--查看10053跟踪:
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "E1"."EMPNO" "EMPNO","E1"."ENAME" "ENAME","E1"."JOB" "JOB","E1"."MGR" "MGR","E1"."HIREDATE" "HIREDATE","E1"."SAL" "SAL","E1"."COMM"
"COMM","E1"."DEPTNO" "DEPTNO","VW_LAT_A18161FF"."AVG_SAL_0" "AVG_SAL"
FROM "SCOTT"."EMP" "E1", LATERAL( (
SELECT AVG("E2"."SAL") "AVG_SAL_0"
FROM "SCOTT"."EMP" "E2"
WHERE "E1"."DEPTNO" <> "E2"."DEPTNO")) "VW_LAT_A18161FF";
--看来这种语法不好转换.我举上面的例子实际上许多sql语句oracle最终转换oracle为(+)的语法的.可以看看我以前的例子:
http://blog.itpub.net/267265/viewspace-1593068/