alter index index_name rebulid 與rebuild online nologging的區別

最新推荐文章于 2021-11-29 15:02:45 发布

csdn7007

最新推荐文章于 2021-11-29 15:02:45 发布

阅读量873

点赞数

文章标签：数据库嵌入式

1.扫描方式不同，rebuild 扫描index,rebuild online扫描表，但都需要排序
2.rebuild 会阻塞dml语句而rebuild online不会
3.rebuild online时系统会产生一个SYS_JOURNAL_xxx的IOT类型的系统临时日志表，所有rebuild online时索引的变化都记录在这个表中，
当新的索引创建完成后，把这个表的记录维护到新的索引中去，然后drop掉旧的索引，rebuild online就完成了。

rebuild 走的是INDEX FAST FULL SCAN是要排序的

SYS AS SYSDBA>explain plan for alter index test_ind rebuild;

Explained.

Elapsed: 00:00:00.01
SYS AS SYSDBA> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
Plan hash value: 119353490

-----------------------------------------------------------------------------------
| Id | Operation              | Name     | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | ALTER INDEX STATEMENT |          |     4 |    12 |     2   (0)| 00:00:01 |
|   1 | INDEX BUILD NON UNIQUE| TEST_IND |       |       |            |          |
|   2 |   SORT CREATE INDEX    |          |     4 |    12 |            |          |
|   3 |    INDEX FAST FULL SCAN| TEST_IND |       |       |            |          |
-----------------------------------------------------------------------------------

10 rows selected.

Elapsed: 00:00:00.04

SYS AS SYSDBA>explain plan for alter index test_ind rebuild online

Explained.

Elapsed: 00:00:00.01
SYS AS SYSDBA> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2966097880

-----------------------------------------------------------------------------------
| Id | Operation              | Name     | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | ALTER INDEX STATEMENT |          |     4 |    12 |     2   (0)| 00:00:01 |
|   1 | INDEX BUILD NON UNIQUE| TEST_IND |       |       |            |          |
|   2 |   SORT CREATE INDEX    |          |     4 |    12 |            |          |
|   3 |    TABLE ACCESS FULL   | TEST     |     4 |    12 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

10 rows selected.

Elapsed: 00:00:00.04

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14876437/viewspace-555615/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14876437/viewspace-555615/