1.dml对create index 的影响。
如果在create 的时候,有其他进程在对这个index所对应的数据进行dml操作,create会受影响。
session 1:
SQL> create table t(id int);
如果在create 的时候,有其他进程在对这个index所对应的数据进行dml操作,create会受影响。
session 1:
SQL> create table t(id int);
Table created.
SQL> insert into t values(1);
1 row created.
session 2:
SQL> create index idx_t on t(id);
create index idx_t on t(id)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> create index idx_t on t(id) online;
Index created.
如果不commit则会一直hold住。
在create ,rebuild索引的时候最好加上online。
2.
alter session enable resumable;
在对大表执行创建索引的时候,执行了很长时间后由于表空间不足导致索引创建失败,是件很悲惨的事情,又的重新建立。可以在创建大索引的时候执行alter session enable resumable;这样在空间用完后会等待,直到有了空间后在继续执行。
3.rebuild 和rebuild online差别
SQL> create index idx_t on t(id);
Index created.
SQL> explain plan for alter index idx_t rebuild;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 32582980
--------------------------------------------------------------------------------
Plan hash value: 32582980
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 82 | 1066 | 2 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| IDX_T | | | | |
| 2 | SORT CREATE INDEX | | 82 | 1066 | | |
| 3 | INDEX FAST FULL SCAN| IDX_T | | | | |
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 82 | 1066 | 2 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| IDX_T | | | | |
| 2 | SORT CREATE INDEX | | 82 | 1066 | | |
| 3 | INDEX FAST FULL SCAN| IDX_T | | | | |
--------------------------------------------------------------------------------
SQL> explain plan for alter index idx_t rebuild online;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 823570914
--------------------------------------------------------------------------------
Plan hash value: 823570914
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 82 | 1066 | 2 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| IDX_T | | | | |
| 2 | SORT CREATE INDEX | | 82 | 1066 | | |
| 3 | TABLE ACCESS FULL | T | 82 | 1066 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 82 | 1066 | 2 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| IDX_T | | | | |
| 2 | SORT CREATE INDEX | | 82 | 1066 | | |
| 3 | TABLE ACCESS FULL | T | 82 | 1066 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
10 rows selected.
从上面例子可以很清楚的看到 index rebuild online实质上是扫描表而不是扫描现有的索引块来实现索引的重建,index rebuild 只扫描现有的索引块来实现索引的重建。
除了扫描方式不同外,rebuild 会阻塞dml语句而rebuild online则不会(ddl语句除外)。
rebuild online时系统会产生一个SYS_JOURNAL_xxx的IOT类型的系统临时日志表,所有rebuild online时索引的变化都记录在这个表中(但要使用两倍于传统方法的空间),当新的索引创建完成后,把这个表的记录维护到新的索引中去,然后drop掉旧的索引,rebuild online就完成了。
注意并行处理,DDL,位图索引不能使用ONLINE。
在rebuild online 时会报错:
ORA-08120: Need to create SYS.IND_ONLINE$ table in order to (re)build index
Cause: Alter index Build/Rebuild online require existing of SYS.IND_ONLINE$ table.
Action: User/DBA needs to create sys.ind_online$ before alter the index /rdbms/admin/catcio.sql contains script. to create ind_online$.
Cause: Alter index Build/Rebuild online require existing of SYS.IND_ONLINE$ table.
Action: User/DBA needs to create sys.ind_online$ before alter the index /rdbms/admin/catcio.sql contains script. to create ind_online$.
解决方法就是运行catcio.sql 创建SYS.IND_ONLINE$表
4并行
在创建大索引的时候加上并行参数parallel
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22779291/viewspace-701619/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22779291/viewspace-701619/