ITPub博客

首页 > 数据库 > Oracle > [2016026]12c lateral语法.txt

[2016026]12c lateral语法.txt

原创 Oracle 作者:lfree 时间:2016-02-06 22:26:39 0 删除 编辑
[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/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1987029/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6291339