Oracle_Sql语句

来源：百度知道 编辑：UC知道 时间：2024/07/04 03:56:26
with tab as
(select o.*, c.id,c.reportseq,c.consultationresult,c.consultationdr, c.recorderid,c.recorder con_recorder,
c.recorddt con_recorddt
from T_CONSULTATION c
right join vieworderlist o on o.orderseq =c.orderseq
where c.orderseq is not null)

select * from tab
where OrderDT>='2009-04-28 00:00:00' and OrderDT<='2009-10-29 23:59:59'
and ( MODALITY='CR' or MODALITY='CT' or MODALITY='US' or MODALITY='MR' or MODALITY='ES')
and ( PATTYPE='W' or PATTYPE='P' or PATTYPE='O' or PATTYPE='I')
执行要30多秒，结果就13条
如果每个条件都写成一个sql，用intersect关联，起来则不到1秒就可以了，高手帮帮我，着急～～～

你的Where条件里面的Or用得太多了，用In试试吧，Oracle在10G以后对In优化后效果还是挺好的，实在不行就改用exists来改写条件

首先看
select o.*, c.id,c.reportseq,c.consultationresult,c.consultationdr, c.recorderid,c.recorder con_recorder,
c.recorddt con_recorddt
from T_CONSULTATION c
right join vieworderlist o on o.orderseq =c.orderseq
where c.orderseq is not null这条语句执行的时间长不长。如果长需要先优化他。

看索引是否都建好。
如果正常，试试下面的语句：
with tab as
(select o.*, c.id,c.reportseq,c.consultationresult,c.consultationdr, c.recorderid,c.recorder con_recorder,
c.recorddt con_recorddt
from T_CONSULTATION c
right join vieworderlist o on o.orderseq =c.orderseq
where c.orderseq is not null)

SELECT *
FROM tab
WHERE orderdt >= '2009-04-28 00:00:00'
AND orderdt <= '2009-10-29 23:59:59'
AND modality IN ('CR','CT','US','MR','ES')
AND pattype IN ('W','P','O','I');<