如何加快建 index 索引 的时间

朋友在500w的表上建索引，半个小时都没有结束。所以就讨论如何提速。

一．先来看一下创建索引要做哪些操作：
1.把indexkey的data读到内存
==>如果data没在db_cache中，这时候很容易有大量的dbfilescatterreadwait

2.对indexkey的data作排序
==>sort_area_size或者pga_aggregate_target不够大的情况下，需要做disksort,会有大量的driectpathread/write,另外，消耗大量CPUTime

3.创建新的indexsegment,把排过序的indexdata写到所创建的indexsegment里面
==>如果index很大，那么，有时也会有redolog相关等待，如：
logbufferspace,logfilesync,logfileparallelwrite等

所以，在建大表索引时，可以增大pga，增大temptablepace,并且用nologging或并行选项。

如：
createindexidx_logsonlogs(time)nologgingparallel4;

并行度一般看CPU个数。当然在CPU比较空闲的情况下可以多并行几个。对于单CPU不建议用并行，这样反而会增加创建时间。也可以根据v$session_wait的资料，做针对性的tuning,这样可以降低点时间。

补充知识：

查看cpu信息：more/proc/cpuinfo

查看内存信息：more/proc/meminfo

查看操作系统信息：more/etc/issue

有关索引概念性的东西，请参考我的Blog：

Oracle索引详解

http://blog.csdn.net/xujinyang/article/details/6829641

二．测试

自己也测试了下。测试环境：Oracle11gR2,win764bit,CPUT66702.2G双核,内存：4G。

1.查看表的数据量：

SQL>selectcount(*)fromcustaddr;

COUNT(*)

----------

7230464

2.查看现有索引：

SQL>selectindex_name,index_typefromuser_indexeswheretable_name='CUSTADDR';

INDEX_NAMEINDEX_TYPE

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

PK_CUSTADDR_TP_723NORMAL

IX_CUSTADDR_ADDRABB_TPNORMAL

IX_CUSTADDR_TEAMID_TPNORMAL

IX_CUSTADDR_CUSTID_TPNORMAL

IX_CUSTADDR_COMPABB_TPNORMAL

IX_CUSTADDR_AREACODENORMAL

IX_CUSTADDR_ADDR_TPNORMAL

已选择7行。

3.删除索引：IX_CUSTADDR_CUSTID_TP

SQL>dropindexIX_CUSTADDR_CUSTID_TP;

索引已删除。

4.默认方式创建索引：

SQL>SETtimingon;

SQL>CREATEINDEXIX_CUSTADDR_CUSTID_TPONCUSTADDR(CUSTID);

索引已创建。

已用时间:00:00:48.37

单位：s

5.用nologging模式：
SQL>dropindexIX_CUSTADDR_CUSTID_TP;

索引已删除。

已用时间:00:00:00.09
SQL>CREATEINDEXIX_CUSTADDR_CUSTID_TPONCUSTADDR(CUSTID)NOLOGGING;

索引已创建。

已用时间:00:00:34.46

6.Nologging+parallel模式

SQL>dropindexIX_CUSTADDR_CUSTID_TP;

索引已删除。

已用时间:00:00:00.17

SQL>CREATEINDEXIX_CUSTADDR_CUSTID_TPONCUSTADDR(CUSTID)NOLOGGINGPARALLEL2;

索引已创建。

已用时间:00:00:52.56

SQL>dropindexIX_CUSTADDR_CUSTID_TP;

索引已删除。

已用时间:00:00:00.07

SQL>CREATEINDEXIX_CUSTADDR_CUSTID_TPONCUSTADDR(CUSTID)NOLOGGINGPARALLEL4;

索引已创建。

已用时间:00:00:53.44

看来在单CPU上，并行效果还不好.

7.Parallel模式

SQL>dropindexIX_CUSTADDR_CUSTID_TP;

索引已删除。

已用时间:00:00:00.02

SQL>CREATEINDEXIX_CUSTADDR_CUSTID_TPONCUSTADDR(CUSTID)PARALLEL2;

索引已创建。

已用时间:00:00:49.97

SQL>dropindexIX_CUSTADDR_CUSTID_TP;

索引已删除。

已用时间:00:00:00.02

SQL>CREATEINDEXIX_CUSTADDR_CUSTID_TPONCUSTADDR(CUSTID)PARALLEL4;

索引已创建。

已用时间:00:00:50.25

从上面的测试数据可以看出，700万的数据，建索引，也在1分钟以内。但是并行在单CPU上效果不明显，而且比光使用NOLOGGING还要慢，因为出现资源争用了，可能是CPU的争用，也可能是I/O的争用。

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