当前位置:Gxlcms > 数据库问题 > ORACLE使用WITH AS和HINT MATERIALIZE优化SQL解决FILTER效率低下

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

时间:2021-07-01 10:21:17 帮助过:3人阅读

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

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


WITH PARAMS AS
 (SELECT '' USER_ID, '' SDATE, '%' || '' || '%' SNAME FROM DUAL)
SELECT AU.USERNAME, AU.USER_ID
  FROM ALL_USERS AU
 INNER JOIN PARAMS PA
    ON 1 = 1
 INNER JOIN DBA_USERS DU
    ON AU.USERNAME = DU.USERNAME
 WHERE ((PA.SDATE IS NULL AND PA.USER_ID IS NOT NULL AND
       AU.USER_ID = PA.USER_ID) OR
       
       (PA.SDATE IS NULL AND PA.USER_ID IS NULL AND
       AU.USERNAME NOT IN
       (SELECT AU.USERNAME
            FROM ALL_USERS AU
           INNER JOIN DBA_USERS DEV
              ON AU.USERNAME = DEV.USERNAME
           INNER JOIN (SELECT OWNER AS USERNAME
                        FROM ALL_TABLES T
                       WHERE T.LAST_ANALYZED = TRUNC(SYSDATE)) ATA
              ON AU.USERNAME = ATA.USERNAME)) OR
       (PA.SDATE IS NOT NULL AND
       AU.USERNAME IN
       (SELECT AU.USERNAME
            FROM ALL_USERS AU
           INNER JOIN DBA_USERS PA
              ON AU.USERNAME = PA.USERNAME
           INNER JOIN ALL_TABLES ATA
              ON PA.USERNAME = ATA.OWNER
           WHERE TO_CHAR(ATA.LAST_ANALYZED, 'YYYY-MM-DD') = PA.SDATE) AND
       AU.USER_ID = PA.USER_ID))
   AND DU.PROFILE LIKE 'D%'
   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 MATERIALIZEWITH AS 结合使用,改动成例如以下的SQL,查询速度马上提升了非常多。例如以下图所看到的。运行计划中FILTERNOT EXISTS不存在了。

WITH PARAMS AS
 (SELECT '' USER_ID, '' SDATE, '%' || '' || '%' SNAME FROM DUAL),
USERNAMEDATA AS
 (SELECT /*+ materialize */
   AU.USERNAME
    FROM ALL_USERS AU
   INNER JOIN DBA_USERS DEV
      ON AU.USERNAME = DEV.USERNAME
   INNER JOIN (SELECT OWNER AS USERNAME
                FROM ALL_TABLES T
               WHERE T.LAST_ANALYZED = TRUNC(SYSDATE)) ATA
      ON AU.USERNAME = ATA.USERNAME)
SELECT AU.USERNAME, AU.USER_ID
  FROM ALL_USERS AU
 INNER JOIN PARAMS PA
    ON 1 = 1
 INNER JOIN DBA_USERS DU
    ON AU.USERNAME = DU.USERNAME
 WHERE ((PA.SDATE IS NULL AND PA.USER_ID IS NOT NULL AND
       AU.USER_ID = PA.USER_ID) OR
       
       (PA.SDATE IS NULL AND PA.USER_ID IS NULL AND
       AU.USERNAME NOT IN (SELECT USERNAME FROM USERNAMEDATA)) OR
       (PA.SDATE IS NOT NULL AND
       AU.USERNAME IN
       (SELECT AU.USERNAME
            FROM ALL_USERS AU
           INNER JOIN DBA_USERS PA
              ON AU.USERNAME = PA.USERNAME
           INNER JOIN ALL_TABLES ATA
              ON PA.USERNAME = ATA.OWNER
           WHERE TO_CHAR(ATA.LAST_ANALYZED, 'YYYY-MM-DD') = PA.SDATE) AND
       AU.USER_ID = PA.USER_ID))
   AND DU.PROFILE LIKE 'D%'
   AND AU.USERNAME LIKE PA.SNAME


技术分享

总结:

FILTER中。NOT EXISTS后的SQL语句多次运行,本来数据量就非常大,每次都要运行一遍,结果可想而知。可是使用HINT MATERIALIZEWITH AS 结合使用,把内联视图实体化,运行过程中会创建基于视图的暂时表。

这样就不会每次NOT EXISTS都去运行一遍大数据表的扫描,仅仅须要扫描一次就可以。

可是是不是能够在WITHAS中的每一个语句都实体化那?假设WITH AS中的语句仅仅被调用一次的话,不妨不要使用HINT MATERIALIZE。由于使用HINT MATERIALIZE第一次查询会创建基于视图结果的暂时表,这也耗费一些时间。

多次使用的话能够使用HINT MATERIALIZE


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

标签:rom   计划   ast   div   语句   内联   like   lib   with   

人气教程排行