如何优化海量数据的查询速度啊（有SQL语句）？

[fanjb]

/*
Oracle 9i，下边的查询（一天的数据）需要1分半到2分钟，太慢了，有何办法优化吗？
1、介绍：表cdr_table，大概有2~3亿条记录，记录按日期分区（每个小时一个分区表），cg_nbr、loss_reason上各自建有nonunique索引。
2、观察执行计划，发现用的是表访问（Table Access Full）。即使手工指定索引，即/*+index()*/，但不起作用，oracle似乎只认表访问。
*/

SELECT cg_nbr, COUNT (*),
COUNT (DECODE (loss_reason,
0, 1,
1, 1,
NULL
)
)
FROM cdr_table
WHERE start_time >= TO_DATE ('2010042000', 'yyyymmddhh24')
AND start_time <TO_DATE ('2010042100', 'yyyymmddhh24')
GROUP BY cg_nbr
HAVING COUNT (*) >= 900
AND COUNT (DECODE (loss_reason,
0, 1,
1, 1,
NULL
)
) / COUNT (*) <= 0.95

[kinlin]

select *
from (
SELECT cg_nbr, COUNT (*) as COUNT1,
COUNT (DECODE (loss_reason,
0, 1,
1, 1,
NULL
)
) COUNT2
FROM cdr_table
WHERE start_time >= TO_DATE ('2010042000', 'yyyymmddhh24')
AND start_time <TO_DATE ('2010042100', 'yyyymmddhh24')
GROUP BY cg_nbr
) A
where
COUNT1 >= 900
AND COUNT2 / COUNT1 <= 0.95

[kinlin]

或者升级数据库

[kinlin]

start_time 也建个索引

[fanjb]

已经利用此字段做分区了。尝试过建索引，但影响数据导入。[Quote=引用 3 楼 kinlin 的回复:]
start_time 也建个索引
[/Quote]

[duanzhi1984]

学习！！！

[tuies]

你这个语句使用全表扫描是正确的，其显示的是全表扫描，但是真正执行的是全分区扫描，你应该会发现分区过滤。不使用索引的原因如下：9i中虽然默认choose,但分区表与函数索引都是使用的CBO
你的where 语句中没有索引过滤条件，此时要计算成本的话，全表扫描的成本更低，若你使用提示强制使用索引的话，oracle就会多执行一遍索引扫描，性能反而会差，又因为都是nonunique索引，不能使用index_ffs，所以我觉得你只要保证使用了分区过滤技术就可以了，也就是此sql基本上没办法优化了，要优化只有从硬件考虑了.

顺便说一下，如果楼主想使用索引的话，除了提示外，最好在where条件后加一个全部满足的过滤条件(cg_nbr> -100 and cg_nbr < 10000 )，至于为什么要加过滤条件,才能使用索引我也说不上来。

[tuies]

更正下，我刚查了下资料，index_ffs 可以运行的条件就是 非空约束，若楼主的cg_nbr、loss_reason字段上包含了非空约束的话，可以试试全索引扫描
select /*+ index_join (t indexa indexb )*/count(tid)
from t_rangepart partition(p3) t

性能也不一定会好

[fanjb]

有道理。但通过调整sort_area_size等设置可以优化吗？[Quote=引用 6 楼 tuies 的回复:]
你这个语句使用全表扫描是正确的，其显示的是全表扫描，但是真正执行的是全分区扫描，你应该会发现分区过滤。不使用索引的原因如下：9i中虽然默认choose,但分区表与函数索引都是使用的CBO
你的where 语句中没有索引过滤条件，此时要计算成本的话，全表扫描的成本更低，若你使用提示强制使用索引的话，oracle就会多执行一遍索引扫描，性能反而会差，又因为都是nonunique索引，不能使用inde……
[/Quote]

[etsilence]

LZ你先在 set autotrace traceonly，再执行这个查询，看看有没有sorts(disk)，如果没有，调整sort_area_size就没有什么作用，一般出现磁盘排序操作的时候才调整它。

[tuies]

单次执行，我觉得优化后1分钟与1.5分钟，意义不大，若存在多次访问的话，我觉得这种大表的统计，最好能够建立临时表，然后晚上以定时任务的形式，把数据抽取出来，第二天我们只需要查询部分临时表就可以了