[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 (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/