oracle海量数据查询优化问题

grass_root

数据库环境：
oracle 10gR2 +windows2003
每日大概350万条记录，表按照范围分区，每周建立一个分区。
生产库只保存两个月记录，每天凌晨会有作业将超出两月的记录转移至历史库。
现在的问题：
由于第一次转移数据（以前不转移数据，造成单表数据量大的吓人，无法通过delete删除），直接采取删分区方式删数据，引起索引失效，
然后花了很长的时间来重建索引。
重建索引后发现查询效率反而不如以前。
后台查询sql 是通过拼接SQL，指定分区来查询的。
请教各位有何办法能优化的。

tom_fans

350W×7=2250W. 按照你说的生产库保存2个月的记录，这个要看你的查询到底查询什么？

grass_root

查询条件只有时间（限制一次只能查询一周的数据）、名称、类型，这几个字段均建有索引。

gszoracle

指定分区查询，建分区索引就行

BTxigua

试试收集一下统计信息

grass_root

原帖由 gszoracle 于 2011-5-9 20:13 发表
指定分区查询，建分区索引就行

已经应用分区索引，但是速度还是不满意。
该表实时产生的数据量相当大。

myownstars

原帖由 grass_root 于 2011-5-13 14:07 发表

已经应用分区索引，但是速度还是不满意。
该表实时产生的数据量相当大。

由于第一次转移数据（以前不转移数据，造成单表数据量大的吓人，无法通过delete删除），直接采取删分区方式删数据，引起索引失效，
然后花了很长的时间来重建索引。
重建索引后发现查询效率反而不如以前。

--既然是分区索引，truncate分区之后 怎么会花很长时间重建索引，此时分区索引的所在分区记录应该为0 啊
--重建索引后 sql执行计划有无变化 查看一下索引的统计信息
--最好把执行计划贴出来以及sql的大致结构，查询条件所用的字段上是否有索引，选择性高不高

jieyancai

楼主应该建local索引，如果是local,truncate是不会引起索引失效的。

nkxu

select owner,index_name,index_type,index_name,table_name,table_owner from dba_indexes where owner='SCOTT';

select dbms_metadata.get_ddl('INDEX','INDEX_NAME','OWNER') from dual;

之后根据索引的结构重新创建索引

sfx123

“如果分区的目的是为了进行历史数据归档，那么一般来说分区主键都是根据时间字段。历史数据归档和历史查询库的使用也是优化海量数据库最常用和最有效的方法之一。通过对生产表按照时间字段进行分区，通过exchange partition的方式将分区清空，数据再exchange到历史表，可以实现快速的数据归档。这种归档是一次归档，归档到历史表中，实际上，很多系统不仅需要将生产表数据归档到历史表中，还需要将生产库的数据归档到历史库中，从而减少生产库的规模，这种归档一般需要通过数据复制的方法来实现。这种设计是需要在数据库架构设计的时候就考虑好的。”
----主题：DBA日记第三部 像Oracle一样思考 4月11日理解分区表（2）分区表和海量数据优化
http://www.oraclefans.cn/forum/showblog.jsp?rootid=29517