千万级大表如何更快速的创建索引

Question

小白要在千万级数据的表上建索引，8个索引，创建了一个晚上都木有成功，这要是生产上线日岂不是要挂，要挂呀？心急如焚搜搜搜.....找到一个好资料....一． 先来看一下创建索引要做哪些操作：1. 把index key的data 读到内存==>如果data 没在db_cache 中，这时候很容易有大量的db file scatter read wait 2. 对index key的data 作排序==>sort_area_size 或者pga_aggregate_target 不够大的情况下，需要做disk sort, 会有大量的driect path read/write , 另外，消耗大量CPU Time 3. 创建新的index segment , 把排过序的index data 写到所创建的index segment 里面==>如果index 很大，那么，有时也会有redo log 相关等待，如：log buffer space ,log file sync , log file parallel write 等所以，在建大表索引时，可以增大pga，增大temp tablepace,并且用nologging或并行选项。如：create index idx_logs on logs(time) nologging parallel 4;并行度一般看CPU 个数。当然在CPU 比较空闲的情况下可以多并行几个。对于单CPU 不建议用并行，这样反而会增加创建时间。也可以根据v$session_wait 的资料，做针对性的tuning , 这样可以降低点时间。补充知识：查看cpu 信息：more /proc/cpuinfo查看内存信息：more /proc/meminfo查看操作系统信息：more /etc/issue有关索引概念性的东西，请参考我的Blog：Oracle 索引 详解：/database/201110/107271.html
----------------------------------------------------------------------------------
--场景一：
SQL> CREATE index IX_RC_MATCH ON  CONTROL_MATCH(ITS_ORDER_NO, TEL) initrans 16 online;
 
Index created
 
Executed in 306.588 seconds
---场景二：
SQL> SET TIMING ON;
SQL> CREATE index IX_RC_MATCH ON  CONTROL_MATCH(ITS_ORDER_NO, TEL) initrans 16 NOLOGGING PARALLEL 4 online; 
 
Index created
 
Executed in 137.983 seconds
---场景三：
SQL> set timing on;
SQL> CREATE index IX_RC_MATCH ON  CONTROL_MATCH(ITS_ORDER_NO, TEL) initrans 16 NOLOGGING online; 
 
Index created
 
Executed in 302.689 seconds
--场景四：
SQL> CREATE index IX_RC_MATCH ON  CONTROL_MATCH(ITS_ORDER_NO, TEL) initrans 16 NOLOGGING PARALLEL 6 online; 
Index created
 
Executed in 87.142 seconds--场景五：
SQL> set timing on;
SQL> CREATE index IX_RC_MATCH ON  CONTROL_MATCH(ITS_ORDER_NO, TEL) initrans 16 NOLOGGING PARALLEL 8 online;
 
Index created
 
Executed in 102.274 seconds
--场景六：
SQL> CREATE index IX_RC_MATCH ON  CONTROL_MATCH(ITS_ORDER_NO, TEL) initrans 16 NOLOGGING PARALLEL 5 online;
 
Index created
 
Executed in 167.981 seconds
---场景七：
SQL> set timing on;
SQL> CREATE index IX_RC_MATCH ON  CONTROL_MATCH(ITS_ORDER_NO, TEL) initrans 16 NOLOGGING PARALLEL 7 online;
 
Index created
 
Executed in 100.668 seconds
--场景八： 
SQL> set timing on;
SQL> CREATE index IX_RC_MATCH ON  CONTROL_MATCH(ITS_ORDER_NO, TEL) initrans 16  PARALLEL 7 online;
 
Index created
 
Executed in 197.809 seconds

Answer 1

可以将表中数据转移到临时表，然后在空表上建立索引，完成之后再将数据重新insert到索引表中

Answer 2

给你一个大表创建通用参考：
1.业务不忙的时候，反正后台跑，一般做成定时任务
2.临时表空间足够大（8个索引可能占多大空间，就给多大，自行估算一下）
#8 15 * * * /jyc/addindex.sh[root@db]/jyc#more /jyc/addindex.sh
#!/bin/sh
su - oracle -c "sqlplus /nolog <<EOF
set time on
set timing on
conn xxx/xxx
alter session set workarea_size_policy=manual; 
alter session set sort_area_size=1073741820; 
alter session set sort_area_retained_size=1073741820; 
alter session set db_file_multiblock_read_count=128; 
create index IDX_XXX on XXX(字段) online NOLOGGING parallel 8 compute statistics;
alter index IDX_XXX noparallel;
alter index IDX_XXX LOGGING;
exit;
EOF" >> /jyc/IDX_XXX.out另外查看索引创建的进度：
 col opname format a32
col target_desc format a32
col perwork format a12
set lines 131
select sid,OPNAME,TARGET_DESC,sofar,TOTALWORK,trunc(sofar/totalwork*100,2)||'%' as perwork 
from v$session_longops where sofar!=totalwork; 

Answer 3

是个好办法，只是我们java开发和数据库dba是两个组织，上线时间限定在1个小时，40个系统同时上线，系统关联性大，dba应该头都不用摇就把我们给拒绝了...

Answer 4

1、先插入数据，在建立索引
2、业务空闲时，调整PGA的大小