ORACLE使用WITH AS和HINT MATERIALIZE优化SQL解决FILTER效率低下

原文：http://blog.csdn.net/liangweiwei130/article/details/37882503

-------------------------------------------------

在做项目的过程中，一个页面使用类似如下的SQL查询数据，为了保密和使用方便，我把项目中有关的表名和字段替换使用ORACLE数据库中的系统表和字段。

在我所做的项目中，类似ALL_TABLES的表中大概有8W多条数据，下面这个查询SQL很慢。

[sql]  view plain  copy

 print ?

1. WITH PARAMS AS  
2.  (SELECT '' USER_ID, '' SDATE, '%' || '' || '%' SNAME FROM DUAL)  
3. SELECT AU.USERNAME, AU.USER_ID  
4.   FROM ALL_USERS AU  
5.  INNER JOIN PARAMS PA  
6.     ON 1 = 1  
7.  INNER JOIN DBA_USERS DU  
8.     ON AU.USERNAME = DU.USERNAME  
9.  WHERE ((PA.SDATE IS NULL AND PA.USER_ID IS NOT NULL AND  
10.        AU.USER_ID = PA.USER_ID) OR  
11.          
12.        (PA.SDATE IS NULL AND PA.USER_ID IS NULL AND  
13.        AU.USERNAME NOT IN  
14.        (SELECT AU.USERNAME  
15.             FROM ALL_USERS AU  
16.            INNER JOIN DBA_USERS DEV  
17.               ON AU.USERNAME = DEV.USERNAME  
18.            INNER JOIN (SELECT OWNER AS USERNAME  
19.                         FROM ALL_TABLES T  
20.                        WHERE T.LAST_ANALYZED = TRUNC(SYSDATE)) ATA  
21.               ON AU.USERNAME = ATA.USERNAME)) OR  
22.        (PA.SDATE IS NOT NULL AND  
23.        AU.USERNAME IN  
24.        (SELECT AU.USERNAME  
25.             FROM ALL_USERS AU  
26.            INNER JOIN DBA_USERS PA  
27.               ON AU.USERNAME = PA.USERNAME  
28.            INNER JOIN ALL_TABLES ATA  
29.               ON PA.USERNAME = ATA.OWNER  
30.            WHERE TO_CHAR(ATA.LAST_ANALYZED, 'YYYY-MM-DD') = PA.SDATE) AND  
31.        AU.USER_ID = PA.USER_ID))  
32.    AND DU.PROFILE LIKE 'D%'  
33.    AND AU.USERNAME LIKE PA.SNAME  

针对上面的SQL语句执行慢的问题，我做了如下的分析：

                第一步，把语句的WHERE条件后的三个OR都分别和主查询一块执行，执行速度都很快，放到一块就很慢。

                第二步，对比上面SQL和三个OR拆分出来的三个SQL的执行计划，如下图所示。发现上面SQL的执行中有一个FILTER，过滤器谓词中用到了NOT EXISTS，是导致这条SQL跑的慢的原因。

原因找到了，就得想办法把执行计划的FILTER去掉。开始想加HINT，但是实验了很多HINT，都不起作用。最后的结果还一样，后来想到WITH AS 能提高SQL的查询速度，就把影响SQL执行的那段SQL放到WITH AS里面，结果还是一样。后来尝试把HINT MATERIALIZE和WITH AS 结合使用，修改成如下的SQL，查询速度立即提升了很多。如下图所示，执行计划中FILTER的NOT EXISTS不存在了。

[sql]  view plain  copy

 print ?

1. WITH PARAMS AS  
2.  (SELECT '' USER_ID, '' SDATE, '%' || '' || '%' SNAME FROM DUAL),  
3. USERNAMEDATA AS  
4.  (SELECT /*+ materialize */  
5.    AU.USERNAME  
6.     FROM ALL_USERS AU  
7.    INNER JOIN DBA_USERS DEV  
8.       ON AU.USERNAME = DEV.USERNAME  
9.    INNER JOIN (SELECT OWNER AS USERNAME  
10.                 FROM ALL_TABLES T  
11.                WHERE T.LAST_ANALYZED = TRUNC(SYSDATE)) ATA  
12.       ON AU.USERNAME = ATA.USERNAME)  
13. SELECT AU.USERNAME, AU.USER_ID  
14.   FROM ALL_USERS AU  
15.  INNER JOIN PARAMS PA  
16.     ON 1 = 1  
17.  INNER JOIN DBA_USERS DU  
18.     ON AU.USERNAME = DU.USERNAME  
19.  WHERE ((PA.SDATE IS NULL AND PA.USER_ID IS NOT NULL AND  
20.        AU.USER_ID = PA.USER_ID) OR  
21.          
22.        (PA.SDATE IS NULL AND PA.USER_ID IS NULL AND  
23.        AU.USERNAME NOT IN (SELECT USERNAME FROM USERNAMEDATA)) OR  
24.        (PA.SDATE IS NOT NULL AND  
25.        AU.USERNAME IN  
26.        (SELECT AU.USERNAME  
27.             FROM ALL_USERS AU  
28.            INNER JOIN DBA_USERS PA  
29.               ON AU.USERNAME = PA.USERNAME  
30.            INNER JOIN ALL_TABLES ATA  
31.               ON PA.USERNAME = ATA.OWNER  
32.            WHERE TO_CHAR(ATA.LAST_ANALYZED, 'YYYY-MM-DD') = PA.SDATE) AND  
33.        AU.USER_ID = PA.USER_ID))  
34.    AND DU.PROFILE LIKE 'D%'  
35.    AND AU.USERNAME LIKE PA.SNAME  

总结：

在FILTER中，NOT EXISTS后的SQL语句多次执行，本来数据量就很大，每次都要执行一遍，结果可想而知。但是使用HINT MATERIALIZE和WITH AS 结合使用，把内联视图实体化，执行过程中会创建基于视图的临时表。这样就不会每次NOT EXISTS都去执行一遍大数据表的扫描，只需要扫描一次即可。

但是是不是可以在WITHAS中的每个语句都实体化那？如果WITH AS中的语句只被调用一次的话，最好还是不要使用HINT MATERIALIZE，因为使用HINT MATERIALIZE第一次查询会创建基于视图结果的临时表，这也耗费一些时间。多次使用的话可以使用HINT MATERIALIZE。