select count(distinct linkid)
from (select a.*
from (select *
from (select *
from C200904
union
select * from C200905)) a,
(select *
from (select *
from R200904
union
select * from R200905)) b
where a.linkid = b.linkid
and (b.msgcontent in
('10', '101', '1011', '1012', '1013', '1014', '1015', '1017',
'1018', '102', '1022', '100', '1016', '1019', '1021'))
and b.rcv = '1066010199')
where rcv like '%13567878765'
and REPORTSTAT = '0'
AND LINKID IS NOT NULL
and SERVICEID = '10051'
and RCVAREAID = '024'
and to_char(SENDTIME, 'yyyymmddhh24') between '2009040600' and
'2009050623';这个如果只是从一张表中查询没有union,则效率还行,一旦跨几个表查询效率就超慢。。大家帮忙优化一下吧小弟多谢了。
from (select a.*
from (select *
from (select *
from C200904
union
select * from C200905)) a,
(select *
from (select *
from R200904
union
select * from R200905)) b
where a.linkid = b.linkid
and (b.msgcontent in
('10', '101', '1011', '1012', '1013', '1014', '1015', '1017',
'1018', '102', '1022', '100', '1016', '1019', '1021'))
and b.rcv = '1066010199')
where rcv like '%13567878765'
and REPORTSTAT = '0'
AND LINKID IS NOT NULL
and SERVICEID = '10051'
and RCVAREAID = '024'
and to_char(SENDTIME, 'yyyymmddhh24') between '2009040600' and
'2009050623';这个如果只是从一张表中查询没有union,则效率还行,一旦跨几个表查询效率就超慢。。大家帮忙优化一下吧小弟多谢了。
2.union 改为union all
3.把in 和distinct 想办法换掉。你的C200905这种表里放的是什么?ID有重复的么?
from (select a.*
from (select * from C200904
union
select * from C200905) a,
(select * from R200904
union
select * from R200905) b
where a.linkid = b.linkid
and (b.msgcontent in
('10', '101', '1011', '1012', '1013', '1014', '1015', '1017',
'1018', '102', '1022', '100', '1016', '1019', '1021'))
and b.rcv = '1066010199')
where rcv like '%13567878765'
and REPORTSTAT = '0'
AND LINKID IS NOT NULL
and SERVICEID = '10051'
and RCVAREAID = '024'
and to_char(SENDTIME, 'yyyymmddhh24') between '2009040600' and
'2009050623';1 把*换成字段
2 看看索引
现在那张c表中的数据量不是很大,效率慢点能接受,只是担心以后数据量大了速度就受不了了,呵呵。
多谢各位啦。。