首页 新闻 搜索 专区 学院

ORACLE查询IN的优化问题

0
[已关闭问题] 关闭于 2020-05-26 15:37

原来用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
速度快了很多,但数据和上面的不一样,感觉少数据,这个不知道是什么问题,各位老哥帮忙看一下

kfsrex的主页 kfsrex | 菜鸟二级 | 园豆:202
提问于:2020-01-15 10:29

没有那个老哥评论一下吗

kfsrex 4个月前
< >
分享
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册