大表建立索引的注意事项

对一大表(百万级以上)建立索引时应当注意的事项及提高性能的手段

一、注意事项：

1、表空间和磁盘空间是否足够。索引也是一种数据，在建立索引的时候势必也会占用大量表空间。因此在对一大表建立索引的时候首先应当考虑的是空间容量问题。

2、在对建立索引的时候要对表进行加锁，因此应当注意操作在业务空闲的时候进行。

二、性能调整方面：

1、磁盘I/O

物理上，应当尽量把索引与数据分散到不同的磁盘上(不考虑阵列的情况)。

逻辑上，数据表空间与索引表空间分开。这是在建索引时应当遵守的基本准则。

2、在建立索引的时候要对表进行全表的扫描工作，因此，应当考虑调大初始化参db_file_multiblock_read_count的值。一般设置为16或更大。

调整排序区的大小在建立索引的时候要对数据进行大量的排序操作，因此，应当调整排序区的大小。

9i之前，可以在session级别上加大sort_area_size的大小，比如设置为100m或者更大。

9i以后，如果初始化参数workarea_size_policy的值为TRUE，则排序区从pga_aggregate_target里自动分配获得。

4、建立索引的时候，可以加上nologging选项。以减少在建立索引过程中产生的大量redo，从而提高执行的速度。

5、让尽可能多的源表进入buffer cache，使用cache和full hint对源表作FTS, 让它尽可能地出现在LRU的MRU一端。

--------------------------------------------------------------

SQL> alter session set db_file_multiblock_read_count=128;

SQL> select /*+ cache(t) full(t) */ count(*) from scott.t;

SQL> alter session set sort_area_size=104857600;

SQL> create index ind_t on scott.t(id)

          tablespace users

          pctfree 5 initrans 4 maxtrans 255 nologging

          storage(initial 104857600)

          parallel 4 online;

SQL> alter index ind_t logging;

SQL> alter index ind_t parallel 1;

SQL> alter session set db_file_multiblock_read_count=16;

--------------------------------------------------------------

如果workarea_size_polizy = auto的时候，sort_area_size参数将被忽略，pga_aggregate_target将被启用。它决定了整个系统中的pga大小。当然，不是一个session在pga_aggregate_target中想使用多少就使用多少内存，它受到一个隐含参数的限制，大致就是能够使用该参数的5%； 所有我认为，你可以将workarea_size_polizy ＝manual，然后设置较大的sort_area_size来满足你的imp需求。当然，imp的时候，确保就你自己登录比较好。因为，sort_area_size是针对一个session的