SQL> explain plan for alter index t_index3 rebuild;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2701019691
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 1000 | 2000 | 1 (0)| 00:00:
01 |
| 1 | INDEX BUILD NON UNIQUE | T_INDEX3 | | | |
|
| 2 | BITMAP COMPACTION | | | | |
|
| 3 | SORT CREATE INDEX | | 1000 | 2000 | |
|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 4 | INDEX FAST FULL SCAN| T_INDEX3
01 |
--------------------------------------------------------------------------------
----
已选择11行。
可以看到是对索引进行扫描。
SQL> explain plan for alter index t_index3 rebuild online;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3732900664
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 1000 | 2000 | 1 (0)| 00:00:
01 |
| 1 | INDEX BUILD NON UNIQUE | T_INDEX3 | | | |
|
| 2 | BITMAP COMPACTION | | | | |
|
| 3 | SORT CREATE INDEX | | 1000 | 2000 | |
|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 4 | BITMAP CONSTRUCTION | | | | |
|
| 5 | TABLE ACCESS FULL
01 |
--------------------------------------------------------------------------------
----
已选择12行。
可以看到是对表进行扫描。
结论:
主要区别:是否阻塞DML操作和扫描数据的不同
在OLTP系统中,rebuild online比较合适。
alter index rebuild和alter index rebuild online的区别
最新推荐文章于 2021-04-03 15:37:51 发布