【ORACLE】Oracle提高篇之WITH AS

本文深入探讨WITHAS短语,即子查询部分在SQL语句中的应用,它能显著提升查询效率,尤其是在处理复杂UNION ALL操作时。通过实际案例,文章详细解释了WITHAS如何增强SQL语句的可读性和性能,包括其与增删改查操作的结合使用。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

WITH AS 的含义

WITH AS 短语,也叫做子查询部分(subquery factoring),可以定义一个sql片断,该片断会被整个sql语句所用到,特别对于UNION ALL比较有用。因为UNION ALL 的每个部分可能相同,但是如果每个部分都去执行一遍的话,成本太高,所以可以使用WITH AS语句,只需要执行一遍即可。

WITH AS 的优点

1.增加了sql的易读性,如果构造了多个子查询,结构会更清晰
2.提高效率,一次构建,多次使用。如果with as 短语定义的表名被调用两次以上,则优化器会自动把with as 短语所获取的数据放入临时表中,如果只是被调用一次,则不会。提示materialize则是强制将with as短语中的数据放到一个全局临时表中。在执行计划中 SYS_TEMP_XXXXX就是临时表+

WITH AS 的语法
-- 单个表
with tmpTableName as (select * from tb_name)
select * from tmpTableName
-- 加materialize
with tmpTableName as 
(select  /*+ materialize */ t.* from tb_name t)
select * from tmpTableName
-- 多个表    
with tmp1 as (select * from tb_1),
     tmp2 as (select * from tb_2)
select * from tmp1,tmp2  where tmp1.id  = tmp2.id
WITH AS 与增删改查结合

学生表:
在这里插入图片描述
成绩表:
在这里插入图片描述

  1. select查询
with t1 as
 (select * from STUDENT),
t2 as
 (select * from score)
select * from t1, t2 where t1.stuid = t2.stuid

在这里插入图片描述
2. insert插入
插入语句中 with as 要放在insert的后面

insert into STUDENT
  with t1 as
   (select * from STUDENT t)
  select t1.stuid + 10 as stuid,
         t1.stuname || '1' as stuname,
         t1.stusex,
         t1.stuclass
    from t1
   where t1.stuid = '5'

在这里插入图片描述
3. delete删除
同上,删除的时候也不能把with as 放在前面

delete from STUDENT t
 where t.stuid in (with t1 as (select * from STUDENT t)
                    select t1.stuid from t1 where t1.stuid = '15'
                   )
  1. update更新
update STUDENT t
   set t.stuname =
       (with t1 as (select * from STUDENT t)
         select t1.stuname || '55555555'
           from t1
          where t1.stuid = '5')
          where t.stuid = '5'

在这里插入图片描述

总结

基本上,我使用with as用得最多的地方是在select查询上,其他地方看你的情况了,例子比较简单,理解就好。

