重建索引index rebuild online vs offline vs index coalesce vs index shrik space
重建索引：ALTER INDEX..REBUILD ONLINE vs ALTER INDEX..REBUILD:
http://blog.csdn.net/pan_tian/article/details/46563897
深入理解重建索引(原創):
http://czmmiao.iteye.com/blog/1481957
alter index coalesce和alter index rebuild的區別:
http://blog.csdn.net/techchan/article/details/6693275
Alter index coalesce VS shrink space：
什麼時候需要重建索引
1、 刪除的空間沒有重用，導致 索引出現碎片
2、 刪除大量的表資料後，空間沒有重用，導致 索引"虛高"
3、索引的 clustering_facto 和表不一致
也有人認為當索引樹高度超過4的時候需要進行重建，但是如果表數量級較大，自然就不會有較高的樹，而且重建不會改變索引樹高度，除非是由於大量引起的索引樹“虛高”，重建才會改善效能，當然這又回到了索引碎片的問題上了。
關於索引是否需要重建，Oracle有這麼一句話:
Generally speaking, the need to rebuild b-tree indexes is very rare, basically because a b-tree index is largely self-managed or self-balanced.
另外找到了一篇《When should one perform a rebuild?》分析的比較好的文章
Firstly, if the index value were to have monotonically increasing values
then any deleted space could be a problem as this space may not be reused
(making feature 3 above redundant). However, if sufficient entries are
deleted resulting in index nodes being fully emptied (say via a bulk delete)
then feature 4 would kick in and the deleted space could be reused. The
question now becomes one of *when* would the equivalent amount of index
entries be reinserted from the time of the deletions, as index scans (in all
it's manifestations) would be impacted during this interim period. So
monotonically increasing values *and* sparse deletions would present one
case for an index rebuild. These types of indexes can be identified as
having predominately 90-10 splits rather than the usual 50-50 split.
Another case would be an index that has deletions without subsequent inserts
or inserts within an acceptable period of time. Such a case would result in
wasted space that can't be effectively reused as there's not the sufficient
insert activity to reclaim the space. However, in this scenario, it's really
the *table* itself rather than the indexes directly that should be rebuilt.
Because such "shrinkage" results in both the table and associated indexes
being fragmented with HWMs that need resetting (to prevent performance
issues with Full Table Scans and all types of Index Scans). Yes the index
needs rebuilding but only as a result of the dependent table being rebuilt
as well.
ALTER INDEX..REBUILD ONLINE vs ALTER INDEX..REBUILD
alter index rebuild online實質上是掃描表而不是掃描現有的索引塊來實現索引的重建.
alter index rebuild 只掃描現有的索引塊來實現索引的重建。
rebuild index online在執行期間不會阻塞DML操作，但在開始和結束階段，需要請求模式為4的TM鎖。因此，如果在rebuild index online開始前或結束時，有其它長時間的事物在執行，很有可能就造成大量的鎖等待。也就是說在執行前仍會產生阻塞, 應該避免排他鎖.
而rebuild index在執行期間會阻塞DML操作, 但速度較快.
Online Index Rebuild Features:
+ ALTER INDEX REBUILD ONLINE;
+ DMLs are allowed on the base table
+ It is comparatively Slow
+ Base table is referred for the new index
+ Base table is locked in shared mode and DDLs are not possible
+ Intermediate table stores the data changes in the base table, during the index rebuild to update the new index later
Offline Index Rebuild Features:
+ ALTER INDEX REBUILD; (Default)
+ Does not refer the base table and the base table is exclusively locked
+ New index is created from the old index
+ No DML and DDL possible on the base table
+ Comparatively faster
兩者重建索引時的掃描方式不同，rebuild用的是“INDEX FAST FULL SCAN”，rebuild online用的是“TABLE ACCESS FULL”; 即rebuild index是掃描索引塊,而rebuild index online是掃描全表的資料塊.
實驗一：
SQL> create table t1 as select * From emp;
Table created.
SQL> CREATE INDEX i_empno on T1 (empno);
Index created.
SQL> CREATE INDEX i_deptno on T1 (deptno);
Index created.
--offline重建索引，檢視執行計劃
SQL> explain plan for alter index i_empno rebuild;
Explained.
SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 1909342220
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 327 | 4251 | 3 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| I_EMPNO | | | | |
| 2 | SORT CREATE INDEX | | 327 | 4251 | | |
| 3 | INDEX FAST FULL SCAN| I_EMPNO | | | | |
----------------------------------------------------------------------------------
10 rows selected.
--online重建索引，檢視執行計劃
SQL> explain plan for alter index i_empno rebuild online;
Explained.
SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------
Plan hash value: 1499455000
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 327 | 4251 | 3 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| I_EMPNO | | | | |
| 2 | SORT CREATE INDEX | | 327 | 4251 | | |
| 3 | TABLE ACCESS FULL | T1 | 327 | 4251 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
10 rows selected.
結論：alter index rebuild online實質上是掃描表而不是掃描現有的索引塊來實現索引的重建，速度慢.
alter index rebuild 只掃描現有的索引塊來實現索引的重建，速度快。
實驗二：
SQL> create table YOUYUS as select rownum t1,rpad('A',20,'B') t2 from dual connect by level<=999999;
Table created.
Elapsed: 00:00:01.03
SQL> create index ind_youyus on youyus(t1,t2) nologging;
Index created.
Elapsed: 00:00:04.13
--分析索引
SQL> analyze index IND_YOUYUS validate structure;
Index analyzed.
Elapsed: 00:00:00.41
SQL> set linesize 200;
SQL> set linesize 200;
SQL> select height,
2 blocks,
3 lf_blks,
4 lf_rows_len,
5 lf_blk_len,
6 br_blks,
7 br_rows,
8 br_rows_len,
9 br_blk_len,
10 btree_space,
11 used_space,
12 pct_used
13 from index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_BLKS BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
3 5376 5154 36979767 7996 9 5153 61784 8028 41283636 37041551 90
Elapsed: 00:00:00.34
/* 可以看到IND_YOUYUS索引的基本結構，在初始狀態下其block總數為5376，其中頁塊共5154 */
--刪除三分之一資料：
SQL> delete YOUYUS where mod(t1,3)=1;
333333 rows deleted.
Elapsed: 00:00:10.31
SQL> commit;
Commit complete.
Elapsed: 00:00:00.02
--再次查詢redo生成量：
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where vs.statistic# = ms.statistic#
4 and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 36422640
redo size 1471998664
Elapsed: 00:00:00.05
--使用coalesce字句合併索引：
SQL> alter index ind_youyus coalesce;
Index altered.
Elapsed: 00:00:03.72
--再次查詢redo生成量：
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where vs.statistic# = ms.statistic#
4 and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 36426180
redo size 1542936592
經過前後對比coalesce 操作產生了大約70MB的redo資料。
Elapsed: 00:00:00.00
--再次分析索引結構；
SQL> analyze index IND_YOUYUS validate structure;
Index analyzed.
Elapsed: 00:00:00.17
SQL> select height,
2 blocks,
3 lf_blks,
4 lf_rows_len,
5 lf_blk_len,
6 br_blks,
7 br_rows,
8 br_rows_len,
9 br_blk_len,
10 btree_space,
11 used_space,
12 pct_used
13 from index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_BLKS BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
3 5376 3439 24653178 7996 9 3438 41188 8028 27570496 24694366 90
Elapsed: 00:00:00.02
/* 可以看到執行coalesce(合併)操作後頁塊數量下降到3439，而branch枝塊和root根塊的結構是不會變化的，同時coalesc命令並不釋放索引上的多餘空間，但索引結構實際佔用的空間BTREE_SPACE下降到了27570496 bytes */
/* 清理測試現場 */
SQL> drop table YOUYUS;
Table dropped.
Elapsed: 00:00:01.42
SQL>
SQL> create table YOUYUS as select rownum t1,rpad('A',20,'B') t2 from dual connect by level<=999999;
Table created.
Elapsed: 00:00:01.04
SQL>
SQL> create index ind_youyus on youyus(t1,t2) nologging;
Index created.
Elapsed: 00:00:03.68
--再次刪除資料：1/3
SQL> delete YOUYUS where mod(t1,3)=1;
333333 rows deleted.
Elapsed: 00:00:14.31
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
--查詢目前redo生成量：
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where vs.statistic# = ms.statistic#
4 and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 36445880
redo size 1711003916
Elapsed: 00:00:00.01
--使用shrink space子句回收索引：
SQL> alter index ind_youyus shrink space;
Index altered.
Elapsed: 00:00:05.30
--再次查詢目前redo生成量：
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where vs.statistic# = ms.statistic#
4 and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 36452200
redo size 1802409928
Elapsed: 00:00:00.01
前後比對，redo生成量為90MB左右，多出coalesce時的28%左右。。。。。
--再次分析索引：
SQL> analyze index IND_YOUYUS validate structure;
Index analyzed.
Elapsed: 00:00:00.17
SQL>
SQL> select height,
2 blocks,
3 lf_blks,
4 lf_rows_len,
5 lf_blk_len,
6 br_blks,
7 br_rows,
8 br_rows_len,
9 br_blk_len,
10 btree_space,
11 used_space,
12 pct_used
13 from index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_BLKS BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
3 3520 3439 24653178 7996 9 3438 41188 8028 27570496 24694366 90
Elapsed: 00:00:00.01
/* 索引結構與coalesce命令維護後相同，但shrink space操作釋放了索引上的空閒空間 */
/* 清理測試現場 */
SQL> drop table YOUYUS;
Table dropped.
Elapsed: 00:00:00.51
SQL>
SQL> create table YOUYUS as select rownum t1,rpad('A',20,'B') t2 from dual connect by level<=999999;
Table created.
Elapsed: 00:00:00.84
SQL>
SQL>
SQL> create index ind_youyus on youyus(t1,t2) nologging;
Index created.
Elapsed: 00:00:03.60
--刪除資料：
SQL> delete YOUYUS where mod(t1,3)=1;
333333 rows deleted.
Elapsed: 00:00:14.61
SQL>
SQL> commit;
Commit complete.
Elapsed: 00:00:00.07
--查詢目前redo生成量：
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where vs.statistic# = ms.statistic#
4 and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 36468913
redo size 1970476820
Elapsed: 00:00:00.01
--使用shrink space compact子句回收索引：
SQL> alter index ind_youyus shrink space compact;
Index altered.
Elapsed: 00:00:04.95
--再次查詢目前redo生成量：
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where vs.statistic# = ms.statistic#
4 and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 36474731
redo size 2061844832
Elapsed: 00:00:00.00
前後比對發現，redo生成量為90mb左右，與shrink space子句相同
--再次分析索引：
SQL> analyze index IND_YOUYUS validate structure;
Index analyzed.
Elapsed: 00:00:00.16
SQL>
SQL> select height,
2 blocks,
3 lf_blks,
4 lf_rows_len,
5 lf_blk_len,
6 br_blks,
7 br_rows,
8 br_rows_len,
9 br_blk_len,
10 btree_space,
11 used_space,
12 pct_used
13 from index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_BLKS BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
3 5376 3439 24653178 7996 9 3438 41188 8028 27570496 24694366 90
Elapsed: 00:00:00.01
/* shrink space compact 起到了和coalesce完全相同的作用，但其產生的redo仍要多於coalesce於28%，與shrink space相同 */；
總結：
coalesce與shrink space命令對比重建索引(rebuild index)有一個顯著的優點:不會導致索引降級。從以上測試可以看到coalesce與shrink space compact功能完全相同；在OLTP環境中，大多數情況下我們並不希望回收索引上的空閒空間，那麼coalesce或者shrink space compact(not shrink space)可以成為我們很好的選擇，雖然實際操作過程中2者消耗的資源有不少差別。並不是說coalesce就一定會消耗更少的資源，這需要在您的實際環境中具體測試，合適的才是最好的！
http://blog.csdn.net/pan_tian/article/details/46563897
深入理解重建索引(原創):
http://czmmiao.iteye.com/blog/1481957
alter index coalesce和alter index rebuild的區別:
http://blog.csdn.net/techchan/article/details/6693275
Alter index coalesce VS shrink space：
什麼時候需要重建索引
1、 刪除的空間沒有重用，導致 索引出現碎片
2、 刪除大量的表資料後，空間沒有重用，導致 索引"虛高"
3、索引的 clustering_facto 和表不一致
也有人認為當索引樹高度超過4的時候需要進行重建，但是如果表數量級較大，自然就不會有較高的樹，而且重建不會改變索引樹高度，除非是由於大量引起的索引樹“虛高”，重建才會改善效能，當然這又回到了索引碎片的問題上了。
關於索引是否需要重建，Oracle有這麼一句話:
Generally speaking, the need to rebuild b-tree indexes is very rare, basically because a b-tree index is largely self-managed or self-balanced.
另外找到了一篇《When should one perform a rebuild?》分析的比較好的文章
Firstly, if the index value were to have monotonically increasing values
then any deleted space could be a problem as this space may not be reused
(making feature 3 above redundant). However, if sufficient entries are
deleted resulting in index nodes being fully emptied (say via a bulk delete)
then feature 4 would kick in and the deleted space could be reused. The
question now becomes one of *when* would the equivalent amount of index
entries be reinserted from the time of the deletions, as index scans (in all
it's manifestations) would be impacted during this interim period. So
monotonically increasing values *and* sparse deletions would present one
case for an index rebuild. These types of indexes can be identified as
having predominately 90-10 splits rather than the usual 50-50 split.
Another case would be an index that has deletions without subsequent inserts
or inserts within an acceptable period of time. Such a case would result in
wasted space that can't be effectively reused as there's not the sufficient
insert activity to reclaim the space. However, in this scenario, it's really
the *table* itself rather than the indexes directly that should be rebuilt.
Because such "shrinkage" results in both the table and associated indexes
being fragmented with HWMs that need resetting (to prevent performance
issues with Full Table Scans and all types of Index Scans). Yes the index
needs rebuilding but only as a result of the dependent table being rebuilt
as well.
ALTER INDEX..REBUILD ONLINE vs ALTER INDEX..REBUILD
alter index rebuild online實質上是掃描表而不是掃描現有的索引塊來實現索引的重建.
alter index rebuild 只掃描現有的索引塊來實現索引的重建。
rebuild index online在執行期間不會阻塞DML操作，但在開始和結束階段，需要請求模式為4的TM鎖。因此，如果在rebuild index online開始前或結束時，有其它長時間的事物在執行，很有可能就造成大量的鎖等待。也就是說在執行前仍會產生阻塞, 應該避免排他鎖.
而rebuild index在執行期間會阻塞DML操作, 但速度較快.
Online Index Rebuild Features:
+ ALTER INDEX REBUILD ONLINE;
+ DMLs are allowed on the base table
+ It is comparatively Slow
+ Base table is referred for the new index
+ Base table is locked in shared mode and DDLs are not possible
+ Intermediate table stores the data changes in the base table, during the index rebuild to update the new index later
Offline Index Rebuild Features:
+ ALTER INDEX REBUILD; (Default)
+ Does not refer the base table and the base table is exclusively locked
+ New index is created from the old index
+ No DML and DDL possible on the base table
+ Comparatively faster
兩者重建索引時的掃描方式不同，rebuild用的是“INDEX FAST FULL SCAN”，rebuild online用的是“TABLE ACCESS FULL”; 即rebuild index是掃描索引塊,而rebuild index online是掃描全表的資料塊.
實驗一：
SQL> create table t1 as select * From emp;
Table created.
SQL> CREATE INDEX i_empno on T1 (empno);
Index created.
SQL> CREATE INDEX i_deptno on T1 (deptno);
Index created.
--offline重建索引，檢視執行計劃
SQL> explain plan for alter index i_empno rebuild;
Explained.
SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 1909342220
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 327 | 4251 | 3 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| I_EMPNO | | | | |
| 2 | SORT CREATE INDEX | | 327 | 4251 | | |
| 3 | INDEX FAST FULL SCAN| I_EMPNO | | | | |
----------------------------------------------------------------------------------
10 rows selected.
--online重建索引，檢視執行計劃
SQL> explain plan for alter index i_empno rebuild online;
Explained.
SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------
Plan hash value: 1499455000
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 327 | 4251 | 3 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| I_EMPNO | | | | |
| 2 | SORT CREATE INDEX | | 327 | 4251 | | |
| 3 | TABLE ACCESS FULL | T1 | 327 | 4251 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
10 rows selected.
結論：alter index rebuild online實質上是掃描表而不是掃描現有的索引塊來實現索引的重建，速度慢.
alter index rebuild 只掃描現有的索引塊來實現索引的重建，速度快。
實驗二：
SQL> create table YOUYUS as select rownum t1,rpad('A',20,'B') t2 from dual connect by level<=999999;
Table created.
Elapsed: 00:00:01.03
SQL> create index ind_youyus on youyus(t1,t2) nologging;
Index created.
Elapsed: 00:00:04.13
--分析索引
SQL> analyze index IND_YOUYUS validate structure;
Index analyzed.
Elapsed: 00:00:00.41
SQL> set linesize 200;
SQL> set linesize 200;
SQL> select height,
2 blocks,
3 lf_blks,
4 lf_rows_len,
5 lf_blk_len,
6 br_blks,
7 br_rows,
8 br_rows_len,
9 br_blk_len,
10 btree_space,
11 used_space,
12 pct_used
13 from index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_BLKS BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
3 5376 5154 36979767 7996 9 5153 61784 8028 41283636 37041551 90
Elapsed: 00:00:00.34
/* 可以看到IND_YOUYUS索引的基本結構，在初始狀態下其block總數為5376，其中頁塊共5154 */
--刪除三分之一資料：
SQL> delete YOUYUS where mod(t1,3)=1;
333333 rows deleted.
Elapsed: 00:00:10.31
SQL> commit;
Commit complete.
Elapsed: 00:00:00.02
--再次查詢redo生成量：
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where vs.statistic# = ms.statistic#
4 and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 36422640
redo size 1471998664
Elapsed: 00:00:00.05
--使用coalesce字句合併索引：
SQL> alter index ind_youyus coalesce;
Index altered.
Elapsed: 00:00:03.72
--再次查詢redo生成量：
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where vs.statistic# = ms.statistic#
4 and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 36426180
redo size 1542936592
經過前後對比coalesce 操作產生了大約70MB的redo資料。
Elapsed: 00:00:00.00
--再次分析索引結構；
SQL> analyze index IND_YOUYUS validate structure;
Index analyzed.
Elapsed: 00:00:00.17
SQL> select height,
2 blocks,
3 lf_blks,
4 lf_rows_len,
5 lf_blk_len,
6 br_blks,
7 br_rows,
8 br_rows_len,
9 br_blk_len,
10 btree_space,
11 used_space,
12 pct_used
13 from index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_BLKS BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
3 5376 3439 24653178 7996 9 3438 41188 8028 27570496 24694366 90
Elapsed: 00:00:00.02
/* 可以看到執行coalesce(合併)操作後頁塊數量下降到3439，而branch枝塊和root根塊的結構是不會變化的，同時coalesc命令並不釋放索引上的多餘空間，但索引結構實際佔用的空間BTREE_SPACE下降到了27570496 bytes */
/* 清理測試現場 */
SQL> drop table YOUYUS;
Table dropped.
Elapsed: 00:00:01.42
SQL>
SQL> create table YOUYUS as select rownum t1,rpad('A',20,'B') t2 from dual connect by level<=999999;
Table created.
Elapsed: 00:00:01.04
SQL>
SQL> create index ind_youyus on youyus(t1,t2) nologging;
Index created.
Elapsed: 00:00:03.68
--再次刪除資料：1/3
SQL> delete YOUYUS where mod(t1,3)=1;
333333 rows deleted.
Elapsed: 00:00:14.31
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
--查詢目前redo生成量：
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where vs.statistic# = ms.statistic#
4 and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 36445880
redo size 1711003916
Elapsed: 00:00:00.01
--使用shrink space子句回收索引：
SQL> alter index ind_youyus shrink space;
Index altered.
Elapsed: 00:00:05.30
--再次查詢目前redo生成量：
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where vs.statistic# = ms.statistic#
4 and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 36452200
redo size 1802409928
Elapsed: 00:00:00.01
前後比對，redo生成量為90MB左右，多出coalesce時的28%左右。。。。。
--再次分析索引：
SQL> analyze index IND_YOUYUS validate structure;
Index analyzed.
Elapsed: 00:00:00.17
SQL>
SQL> select height,
2 blocks,
3 lf_blks,
4 lf_rows_len,
5 lf_blk_len,
6 br_blks,
7 br_rows,
8 br_rows_len,
9 br_blk_len,
10 btree_space,
11 used_space,
12 pct_used
13 from index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_BLKS BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
3 3520 3439 24653178 7996 9 3438 41188 8028 27570496 24694366 90
Elapsed: 00:00:00.01
/* 索引結構與coalesce命令維護後相同，但shrink space操作釋放了索引上的空閒空間 */
/* 清理測試現場 */
SQL> drop table YOUYUS;
Table dropped.
Elapsed: 00:00:00.51
SQL>
SQL> create table YOUYUS as select rownum t1,rpad('A',20,'B') t2 from dual connect by level<=999999;
Table created.
Elapsed: 00:00:00.84
SQL>
SQL>
SQL> create index ind_youyus on youyus(t1,t2) nologging;
Index created.
Elapsed: 00:00:03.60
--刪除資料：
SQL> delete YOUYUS where mod(t1,3)=1;
333333 rows deleted.
Elapsed: 00:00:14.61
SQL>
SQL> commit;
Commit complete.
Elapsed: 00:00:00.07
--查詢目前redo生成量：
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where vs.statistic# = ms.statistic#
4 and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 36468913
redo size 1970476820
Elapsed: 00:00:00.01
--使用shrink space compact子句回收索引：
SQL> alter index ind_youyus shrink space compact;
Index altered.
Elapsed: 00:00:04.95
--再次查詢目前redo生成量：
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where vs.statistic# = ms.statistic#
4 and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 36474731
redo size 2061844832
Elapsed: 00:00:00.00
前後比對發現，redo生成量為90mb左右，與shrink space子句相同
--再次分析索引：
SQL> analyze index IND_YOUYUS validate structure;
Index analyzed.
Elapsed: 00:00:00.16
SQL>
SQL> select height,
2 blocks,
3 lf_blks,
4 lf_rows_len,
5 lf_blk_len,
6 br_blks,
7 br_rows,
8 br_rows_len,
9 br_blk_len,
10 btree_space,
11 used_space,
12 pct_used
13 from index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_BLKS BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
3 5376 3439 24653178 7996 9 3438 41188 8028 27570496 24694366 90
Elapsed: 00:00:00.01
/* shrink space compact 起到了和coalesce完全相同的作用，但其產生的redo仍要多於coalesce於28%，與shrink space相同 */；
總結：
coalesce與shrink space命令對比重建索引(rebuild index)有一個顯著的優點:不會導致索引降級。從以上測試可以看到coalesce與shrink space compact功能完全相同；在OLTP環境中，大多數情況下我們並不希望回收索引上的空閒空間，那麼coalesce或者shrink space compact(not shrink space)可以成為我們很好的選擇，雖然實際操作過程中2者消耗的資源有不少差別。並不是說coalesce就一定會消耗更少的資源，這需要在您的實際環境中具體測試，合適的才是最好的！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2148672/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 關於rebuild index online 及drop index後重建問題RebuildIndex
- Index Full Scan vs Index Fast Full ScanIndexAST
- 聊聊索引Index Rebuild和Rebuild Online（下）索引IndexRebuild
- 聊聊索引Index Rebuild和Rebuild Online（上）索引IndexRebuild
- create index online 與rebuild index onlineIndexRebuild
- create index online 與rebuild index onlineIndexRebuild
- Index Online RebuildIndexRebuild
- Sparse Indexes vs unique indexIndex
- alter index rebuild 與 rebuild onlineIndexRebuild
- 【SQL優化】LIKE vs INDEXSQL優化Index
- 【羅玄】從鎖的角度看rebuild index online和rebuild indexRebuildIndex
- create index , rebuild index troubleshooting 索引故障解決IndexRebuild索引
- alter index ind1 rebuild 和alter index ind1 rebuild onlineIndexRebuild
- rebuild indexRebuildIndex
- index rebuildIndexRebuild
- index rebuild online的問題IndexRebuild
- Oracle vs PostgreSQL Develop（31） - Index Only ScanOracleSQLdevIndex
- PostgreSQL DBA(119) - pgAdmin(LIMIT：Index Scan vs Bitmap Index Scan)SQLMITIndex
- alter index rebuild和rebuild online的區別IndexRebuild
- alter index rebuild與index_statsIndexRebuild
- rebuild index online和create index online及沒有online的區別RebuildIndex
- rebuild index 排序RebuildIndex排序
- sybase rebuild indexRebuildIndex
- PostgreSQL DBA(177) - Serializability Isolation（Index vs NonIndex）SQLIndex
- Oracle vs PostgreSQL Develop（30） - Index&Case whenOracleSQLdevIndex
- oracle10g_alter index rebuild_online_重構索引OracleIndexRebuild索引
- rebuild index 和 recreate index (重新建立index) 的區別RebuildIndex
- alter index rebuild online引發的血案IndexRebuild
- 測試index online rebuild故障記錄IndexRebuild
- alter index ... rebuild online的機制(zt)IndexRebuild
- “rebuild index online hang住" 問題解析RebuildIndex
- Index rebuild --case 1IndexRebuild
- "Alter index rebuild online parallel n"會修改索引的degree屬性IndexRebuildParallel索引
- 加快建立索引（create / rebuild index） 的幾點索引RebuildIndex
- rebuild index online的鎖機制淺析RebuildIndex
- Online rebuild index遭遇ORA-08104RebuildIndex
- index索引Index索引
- create index/create index online區別Index