现有三张表,使用了分区建表, 都有 id,和mileage字段,需要联合查询得到各个id某一时间段对应的mileage总和,
我写的sql如下:select TARGET_ID, sum(mileage)as mileage from (
(select/*+ index(messageIDindex,messageTimeIndex)*/ TARGET_ID, sum(mileage) as mileage from cwits.th_messageInfo where built_time>1269187200 and built_time<1269273600 and mileage>0 group by TARGET_ID )union all(select/*+ index(messageIDindex,messageTimeIndex)*/ TARGET_ID, sum(mileage) as mileage from cwits.th_positionInfo where built_time>1269187200 and built_time<1269273600 and mileage>0 group by TARGET_ID )union all(select/*+ index(messageIDindex,messageTimeIndex)*/ TARGET_ID, sum(mileage) as mileage from cwits.th_reportInfo where built_time>1269187200 and built_time<1269273600 and mileage>0 group by TARGET_ID )) group by TARGET_ID;
三张表每天的数据量都在1000万以上, 开始没建索引查询下来要80m左右,后来针对每个表的 target_id和 built_time建了local分区索引,但是查询下来也要80m左右甚至更长。。
那位大侠帮忙看看有什么办法提高下效率呢?
我写的sql如下:select TARGET_ID, sum(mileage)as mileage from (
(select/*+ index(messageIDindex,messageTimeIndex)*/ TARGET_ID, sum(mileage) as mileage from cwits.th_messageInfo where built_time>1269187200 and built_time<1269273600 and mileage>0 group by TARGET_ID )union all(select/*+ index(messageIDindex,messageTimeIndex)*/ TARGET_ID, sum(mileage) as mileage from cwits.th_positionInfo where built_time>1269187200 and built_time<1269273600 and mileage>0 group by TARGET_ID )union all(select/*+ index(messageIDindex,messageTimeIndex)*/ TARGET_ID, sum(mileage) as mileage from cwits.th_reportInfo where built_time>1269187200 and built_time<1269273600 and mileage>0 group by TARGET_ID )) group by TARGET_ID;
三张表每天的数据量都在1000万以上, 开始没建索引查询下来要80m左右,后来针对每个表的 target_id和 built_time建了local分区索引,但是查询下来也要80m左右甚至更长。。
那位大侠帮忙看看有什么办法提高下效率呢?
另外,你的hint写法明显错误,正确应为:select/*+ index(table_name index_name)*/
走时间索引,不要走target_id和 built_time索引。
此时,你应该抛弃使用索引。
此时,你应该抛弃使用索引。
我先前的写法没有报错啊,我按你的写法查询都查不出来结果了
每个子查询选择的数据量有多大?
硬件配置怎么样,有没有启用并行?
但是,索引不是万能的,不是所有查询走索引都快!
我的sql语句的效率有没有办法优化下啊?
还有改>成>=;改<成<=,试试!
你查询的结果如果占整个表的30%上的数据,就不要强制索引了
每个子查询选择的时间就将近整个查询的1/3时间了
硬件配置:现在只是在一个PC上测试的: CPU:intel双核2.8G, 内存:2G