发表于:2008-12-16 09:57:444楼 得分:0 用union all 快 另外a.receivertype=0 or a.receivertype=1 改为a.receivertype in(0,1) 条件字段上用索引filesid 这个地方用OR和IN的开销都是一样的
OR 和 IN 效率是一样的,UNION ALL 比UNION效率高,前者不需要去重复
结贴了,给出目前个人目前的解决方案 希望有朋友能给出更佳的方案 SQL结果:(建立聚集索引:filesid+ReceiverID+receivertype+handlestatus) SELECT distinct a.FromFlowID,a.FromFlowModelID,a.filesid,a.noticeid, a.filesname,a.senddeptname,a.sendusername,a.senddate,a.receivertype, CASE a.receivertype WHEN 0 THEN '局内文' WHEN 1 THEN '局外文' WHEN 3 THEN '个人'END AS receivertypename,a.handleStatus FROM oa_wnotice_Depthandle a,oa_wnotice b WHERE a.filesid=b.filesid and b.isdelete is null AND ( ((a.ReceiverID=3326 OR a.ReceiverID=1) AND (a.receivertype=0 OR a.receivertype=1)) OR (a.ReceiverID=8352 AND a.receivertype=3) ) AND a.handlestatus=0 ORDER BY a.SendDate desc
另外a.receivertype=0 or a.receivertype=1 改为a.receivertype in(0,1)
条件字段上用索引filesid
之前一个Oracle的同事说,一个where里面只会用到一个索引,不知道SQL是不是也是这样呢
用union all 快
另外a.receivertype=0 or a.receivertype=1 改为a.receivertype in(0,1)
条件字段上用索引filesid 这个地方用OR和IN的开销都是一样的
希望有朋友能给出更佳的方案
SQL结果:(建立聚集索引:filesid+ReceiverID+receivertype+handlestatus)
SELECT distinct a.FromFlowID,a.FromFlowModelID,a.filesid,a.noticeid, a.filesname,a.senddeptname,a.sendusername,a.senddate,a.receivertype, CASE a.receivertype WHEN 0 THEN '局内文' WHEN 1 THEN '局外文' WHEN 3
THEN '个人'END AS receivertypename,a.handleStatus
FROM oa_wnotice_Depthandle a,oa_wnotice b
WHERE a.filesid=b.filesid and b.isdelete is null
AND (
((a.ReceiverID=3326 OR a.ReceiverID=1) AND (a.receivertype=0 OR a.receivertype=1))
OR (a.ReceiverID=8352 AND a.receivertype=3)
) AND a.handlestatus=0 ORDER BY a.SendDate desc