<think>我们正在处理用户关于Oracle数据库中WITH AS子句(公用表表达式)的查询。根据引用内容,我们可以整合相关信息,但注意引用中并没有直接讨论WITH AS子句。因此,我们将主要依靠通用知识来回答,并尽可能结合引用中提到的Oracle特性(如执行计划、参数设置等)来增强回答的可靠性。 用户要求:Oracle WITH AS子句的用法和示例。 回答结构: 1. 解释WITH AS子句是什么,以及它的作用。 2. 说明使用WITH AS子句的好处。 3. 提供基本语法。 4. 给出示例,并尽可能结合引用中的内容(如执行计划展示)来展示如何分析WITH AS子句的查询。 5. 注意事项(如Oracle版本支持、性能等)。 6. 生成相关问题。 注意:引用[2]中提到了执行计划的展示(DBMS_XPLAN.DISPLAY),我们可以在示例中展示如何查看WITH AS查询的执行计划,以帮助用户理解性能。 回答内容: ### Oracle WITH AS子句(公用表表达式)的用法 `WITH AS`子句,也称为公用表表达式(Common Table Expression, CTE),是Oracle SQL中用于定义临时结果集的一种方法,该结果集可以在一个查询中多次引用。它通常用于简化复杂的SQL查询,特别是涉及子查询或重复使用相同子查询的情况。`WITH AS`子句在Oracle 9i版本2及以上可用。 #### 优点 - **提高可读性**:将复杂的子查询分解为命名的临时结果集,使SQL语句更易于理解。 - **提高性能**:Oracle可能会将公用表表达式的结果进行物化(临时存储),避免重复计算,从而提升查询性能(尤其是在多次引用同一个CTE时)[^1]。 - **支持递归查询**:通过`WITH`子句可以实现递归查询,处理层次结构数据(如组织结构、目录树)。 #### 基本语法 ```sql WITH cte_name (column1, column2, ...) AS ( SELECT ... -- 定义CTE的查询 ) SELECT ... -- 主查询,可以引用cte_name ``` 可以定义多个CTE,用逗号分隔: ```sql WITH cte1 AS (SELECT ...), cte2 AS (SELECT ...) SELECT ... ``` #### 示例 ##### 示例1:基本使用 假设我们有一个员工表`employees`,我们想找出部门平均工资高于公司平均工资的部门。使用`WITH AS`可以将两个平均工资的计算分开: ```sql WITH dept_avg AS ( SELECT department_id, AVG(salary) avg_sal FROM employees GROUP BY department_id ), comp_avg AS ( SELECT AVG(salary) overall_avg FROM employees ) SELECT d.department_id, d.avg_sal FROM dept_avg d, comp_avg c WHERE d.avg_sal > c.overall_avg; ``` ##### 示例2:递归查询(层次结构) 递归CTE可以用于查询树状结构,例如员工和经理的层次关系: ```sql WITH emp_hierarchy (employee_id, last_name, manager_id, level) AS ( -- 起始点:顶级经理(没有经理) SELECT employee_id, last_name, manager_id, 1 FROM employees WHERE manager_id IS NULL UNION ALL -- 递归成员:连接到上一级 SELECT e.employee_id, e.last_name, e.manager_id, eh.level + 1 FROM employees e JOIN emp_hierarchy eh ON e.manager_id = eh.employee_id ) SELECT employee_id, last_name, level FROM emp_hierarchy ORDER BY level; ``` ##### 示例3:结合执行计划分析 我们可以使用引用[2]中提到的方法来查看CTE查询的执行计划,以了解其性能: ```sql EXPLAIN PLAN FOR WITH dept_avg AS (... ) -- 上述示例1的CTE SELECT ...; -- 主查询 SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY()); ``` 通过执行计划,我们可以观察Oracle是否对CTE进行了物化(可能显示为“VIEW”或“TEMPORARY TABLE”操作),以及整个查询的执行步骤。 #### 注意事项 - **版本支持**:递归CTE在Oracle 11g R2及以上版本中支持。 - **性能**:虽然CTE可以提高可读性,但有时可能不会像预期那样物化结果,特别是当CTE只被引用一次时,优化器可能会将其内联展开。多次引用时,Oracle更可能进行物化。可以通过提示`MATERIALIZE`强制物化(例如:`WITH cte AS (SELECT /*+ MATERIALIZE */ ... )`),但需谨慎使用。 - **替代方案**:对于复杂的查询,也可以考虑使用临时表或视图,但CTE提供了一种更轻量级的方式(无需创建数据库对象)。 ### 相关问题 1. 在Oracle中,公用表表达式(CTE)和临时表有什么区别? 2. 如何优化使用WITH AS子句的复杂查询性能? 3. 在Oracle 11g之前的版本中如何实现递归查询? 4. 如何强制Oracle物化公用表表达式的结果? 5. WITH AS子句在哪些场景下可能不适用? 以上回答结合了Oracle的通用知识,并引用了执行计划查看的方法(引用[2])来增强实用性。同时,我们提到参数设置顺序的重要性(引用[5]的注意点)虽然不直接相关,但提醒用户在使用任何Oracle特性时都应注意正确的配置顺序。</think>### Oracle WITH AS 子句用法详解 `WITH AS`子句(公用表表达式,CTE)是 Oracle 中用于创建**临时命名结果集**的高级 SQL 特性,可显著提升复杂查询的可读性和可维护性。 #### 核心优势 1. **代码复用**:避免重复子查询 2. **逻辑分层**:将复杂查询拆解为模块化单元 3. **递归支持**:处理层次结构数据(如组织结构) 4. **优化器提示**:支持 `MATERIALIZE`/`INLINE` 提示控制执行计划 #### 基本语法 ```sql WITH cte_name (column_list) AS ( SELECT ... -- CTE 定义 ) SELECT ... -- 主查询引用 CTE ``` #### 典型应用示例 **示例 1:简化多层子查询** 查询部门平均工资高于公司平均工资的部门: ```sql WITH dept_sal AS ( SELECT deptno, AVG(sal) avg_sal FROM emp GROUP BY deptno ), comp_sal AS ( SELECT AVG(sal) overall_avg FROM emp ) SELECT d.deptno, d.avg_sal FROM dept_sal d JOIN comp_sal c ON d.avg_sal > c.overall_avg; ``` **示例 2:递归查询层级数据** 查询员工管理树(Oracle 11gR2+): ```sql WITH emp_tree (emp_id, name, mgr_id, lvl) AS ( -- 锚点成员:顶级管理者 SELECT empno, ename, mgr, 1 FROM emp WHERE mgr IS NULL UNION ALL -- 递归成员:逐级向下 SELECT e.empno, e.ename, e.mgr, t.lvl+1 FROM emp e JOIN emp_tree t ON e.mgr = t.emp_id ) SELECT emp_id, LPAD(' ', 2*(lvl-1)) || name AS hierarchy FROM emp_tree; ``` **示例 3:结合 DML 操作** 使用 CTE 更新数据: ```sql WITH high_sal AS ( SELECT empno FROM emp WHERE sal > 5000 ) UPDATE emp SET bonus = 1000 WHERE empno IN (SELECT empno FROM high_sal); ``` #### 性能优化技巧 1. **物化提示**:强制结果集持久化 ```sql WITH /*+ MATERIALIZE */ cte AS (...) ``` 2. **结果集复用**:多次引用的 CTE 自动缓存 3. **替代视图**:避免创建物理视图对象 4. **执行计划分析**:使用引用[2]方法检查优化 ```sql EXPLAIN PLAN FOR WITH cte AS (...) SELECT ...; SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY()); ``` #### 注意事项 1. **作用域限制**:CTE 仅在当前查询有效 2. **版本要求**:递归查询需 Oracle 11gR2+ 3. **性能权衡**:简单查询可能无需 CTE 4. **替代方案**:复杂场景可考虑全局临时表(GTT) > **诊断提示**:若 CTE 性能不佳,可通过引用[2]方法分析执行计划,或检查是否需添加优化器提示[^1][^2]。
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值