[Oracle]With Clase與SQL Server的CTE用法不太一樣喔，要注意！

        SQL Server的With子句叫CTE，可以說是暫存的結果集，讓語法看器來更簡潔，可以Reuse結果；Oracle也有With子句，叫Subquery Factoring，我以為用法跟SQL Server一樣用，實際上有滿大的差異，今天花點時間研究一下，原來Oracle可以把with子句當inline view(在FORM裡的子查詢)或temporary table來處理喔，很不一樣吧

        據說Oracle會自行判斷with子句當inline view或temporary table處理，讓它自行判斷往往會發生意想不到的結果，可以使用兩個undocumented materialize hint與inline hint來指定用哪種囉


        以下我分別針對兩種用法來看執行計畫的差異囉，大家可以看到undocumented materialize hint的話，Oracle真的會產生TEMP資料表喔，如果是習慣SQL Server的用法的，記得用這種的就對啦

• inline view

explain plan for

WITH dept_count AS (

  SELECT /*+ INLINE */ deptno, COUNT(*) AS dept_count

  FROM   emp

  GROUP BY deptno)

SELECT e.ename AS employee_name,

       dc1.dept_count AS emp_dept_count,

       m.ename AS manager_name,

       dc2.dept_count AS mgr_dept_count

FROM   emp e,

       dept_count dc1,

       emp m,

       dept_count dc2

WHERE  e.deptno = dc1.deptno

AND    e.mgr = m.empno

AND    m.deptno = dc2.deptno;

select * from table(dbms_xplan.display);

• temporary table

explain plan for

WITH dept_count AS (

  SELECT /*+ MATERIALIZE */ deptno, COUNT(*) AS dept_count

  FROM   emp

  GROUP BY deptno)

SELECT e.ename AS employee_name,

       dc1.dept_count AS emp_dept_count,

       m.ename AS manager_name,

       dc2.dept_count AS mgr_dept_count

FROM   emp e,

       dept_count dc1,

       emp m,

       dept_count dc2

WHERE  e.deptno = dc1.deptno

AND    e.mgr = m.empno

AND    m.deptno = dc2.deptno;

select * from table(dbms_xplan.display);

參考資料
WITH Clause : Subquery Factoring