ORACLE查询IN的优化问题

测试积点老人

ORACLE查询IN的优化问题
原来用IN写的sql：

1. SELECT
   
2. O.ID,
   
3. (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
   
4. FROM ORG O WHERE O.PID='1000' AND O.TYPE!='03' ORDER BY ID
   
5. 用IN写的非常慢118s，然后改成EXISTS：
   
6. SELECT
   
7. O.ID,
   
8. (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
   
9. FROM ORG O WHERE O.PID='1000' AND O.TYPE!='03' ORDER BY O.ID
   
10. 用EXISTS写的也需要118s左右，然后我改成表连接
    
11. SELECT
    
12. O.ID,
    
13. (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
    
14. FROM ORG O WHERE O.PID='1000' AND O.TYPE!='03' ORDER BY O.ID

复制代码

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

6、自己做

bellas

不知道，来学习下

qqq911

查询范围不一样

jingzizx

学习