重建索引indexrebuildonlinevsofflinevsindexcoalescevsindexshrikspace

重建索引：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：
http://www.askmaclean.com/archives/alter-index-coalesce-vs-shrink-space.html

什么时候需要重建索引
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就一定会消耗更少的资源，这需要在您的实际环境中具体测试，合适的才是最好的！



本文题目：重建索引indexrebuildonlinevsofflinevsindexcoalescevsindexshrikspace
转载源于：http://cqcxhl.com/article/gjsoch.html

重庆分公司，新征程启航

重建索引indexrebuildonlinevsofflinevsindexcoalescevsindexshrikspace

其他资讯