Oracle使用WITH AS和HINT MATERIALIZE优化SQL解决FILTER效率

在做项目的过程中,一个页面使用类似如下的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

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

Linux-6-64下安装Oracle 12C笔记

CentOS 6.4下安装Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虚拟机中安装步骤

Debian 下 安装 Oracle 11g XE R2

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

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

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

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

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

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/43358952e66980211c25e28f3dc90862.html