急！！！SQL效率问题，数据量1亿条，要查询的数据大概1.5千万条左右

Question

所有的数据已经在一张表里，并且此表一分区。SQL如下：
select pv.url, count(pv.url) num
from table partition(table_20101213) pv
where TRUNC(mydate, 'fmhh24') 
between to_date('2010-12-13 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN') 
and to_date('2010-12-13 23:59:59','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')
and pv.url like '/aaaaa%bbbb%' 
group by pv.url 
order by num;目前在PL/SQL里查询大约需要17分钟。这个时间太长了，求改善方法。。

Answer 1

TRUNC(mydate, 'fmhh24')
用了函数就不能用索引了，不能直接mydate做过滤？

Answer 2

在url、mydate上建索引，去掉TRUNC函数。如果效率还是不能满意，就再将表按mydate用分区分开。如果能在查询时间段上做些限制就更好

Answer 3

你的一个分区表就存在1亿条数据吗？把like使用instr代替，然后建立个联合索引on table_20101213(trunc(mydate),instr(....))

Answer 4

url上有索引吗？
TRUNC(mydate, 'fmhh24')  这个建个基于函数的索引
 create index xx_trunc_idx on xx(TRUNC(mydate, 'fmhh24')); 好像是这么写.  
后面的 like改成　instr(pv.url, '/aaaaa%bbbb%' ) > 0;  我也是个土包子，你参考吧。

Answer 5

TRUNC(mydate, 'fmhh24')
在索引上做了函数处理的话，那这个索引就自动失效了呀。。

Answer 6

like里面是不是写错？是不是需要转义查？15%的选择性，走索引未必好，开并行，全扫好了，不过并行的话，要求并发不能多，否则要出错。

Answer 7

1.添加索引。不用加函数索引，把时间比较的函数挪到右边处理。count上亿数据，还带like,速度也不会很理想。（楼主什么门户啊，818）
2.过程处理，定制执行。将你想要的数据迁移到另外一张表里。迁移完后可随时较轻松查询。

Answer 8

TRUNC(mydate, 'fmhh24')   mydate这上面有索引否建个索引 去掉truncpv.url like '/aaaaa%bbbb%'  ---考虑建个instr 函数索引试试

Answer 9

去除 order by能有多大的性能提升呢？
order by 是因为我需要前面几条数据。 如果不做order by的话，那我就得在java代码里获取这些数据了。

Answer 10

在这里，instr和like，到底谁更快？刚刚换成like执行了一下，比用instr快了将近2分钟。

Answer 11

select/*+ parallel(table,n)*/ pv.url, count(pv.url) num
from table partition(table_20101213) pv
where TRUNC(mydate, 'fmhh24')  
between to_date('2010-12-13 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')  
and to_date('2010-12-13 23:59:59','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')
and pv.url like '/aaaaa%bbbb%'  
group by pv.url  
order by num;试试

Answer 12

这个是不是很耗cpu的？会不会直接把服务down掉,这个表只在production上有，如果down掉的话，那我就搞大了。

Answer 13

就是要看执行计划(explain),toad就有这功能，对该语句分析成本，才能找到解决方法呀

Answer 14

很好，不知道PL/SQL developer 有没有这功能？先找找看。