转载

有关大表重建索引的探讨

写这篇文章的初衷是记录处理一个公司大数据库时遇到的问题和值得总结的地方。对于大数据量的数据库而言，会遇到小数据库无法遇到的问题，执行每一步操作都可能需要很长的时间，提高操作性能最简单的办法就是并行，Oracle的很多操作都执行并行，包括：数据泵、创建索引、搜集统计信息等。下面简单记录了操作中遇到的问题。

1.IMPDP导入分区表UNIT_POWER_SECOND_T花费了66分钟。

C:/Users/LIUBINGLIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期六 11月 5 20:55:15 2011

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

下面是UNIT_POWER_SECOND_T表的结构：
SQL> desc ems.unit_power_second_t
名称是否为空? 类型
----------------------------------------- -------- ----------------------------

APPLIEDTIME                               NOT NULL DATE
METERCODE                                 NOT NULL NUMBER(38)
OBJID                                     NOT NULL NUMBER(38)
DATA                                               NUMBER(18,4)

SQL> set timing on

2.为分区表创建普通索引，且没有并行：
SQL> create index ems.idx_unit_power_second_t on ems.unit_power_second_t(applied
time,metercode,objid);

索引已创建。

已用时间: 02: 17: 04.54

SQL> set timing off
SQL> col owner format a10
SQL> col segment_name format a30
SQL> select OWNER, SEGMENT_NAME, SEGMENT_TYPE, SUM(BYTES) / 1024 / 1024 MB
2    from dba_segments
3   where wner = 'EMS'
4     and segment_name = 'UNIT_POWER_SECOND_T'
5   GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE;

OWNER SEGMENT_NAME SEGMENT_TYPE MB
---------- ------------------------------ ------------------ ----------
EMS UNIT_POWER_SECOND_T TABLE PARTITION 16459

UNIT_POWER_SECOND_T表的表分区占用的总空间是16459MB。

SQL> select OWNER, SEGMENT_NAME, SEGMENT_TYPE, BYTES / 1024 / 1024 MB
2    from dba_segments
3   where wner = 'EMS'
4     and segment_name = 'IDX_UNIT_POWER_SECOND_T';

OWNER SEGMENT_NAME SEGMENT_TYPE MB
---------- ------------------------------ ------------------ ----------
EMS IDX_UNIT_POWER_SECOND_T INDEX 19509
UNIT_POWER_SECOND_T表的索引IDX_UNIT_POWER_SECOND_T占用的总空间是19509MB，比表占用的空间还大。

SQL> set timing on
SQL> drop index ems.idx_unit_power_second_t;

索引已删除。

已用时间: 00: 00: 00.13
SQL> create index ems.idx_unit_power_second_t on ems.unit_power_second_t(applied
time,metercode,objid) parallel 2;

索引已创建。

已用时间: 01: 28: 19.49

采用并行创建索引的速度要比非并行创建索引快近一倍。

删除两个分区：
SQL> alter table ems.unit_power_second_t drop partition UNIT_POWER_SECOND_T11062
9;

表已更改。

已用时间: 00: 00: 00.18
SQL> alter table ems.unit_power_second_t drop partition UNIT_POWER_SECOND_T11071
6;

表已更改。

已用时间: 00: 00: 00.06

3.使用Rebuild重建索引：
SQL> alter index ems.idx_unit_power_second_t rebuild parallel 2;

索引已更改。

已用时间: 01: 37: 33.53

索引的REBUILD要比DROP索引，重新新建花费更多的时间，且REBUILD过程需要花费更多的空间。

SQL> select OWNER, SEGMENT_NAME, SEGMENT_TYPE, BYTES / 1024 / 1024 MB
2    from dba_segments
3   where wner = 'EMS'
4     and segment_name = 'IDX_UNIT_POWER_SECOND_T';

OWNER SEGMENT_NAME SEGMENT_TYPE MB
---------- ------------------------------ ------------------ ----------
EMS IDX_UNIT_POWER_SECOND_T INDEX 18126.1875

使用REBUILD重建后的索引会自动调整索引的占用空间。IMPDP、INSERT、索引创建和索引Rebuild都会造成大量日志的产生，在执行过程中注意生成的大量日志。

索引创建的特性：
1.索引创建过程中会使用大量的临时表空间进行排序，要比索引本身占据的空间还要大。
2.在索引创建初期Oracle不会对存储索引段的磁盘做任何的操作，也不会立即生成相应的索引段，只会在临时表空间中把索引相关的所有字段数据完成排序之后，再写到相应的磁盘索引段中存储。

正文到此结束