如何优化 IN

Question

有sql如下：
SELECT *
FROM People T1,
     Department T2
WHERE T1.DEP_ID = T2.ID AND T1.ACTIVE = 'Y' AND T2.SUPER_ID IN (
            SELECT T3.USER_ID
               FROM  USER T3
               WHERE (T3.USER_TYPE = 'Temp' AND T3.STATUS = 'INPROGRESS' AND T3.ACTIVE = 'Y'))IN后面的子查询非常慢，因为有好几万条符合条件的数据。那该如何优化这条SQL呢？
谢！

Answer 1

with T as(
SELECT T3.USER_ID
               FROM  USER T3
               WHERE (T3.USER_TYPE = 'Temp' AND T3.STATUS = 'INPROGRESS' AND T3.ACTIVE = 'Y')
)
SELECT *
FROM People T1,
     Department T2
WHERE T1.DEP_ID = T2.ID AND T1.ACTIVE = 'Y' AND T2.SUPER_ID=T.USER_ID

Answer 2

这种with t as是什么东西啊？

Answer 3

with as 是在查询开始前，构造一个临时表，将需要的数据放到里面去。
在这个地方，对于每一行都要对T3表扫描一次（不知道走没走索引），假设前面查询出来的结果有10W条，每条数据都要对T3表扫描一次，一共是10W次，这个效率还是比较低的，使用with as可以预先将要查询的数据放到临时表中，10W条数据进行比较的时候，只需要同临时表的数据进行比对。

Answer 4

如果外面的数据量小的话，可以用exist来代替in。

Answer 5

with as 之后的逻辑读是多少？优化最后是把执行计划贴上来

Answer 6

直接用 T2.SUPER_ID=T.USER_ID 不报错？

Answer 7

SELECT *
FROM
    People T1,
    -- Department T2
    (
        Select t02.*
        From
            Department t02
            left join (
                Select User_ID
                From 
                    User
                Where 
                    User_Type = 'Temp'
                    And Status = 'INPROGRESS'
                    And Active = 'Y'
            )t03
            On t02.Super_ID = t03.User_ID
        Where
            t03.User_ID Is Not Null
    ) T2
WHERE
    T1.DEP_ID = T2.ID 
    AND T1.ACTIVE = 'Y' 

Answer 8

10g  CBO也聪明了很多 in和exists几乎没什么区别了