软件测试论坛 › [测试积点任务] › ORACLE查询IN的优化问题

测试积点老人 发表于 2020-3-6 11:23:59

ORACLE查询IN的优化问题

ORACLE查询IN的优化问题
原来用IN写的sql：
SELECT
O.ID,
(SELECT COUNT(1) FROM USER_LOGIN UL JOIN USER U ON U.IDCARD=UL.IDCARD WHERE U.DEPTID IN(SELECT ID FROM ORG WHERE TYPE!='03' START WITH ID=O.ID CONNECT BY PRIOR ID=PID)) AS TOTALLOGIN
FROM ORG O WHERE O.PID='1000' AND O.TYPE!='03' ORDER BY ID
用IN写的非常慢118s，然后改成EXISTS：
SELECT
O.ID,
(SELECT COUNT(1) FROM USER_LOGIN UL JOIN USER U ON U.IDCARD=UL.IDCARD WHERE EXISTS(SELECT ID FROM ORG O1 WHERE O1.TYPE!='03' AND O1.ID=U.DEPT START WITH O1.ID=O.ID CONNECT BY PRIOR O1.ID=O1.PID)) AS TOTALLOGIN
FROM ORG O WHERE O.PID='1000' AND O.TYPE!='03' ORDER BY O.ID
用EXISTS写的也需要118s左右，然后我改成表连接
SELECT
O.ID,
(SELECT COUNT(1) FROM USER_LOGIN UL JOIN USER U ON U.IDCARD=UL.IDCARD JOIN (SELECT ID FROM ORG) A WHERE A.TYPE!='03' START WITH A.ID=O.ID CONNECT BY PRIOR A.ID=A.PID) AS TOTALLOGIN
FROM ORG O WHERE O.PID='1000' AND O.TYPE!='03' ORDER BY O.ID

速度快了很多，但数据和上面的不一样，感觉少数据，这个不知道是什么问题？

6、自己做

bellas 发表于 2020-3-9 10:45:54

不知道，来学习下

qqq911 发表于 2020-3-9 11:02:29

查询范围不一样

jingzizx 发表于 2020-3-9 12:53:20

学习

查看完整版本: ORACLE查询IN的优化问题