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