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';