重建索引index rebuild online vs offline vs index coalesce vs index shrik space

發(fā)布時(shí)間：2020-08-11 15:30:19 來源：ITPUB博客閱讀：176 作者：不一樣的天空w 欄目：關(guān)系型數(shù)據(jù)庫(kù)

重建索引：ALTER INDEX..REBUILD ONLINE vs ALTER INDEX..REBUILD:
http://blog.csdn.net/pan_tian/article/details/46563897

深入理解重建索引(原創(chuàng)):
http://czmmiao.iteye.com/blog/1481957

alter index coalesce和alter index rebuild的區(qū)別:
http://blog.csdn.net/techchan/article/details/6693275

Alter index coalesce VS shrink space：
http://www.askmaclean.com/archives/alter-index-coalesce-vs-shrink-space.html

什么時(shí)候需要重建索引
1、刪除的空間沒有重用，導(dǎo)致索引出現(xiàn)碎片
2、刪除大量的表數(shù)據(jù)后，空間沒有重用，導(dǎo)致索引"虛高"
3、索引的 clustering_facto 和表不一致
也有人認(rèn)為當(dāng)索引樹高度超過4的時(shí)候需要進(jìn)行重建，但是如果表數(shù)量級(jí)較大，自然就不會(huì)有較高的樹，而且重建不會(huì)改變索引樹高度，除非是由于大量引起的索引樹“虛高”，重建才會(huì)改善性能，當(dāng)然這又回到了索引碎片的問題上了。

關(guān)于索引是否需要重建，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實(shí)質(zhì)上是掃描表而不是掃描現(xiàn)有的索引塊來實(shí)現(xiàn)索引的重建.
alter index rebuild 只掃描現(xiàn)有的索引塊來實(shí)現(xiàn)索引的重建。

rebuild index online在執(zhí)行期間不會(huì)阻塞DML操作，但在開始和結(jié)束階段，需要請(qǐng)求模式為4的TM鎖。因此，如果在rebuild index online開始前或結(jié)束時(shí)，有其它長(zhǎng)時(shí)間的事物在運(yùn)行，很有可能就造成大量的鎖等待。也就是說在執(zhí)行前仍會(huì)產(chǎn)生阻塞, 應(yīng)該避免排他鎖.
而rebuild index在執(zhí)行期間會(huì)阻塞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

兩者重建索引時(shí)的掃描方式不同，rebuild用的是“INDEX FAST FULL SCAN”，rebuild online用的是“TABLE ACCESS FULL”; 即rebuild index是掃描索引塊,而rebuild index online是掃描全表的數(shù)據(jù)塊.

實(shí)驗(yàn)一：
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重建索引，查看執(zhí)行計(jì)劃

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重建索引，查看執(zhí)行計(jì)劃

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.

結(jié)論：alter index rebuild online實(shí)質(zhì)上是掃描表而不是掃描現(xiàn)有的索引塊來實(shí)現(xiàn)索引的重建，速度慢.
         alter index rebuild 只掃描現(xiàn)有的索引塊來實(shí)現(xiàn)索引的重建，速度快。

實(shí)驗(yàn)二：
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索引的基本結(jié)構(gòu)，在初始狀態(tài)下其block總數(shù)為5376，其中頁(yè)塊共5154 */

--刪除三分之一數(shù)據(jù)：
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

--再次查詢r(jià)edo生成量：
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

--再次查詢r(jià)edo生成量：
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

經(jīng)過前后對(duì)比coalesce 操作產(chǎn)生了大約70MB的redo數(shù)據(jù)。
Elapsed: 00:00:00.00

--再次分析索引結(jié)構(gòu)；
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

/* 可以看到執(zhí)行coalesce(合并)操作后頁(yè)塊數(shù)量下降到3439，而branch枝塊和root根塊的結(jié)構(gòu)是不會(huì)變化的，同時(shí)coalesc命令并不釋放索引上的多余空間，但索引結(jié)構(gòu)實(shí)際占用的空間BTREE_SPACE下降到了27570496 bytes */

/* 清理測(cè)試現(xiàn)場(chǎng) */
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

--再次刪除數(shù)據(jù)：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

前后比對(duì)，redo生成量為90MB左右，多出coalesce時(shí)的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

/* 索引結(jié)構(gòu)與coalesce命令維護(hù)后相同，但shrink space操作釋放了索引上的空閑空間 */

/* 清理測(cè)試現(xiàn)場(chǎng) */
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

--刪除數(shù)據(jù)：
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

前后比對(duì)發(fā)現(xiàn)，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完全相同的作用，但其產(chǎn)生的redo仍要多于coalesce于28%，與shrink space相同 */；

總結(jié)：
coalesce與shrink space命令對(duì)比重建索引(rebuild index)有一個(gè)顯著的優(yōu)點(diǎn):不會(huì)導(dǎo)致索引降級(jí)。從以上測(cè)試可以看到coalesce與shrink space compact功能完全相同；在OLTP環(huán)境中，大多數(shù)情況下我們并不希望回收索引上的空閑空間，那么coalesce或者shrink space compact(not shrink space)可以成為我們很好的選擇，雖然實(shí)際操作過程中2者消耗的資源有不少差別。并不是說coalesce就一定會(huì)消耗更少的資源，這需要在您的實(shí)際環(huán)境中具體測(cè)試，合適的才是最好的！

向AI問一下細(xì)節(jié)

重建索引index rebuild online vs offline vs index coalesce vs index shrik space

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