1年前写的一篇总结，今天翻出来发到博客上去。

    在测试中发现一个存储过程中的一段INSERT...SELECT...操作非常耗时，每次都要跑将近两个小时，于是试着去探究原因。首先看了一下SELECT操作有没有问题，单独跑了一下SELECT语句用了不到10秒钟就跑完了，而且数据量只有50多万，这么小的数据量怎么会导致INSERT消耗那么长时间呢？于是试着加上append提示，结果还是不能解决问题。想了想有可能是这个表有什么属性比较特殊，因此自己建了个表结构相同的测试表，把数据向测试表中INSERT，结果不到20秒钟就跑完了，于是确定一定是表的原因。以前在帮同事造压力测试数据的时候也遇到过INSERT相当慢的情况，不过那次是因为数据量非常大并且没有把索引先disable掉，这次的数据量这么小，不会也是索引的原因吧？突然想到这次插入的表是一个分区表，而在分区表上建索引就有两种不同的方式：全局索引和局部索引，于是查了一下这个表建的是什么索引，一看是全局索引，于是自己觉得应该是找到了答案。
局部索引和全局索引的区别如下图所示：

http://s15/mw690/6ff05a2cgd2d1c68cd4fe&690

    通常来讲，局部索引多用于数据仓库，全局索引多用于OLTP系统。局部索引是在每个表分区内创建一个独立的索引树，互不影响；而全局索引是基于所有分区创建一个大的索引树。实验如下：
首先创建一个分区表：
ETL@CTRTEST> CREATE TABLE liutest(
        yyyymmdd                                DATE NOT NULL,
        member_id                               VARCHAR2(32) NOT NULL
)
PARTITION BY RANGE (yyyymmdd)
( PARTITION P20101228 VALUES LESS THAN (TO_DATE('2010-12-29 00:00:00','YYYY-MM-DD HH24:MI:SS')) ,
  PARTITION P20101229 VALUES LESS THAN (TO_DATE('2010-12-30 00:00:00','YYYY-MM-DD HH24:MI:SS')) ,
  PARTITION P20101230 VALUES LESS THAN (TO_DATE('2010-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS')) ,
  PARTITION P20101231 VALUES LESS THAN (TO_DATE('2011-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))
);

Table created.
然后创建一个局部索引：
ETL@CTRTEST> CREATE INDEX liutest_IND ON liutest(member_id) LOCAL;
Index created.
然后查一下为这个索引创建了多少个段：
ETL@CTRTEST> select segment_name,partition_name,segment_type from dba_segments where segment_name='LIUTEST_IND';

SEGMENT_NAME            PARTITION_NAME                 SEGMENT_TYPE
-------------------------------- ------------------------------ ------------------
LIUTEST_IND                    P20101228                      INDEX PARTITION
LIUTEST_IND                    P20101229                      INDEX PARTITION
LIUTEST_IND                    P20101230                      INDEX PARTITION
LIUTEST_IND                    P20101231                      INDEX PARTITION
可以看到每个表分区都单独创建了一个索引分区段。
然后把局部索引删掉换成全局索引：
ETL@CTRTEST> drop index liutest_IND;

Index dropped.

Elapsed: 00:00:00.15
ETL@CTRTEST> CREATE INDEX liutest_IND ON liutest(member_id);

Index created.
再看一下这个索引有多少个段：
ETL@CTRTEST> select segment_name,partition_name,segment_type from dba_segments where segment_name='LIUTEST_IND';

SEGMENT_NAME                    PARTITION_NAME                 SEGMENT_TYPE
----------------------------------- ------------------------------ ------------------
LIUTEST_IND                                                                           INDEX
可以看到这回只有一个索引段。

因此如果是创建了一个全局索引的话，虽然每次只向一个表分区内插入50多万条数据，但是为了维护全局索引，必须把每条数据插入到这个全局索引中，假设这个表保存了6个月的数据，那就是将近1亿条的数据，向拥有这么大数据量的一棵索引树上更新数据可想而知会有多慢！
而局部索引则完全不一样，它只在自己的分区上维护一棵很小的索引树，所以性能会好很多。
通常情况下在数据仓库中较多用到的是局部索引，因为如果是全局索引的话，如果某一个分区出现故障或者手工删除了某个较旧的分区，则会导致整个全局索引不可用，如：
ETL@CTRTEST> insert into liutest values(to_date('20101228','yyyymmdd'),'liujiannan');

1 row created.

Elapsed: 00:00:00.02
ETL@CTRTEST> insert into liutest values(to_date('20101229','yyyymmdd'),'caopan');

1 row created.

Elapsed: 00:00:00.00
ETL@CTRTEST> commit;

Commit complete.
我向P20101228、P20101229这两个分区中分别插入了一条数据，然后我利用索引查询P20101229这个分区中的数据：
ETL@CTRTEST> select * from liutest where member_id='caopan';

YYYYMMDD            MEMBER_ID
------------------- --------------------------------
2010-12-29 00:00:00 caopan
然后我把P20101228这个分区删除掉：
ETL@CTRTEST> alter table liutest drop partition P20101228;

Table altered.
然后再利用索引查询P20101229这个分区中的数据：
ETL@CTRTEST> select * from liutest where member_id='caopan';
select * from liutest where member_id='caopan'
*
ERROR at line 1:
ORA-01502: index 'ETL.LIUTEST_IND' or partition of such index is in unusable state
按理说应该是不会受到影响的，但是Oracle还是报错了，索引不可用了，这时候只有重建索引了：
ETL@CTRTEST> alter index liutest_ind rebuild;

Index altered.

Elapsed: 00:00:00.07
ETL@CTRTEST> select * from liutest where member_id='caopan';

YYYYMMDD            MEMBER_ID
------------------- --------------------------------
2010-12-29 00:00:00 caopan
虽然在数据仓库中全局索引有很多缺点，但是如果我们想进行唯一性约束的时候，却必须使用全局索引，因为局部索引是相互独立的，他不会检查一个分区中的值和另一个分区中的值是否相等。
其实关于全局索引和局部索引还有很多深入的问题，我只是较浅显地探究了一下，起个抛砖引玉的作用吧。