lateral函数oracle,[2016026]12c lateral语法.txt

[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