select *
from (select DA_COMMENT_TEST.*,
row_number() over(order by DC_RCRE_DATE desc) as rn
from DA_COMMENT_TEST
where DC_ID in
(select DC_ID
from DA_COMMENT_KEY_DATA_TEST
group by DC_ID
having sum(decode(DCKD_KEY_TYPE, 'C', 0, 'FU', 0, 1)) = 0 and sum(decode(DCKD_KEY_VALUE, '金逸', 0, '55555', 0, 1)) = 0 and count(distinct DCKD_KEY_TYPE) = '2' and count(distinct DCKD_KEY_VALUE) = '2'))
where rn >= '1'
and rn <= '20'
and DC_RCRE_USER = 'JXY';
求帮忙把上面那条语句改为用exists ,并且加索引优化查询速度。。。现在是用in 的
这是我自己改的：
select *运行这两条语句在1W数据中取20条数据，in 的那个语句用了0.5秒，exists 的语句用了1100多秒，我想吐血啊！！有木有、是不是那里错了？不懂这个、。还有在一个表中存了300W数据，用in的 取20条也就是用了差不多400秒，那条exists 语句我真不敢试。。。
from (select DA_COMMENT_TEST.*,
row_number() over(order by DC_RCRE_DATE desc) as rn
from DA_COMMENT_TEST
where exists
(select DC_ID
from DA_COMMENT_KEY_DATA_TEST
group by DC_ID
having sum(decode(DCKD_KEY_TYPE, 'C', 0, 'FU', 0, 1)) = 0 and sum(decode(DCKD_KEY_VALUE, '金逸', 0, '55555', 0, 1)) = 0 and count(distinct DCKD_KEY_TYPE) = '2' and count(distinct DCKD_KEY_VALUE) = '2' and DA_COMMENT_TEST. DC_ID = DA_COMMENT_KEY_DATA_TEST .DC_ID ))
where rn >= '1'
and rn <= '20'
and DC_RCRE_USER = 'JXY'
------解决思路----------------------
DA_COMMENT_KEY_DATA_TEST
表增加DC_ID、DCKD_KEY_TYPE、DCKD_KEY_VALUE组合索引