有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呢?
谢!
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呢?
谢!
解决方案 »
- SQL查询当前审核人的问题~求赐教
- 关于数据库教学视频
- orcl中 如何将varchar类型的字段值转换为date类型?!在线等,急!!!!!!!!!!!!!!!
- SQL性能问题求问?
- oracle8i客户端,遇到一个问题,有谁帮忙解决一下???
- SQL*Plus中如何执行多个*.sql脚本文件 ---Oracle初学者问题
- Oracle 电子商务套件 11i10首次亮相
- ***请教oracle9i以sys/change_on_install登陆遇到的问题***
- 为什么表不可见?
- 请问,关于删除ORACLE的一个数据文件后,ORACLE不会报错的问题
- 数组与动态SQL问题
- 有关索引方面的问题(在线求解,解决立马给分)
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
这种with t as是什么东西啊?
在这个地方,对于每一行都要对T3表扫描一次(不知道走没走索引),假设前面查询出来的结果有10W条,每条数据都要对T3表扫描一次,一共是10W次,这个效率还是比较低的,使用with as可以预先将要查询的数据放到临时表中,10W条数据进行比较的时候,只需要同临时表的数据进行比对。
with as 之后的逻辑读是多少?优化最后是把执行计划贴上来
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'