Oracle index for large table
如何加快建 index 索引 的时间
朋友在500w的表上建索引，半个小时都没有结束。所以就讨论如何提速。
一． 先来看一下创建索引要做哪些操作：
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
linux下查看机器是cpu是几核的 几个cpu more /proc/cpuinfo |grep "physical id"|uniq|wc -l
查看内存信息：more /proc/meminfo
查看操作系统信息：more /etc/issue
有关索引概念性的东西，请参考我的Blog：
Oracle 索引 详解：/database/201110/107271.html
二． 测试
自己也测试了下。测试环境：Oracle 11g R2, win7 64bit ,CPU T6670 2.2G 双核, 内存：4G。
1. 查看表的数据量：
SQL> select count(*) from custaddr;
COUNT(*)
----------
7230464
2. 查看现有索引：
SQL> select index_name,index_type from user_indexes where table_name='CUSTADDR';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
PK_CUSTADDR_TP_723 NORMAL
IX_CUSTADDR_ADDRABB_TP NORMAL
IX_CUSTADDR_TEAMID_TP NORMAL
IX_CUSTADDR_CUSTID_TP NORMAL
IX_CUSTADDR_COMPABB_TP NORMAL
IX_CUSTADDR_AREACODE NORMAL
IX_CUSTADDR_ADDR_TP NORMAL
已选择7行。
3. 删除索引：IX_CUSTADDR_CUSTID_TP
SQL> drop index IX_CUSTADDR_CUSTID_TP ;
索引已删除。
4. 默认方式创建索引：
SQL> SET timing on;
SQL> CREATE INDEX IX_CUSTADDR_CUSTID_TP ON CUSTADDR (CUSTID );
索引已创建。
已用时间: 00: 00: 48.37
单位：s
5. 用nologging 模式：
SQL> drop index IX_CUSTADDR_CUSTID_TP ;
索引已删除。
已用时间: 00: 00: 00.09
SQL> CREATE INDEX IX_CUSTADDR_CUSTID_TP ON CUSTADDR (CUSTID ) NOLOGGING;
索引已创建。
已用时间: 00: 00: 34.46
6. Nologging+ parallel 模式
SQL> drop index IX_CUSTADDR_CUSTID_TP ;
索引已删除。
已用时间: 00: 00: 00.17
SQL> CREATE INDEX IX_CUSTADDR_CUSTID_TP ON CUSTADDR (CUSTID ) NOLOGGING PARALLEL 2;
索引已创建。
已用时间: 00: 00: 52.56
SQL> drop index IX_CUSTADDR_CUSTID_TP ;
索引已删除。
已用时间: 00: 00: 00.07
SQL> CREATE INDEX IX_CUSTADDR_CUSTID_TP ON CUSTADDR (CUSTID ) NOLOGGING PARALLEL 4;
索引已创建。
已用时间: 00: 00: 53.44
看来在单CPU上，并行效果还不好.
7. Parallel 模式
SQL> drop index IX_CUSTADDR_CUSTID_TP ;
索引已删除。
已用时间: 00: 00: 00.02
SQL> CREATE INDEX IX_CUSTADDR_CUSTID_TP ON CUSTADDR (CUSTID ) PARALLEL 2;
索引已创建。
已用时间: 00: 00: 49.97
SQL> drop index IX_CUSTADDR_CUSTID_TP ;
索引已删除。
已用时间: 00: 00: 00.02
SQL> CREATE INDEX IX_CUSTADDR_CUSTID_TP ON CUSTADDR (CUSTID ) PARALLEL 4;
索引已创建。
已用时间: 00: 00: 50.25
从上面的测试数据可以看出，700万的数据，建索引，也在1分钟以内。 但是并行在单CPU上效果不明显，而且比光使用NOLOGGING还要慢，因为出现资源争用了，可能是CPU的争用，也可能是I/O的争用。
------------------------------------------------------------------------------
Blog：https://blog.csdn.net/tianlesoftware
-------------------
【DB.Oracle】create index注意n如果是大表建立索引，切记加上ONLINE参数
2014年12月23日 17:05:58
阅读数：6578
转自： http://wmcxy.iteye.com/blog/891224
这几天在做数据库的优化，有个2亿记录的表，发现需要添加一个联合索引，结果就采用普通的create index index_name
on tablename (entp_id,sell_date),结果悲剧了，把所有的DML语句都阻塞了，导致系统不能正常使用，还好是晚上10点，用户不是非常多，1个小时候，索引结束，阻塞解决；上网查了一下，如果加上 online参数后，就可以在线做索引，而不需要阻塞所有的DML语句，血的教训，拿出来与各位共勉，具体online与不加online区别如下：
1. DML操作对create index 的影响。 如果在create的时候，有其他的进程在对这个index 所对应的数据进行DML操作，create会受影响:
SQL> create table test (id number, name varchar2(20));
Table created.
--- 然后重新开一个session:
SQL> insert into test values (1,'lms');
1 row created.
<no commit>
SQL> create index t1 on test(id);
create index t1 on test(id)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
2. 加online这个参数，这个参数加上以后，除了create过程中index 保持online状态，Oracle还会在create index之前等待所有DML操作结束，然后得到DDL锁，开始create.
SQL> create index t1 on test(id) online;
<hold before commit>
<after commit>
SQL> commit;
Commit complete.
Index altered.
---- 如果不commit,上面的操作就会一直hold。
所以以后create索引和rebuild索引的时候最好加上online。
----------------------------------------
create index online 和create index 不同及注意点
分类： Linux操作系统2013-09-26 21:23:25
CREATE INDEX ONLINE 锁模式变化模拟
SESSION 139
SQL> insert into test123
2 select * from dba_objects;
50062 rows inserted
不提交
SESSION 148
SQL> create index test123_i on test123(owner) online;
回话148堵塞
SQL> select * from v$lock where sid in ('139','148') order by sid;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
0000000096669B90 0000000096669BB8 139 TM 53479 0 3 0 66 1
00000000966E6578 00000000966E65B0 139 TX 589843 343 6 0 66 0
0000000096669DD0 0000000096669DF8 148 TM 53481 0 4 0 40 0
0000000096669CB0 0000000096669CD8 148 TM 53479 0 2 4 42 0
00000000978E54F0 00000000978E5510 148 DL 53479 0 3 0 42 0
00000000978E53A0 00000000978E53C0 148 DL 53479 0 3 0 42 0
堵塞正是由于
0000000096669CB0 0000000096669CD8 148 TM 53479 0 2 4 42 0
可以看到已经以模式2也就是SS模式获得TM锁，但是期望以模式4获得TM锁也就是S模式，但是在OBJECTS 53479上被139会话以模式3也就是SX模式获得
因为SS和SX兼容所以可以获得，但是如果想获得S模式，S和SX是不兼容，所以堵塞
顺便提一下OBJECT_ID=53479 就是表TEST123，而对象53481是对象SYS_JOURNAL_53480，就是为了保证在建立索引的同时把可能的更改记录到所谓的日志表中
待索引建立完成后同步到日志中，这也是ONLINE建立索引所独有的。
此时我们COMMIT回话139
SQL> select * from v$lock where sid in ('139','148') order by sid;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
00000000978E53A0 00000000978E53C0 148 DL 53479 0 3 0 821 0
0000000096669DD0 0000000096669DF8 148 TM 53481 0 4 0 819 0
0000000096669CB0 0000000096669CD8 148 TM 53479 0 2 0 14 0
00000000978E54F0 00000000978E5510 148 DL 53479 0 3 0 821 0
一旦提交后期望的锁即可获得，而且获得后会降级为2也就是SS模式而不是S模式
然后我们又在会话139进行多次DML操作，看看CREATE INDEX ONLINE 是否堵塞 随后的DML
SQL> select * from v$lock where sid in ('139','148') order by sid;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
000000009666A250 000000009666A278 139 TM 53479 0 3 0 84 0
00000000966C0868 00000000966C08A0 139 TX 131088 311 6 0 80 0
0000000096669DD0 0000000096669DF8 148 TM 53481 0 4 0 562 0
00000000978E54F0 00000000978E5510 148 DL 53479 0 3 0 563 0
00000000978E53A0 00000000978E53C0 148 DL 53479 0 3 0 563 0
0000000096669CB0 0000000096669CD8 148 TM 53479 0 2 0 549 0
这里可以看到实际不会堵塞随后的DML操作，因为降级后只需要对TEST123获得SS模式即可，SS模式是SX模式是兼容的。
所以CREATE INDEX ONLINE会
1、如果在本表上有DML没有提交，那么CREATE INDEX ONLINE会等待其提交，因为初始的时候需要以S模式获得表上TM锁,S模式和SX模式不兼容
2、如果获得了表上S模式锁后，也就是进行创建过程中，实际对表的TM S锁已经降级为SS，这个时候就不会堵塞随后的DML了。这也是为什么
CREATE INDEX ONLINE优于CREATE INDEX的地方，他不会堵塞随后的DML，因为TM锁是SS模式而不是S模式。
但是还是要注意第一点，所以为了保险还是关闭应用建立索引吧，特别是大表，CREATE INDEX ONLINE也不一定保险。
CREATE INDEX 锁模式变化模拟
其实CREATE INDEX 没什么好模拟的，
如果你还有DML操作在表上，那么一定有TX模式的TM锁，建立索引会报错如下
SQL> create index test123_i
2 on test123(owner);
create index test123_i
on test123(owner)
ORA-00054: resource busy and acquire with NOWAIT specified
当然如果可以建立索引的话你会看到如下
回话148建立索引，查看其锁TM为模式4及模式S
SQL> select * from v$lock where sid in ('139','148') order by sid;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
00000000978E53A0 00000000978E53C0 148 DL 53479 0 3 0 6 0
00000000978E54F0 00000000978E5510 148 DL 53479 0 3 0 6 0
00000000966F2BA8 00000000966F2BE0 148 TX 458790 367 6 0 6 0
0000000096669CB0 0000000096669CD8 148 TM 18 0 3 0 6 0
0000000096669B90 0000000096669BB8 148 TM 53479 0 4 0 6 0
回话139进行操作
delete test123;
查看锁模式如下
SQL> select * from v$lock where sid in ('139','148') order by sid;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
0000000096669DD0 0000000096669DF8 139 TM 53479 0 0 3 15 0
00000000978E54F0 00000000978E5510 148 DL 53479 0 3 0 31 0
0000000096669B90 0000000096669BB8 148 TM 53479 0 4 0 31 1
00000000966F2BA8 00000000966F2BE0 148 TX 458790 367 6 0 31 0
0000000096668868 00000000966688C8 148 TS 4 18509883 6 0 17 0
00000000978E53A0 00000000978E53C0 148 DL 53479 0 3 0 31 0
0000000096669CB0 0000000096669CD8 148 TM 18 0 3 0 31 0
可以看到回话139想以模式3也就是SX模式获得TM锁，但是因为CREATE INDEX 的TM模式是4也就是S模式，是不共享的，所以不能获得，只能堵塞
等待create index 完成，所以CREATE INDEX一定不能再没有确定这个表没有DML操作的情况下使用，除非你确定没有DML操作在这个表上
兼容矩阵
held/get null ss sx s ssx x
null 1 1 1 1 1 1
ss 1 1 1 1 1
sx 1 1 1
s 1 1 1
ssx 1 1
x 1