Oracle With Clause和优化场景

       使用WITH AS 语句可以为一个子查询语句块定义一个名称，使用这个子查询名称可以 在查询语句的很多地方引用这个子查询。Oracle 数据库像对待内联视图或临时表一样对待 被引用的子查询名称，从而起到一定的优化作用。with子句是9i新增语法。你可以在任何一个顶层的SELECT 语句以及几乎所有类型的子查询语句前，使用子查询定义子句。被定义的子查询名称可以在主查询语句以及所有的子查询语句中引用，但未定义前不能引用。with子句中不能嵌套定义<也就是with子句中不能有with子句>，但子查询中出现的“子查询定义”语句可以引用已定义的子查询名称。<可以引用前面已经定义的with子句>

     WITH子句相关总结:

       1、在同级select前有多个查询定义的时候，第1个用with，后面的不用with，并且用逗号隔开。

       2、最后一个with 子句与下面的查询之间不能有逗号，只通过右括号分割,with 子句的查询必须用括号括起来。

       3、如果定义了with子句，而在查询中不使用，那么会报ora-32035 错误：未引用在with子句中定义的查询名。（至少一个with查询的name未被引用，解决方法是移除未被引用的with查询），注意：只要后面有引用的就可以，不一定非要在主查询中引用，比如后面的with查询也引用了，也是可以的。

       4、前面的with子句定义的查询在后面的with子句中可以使用。但是一个with子句内部不能嵌套with子句。

       5、with查询的结果列有别名，引用的时候必须使用别名或*。

 

     WITH语句的优点:

      1. SQL可读性增强。比如对于特定with子查询取个有意义的名字等。

      2. with子查询只执行一次，将结果存储在用户临时表空间中，可以引用多次，增强性能。

     优化场景:    

        此SQL语句有性能问题，其实不太慢6s，不过可以优化的更快(由于涉及公司实体表，只有将表名作了调整)。  

     

SELECT * FROM (SELECT T.*,   ROW_NUMBER() OVER(PARTITION BY EVENT_ID ORDER BY EVENT_ID) FO    FROM FAST_INFO T  WHERE T.IS_PROD = 1       OR (T.IS_PROD = 0 AND T.EVENT_TYPE = 9)) A,       (SELECT U.EVENT_ID, COUNT(distinct u.account) AS RN1,               count(distinct(case when u.zziuser = '1'                         then u.account end)) RN2,               count(distinct(case                        when u.zziuser = '2'                         then u.account end)) RN4          FROM AFFECT_TRANSFORMER T, AFFECT_USER U         WHERE T.EVENT_ID = U.EVENT_ID           AND T.TRANS_CODE = U.TRANS_CODE           AND U.STATE = 0         GROUP BY U.EVENT_ID) U WHERE A.EVENT_ID = U.EVENT_ID   AND A.FO = 1   AND A.LINE_RESTORATION_TIME BETWEEN (SYSDATE - 3) AND SYSDATE;

           

       通过看执行计划可以看到AFFECT_TRANSFORMER 和AFFECT_USER在做hash join，性能消耗在AFFECT_USER（有两百多万的数据，且数据量一直在增长）做了全表扫描，其实可以不用全表扫描，只是统计一个停电事件下面影响了哪些用户。

       优化过程：

            优化思路就是要查AFFECT_USER表走索引，而以此种嵌套的方式是不会走索引的，而把嵌套展开也不行，因为有group by。此时用with可以派上用场。

            with  fast1 as   (select * from  FAST_INFO  where 条件)，

                     fast2 as    (select * from AFFECT_TRANSFORMER,AFFECT_USER  where fast1.event_id = 条件)

            说明，先到FAST_INFO的停电单找出来，其实只有几条数据，然后关联影响用户，这个时候查询的选择性强，则走索引，实际测试速度提升了几倍。