with as

概述：

• WITH AS： 就是将一个子查询部分独立出来，有时候为了提高 sql 语句的可读性，有时候为了提高 sql 语句性能

使用场景：

• 当有多个相似子查询的时候，用 with as 写公共部分。
• 因为子查询结果在内存临时表中，故执行效率较高（临时表在会话结束后自动被 PGA 清除）

注意情况：

• 一般来说，如果 with as 短语定义的表名被调用 2 次及以上，则 CBO 优化器会自动把 with as 短语所获取的数据放入临时表中（对应执行计划中的 SYS_TEMP_XXX）。
• 如果只是被调用 1 次，则不会。

/+ materialize/ 优化

hint 关键字	描述
/*+ materialize*/	会强制性要求 with as 中的结果转换为 临时表
/*+ inline*/	与上相反，不转换

实例1：当 with as 短语定义的表名被使用 1 次时

WITH t_emp AS
 (SELECT /*+ materialize*/ e.empno, e.ename, e.sal
    FROM scott.emp e
   WHERE e.sal > (SELECT AVG(e_1.sal) FROM scott.emp e_1))
SELECT * FROM t_emp;

执行计划：

实例2：当 with as 短语定义的表名被使用 2 次及以上时

WITH t_emp AS
 (SELECT /*+ materialize*/ e.empno, e.ename, e.sal
    FROM scott.emp e
   WHERE e.sal > (SELECT AVG(e_1.sal) FROM scott.emp e_1))
SELECT * FROM t_emp t WHERE t.empno = '7566'
UNION ALL
SELECT * FROM t_emp t WHERE t.empno = '7698';