Shulou(Shulou.com)06/01 Report--

Re-indexing: ALTER INDEX..REBUILD ONLINE vs ALTER INDEX..REBUILD:

Http://blog.csdn.net/pan_tian/article/details/46563897

In-depth understanding of re-indexing (original):

Http://czmmiao.iteye.com/blog/1481957

The difference between alter index coalesce and 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

When do you need to rebuild the index?

1. The deleted space is not reused, resulting in fragmentation of the index.

2. After deleting a large amount of table data, the space is not reused, resulting in the "false height" of the index.

3. The clustering_facto of the index is inconsistent with the table.

Some people think that it is necessary to rebuild the index tree when the height of the index tree is more than 4, but if the table is of a larger order of magnitude, there will be no higher tree, and the reconstruction will not change the height of the index tree, unless it is due to a large number of index tree "virtual height". Reconstruction will improve performance, which, of course, comes back to the problem of index fragmentation.

As to whether the index needs to be rebuilt, Oracle has this sentence:

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.

In addition, I found a better article for the analysis of "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 essentially scans tables rather than existing index blocks to rebuild the index.

Alter index rebuild only scans existing index blocks to rebuild the index.

Rebuild index online does not block DML operations during execution, but TM locks with a mode of 4 are required during the start and end phases. Therefore, if something else is running for a long time before or at the end of the rebuild index online, it is likely to cause a lot of lock waiting. In other words, blocking still occurs before execution, and exclusive locks should be avoided.

Rebuild index will block DML operations during execution, but at a faster speed.

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

The two scan methods are different when rebuilding the index. Rebuild uses "INDEX FAST FULL SCAN" and rebuild online uses "TABLE ACCESS FULL"; that is, rebuild index is the scan index block and rebuild index online is the data block scanning the whole table.

Experiment 1:

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 re-index to view the execution plan

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 re-index to view the execution plan

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 | 4251 | 3 (0) | 00:00:01 |

10 rows selected.

Conclusion: alter index rebuild online essentially scans tables rather than existing index blocks to reconstruct the index, which is slow.

Alter index rebuild only scans the existing index blocks to rebuild the index, which is fast.

Experiment 2:

SQL > create table YOUYUS as select rownum T1 from dual connect by level create index ind_youyus on youyus ('Achilles dagger 20 dint B') T2 from dual connect by level create index ind_youyus on youyus

Index created.

Elapsed: 00:00:04.13

-Analytical index

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

/ * you can see the basic structure of the IND_YOUYUS index. In the initial state, the total number of block is 5376, including 5154 page blocks * /

-- Delete 1/3 data:

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

-- query the amount of redo generated again:

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

-- merge the index using the coalesce sentence:

SQL > alter index ind_youyus coalesce

Index altered.

Elapsed: 00:00:03.72

-- query the amount of redo generated again:

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

After comparing the coalesce operation before and after, the redo data of about 70MB is produced.

Elapsed: 00:00:00.00

-- analyze the index structure again

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

/ * you can see that the number of page blocks decreased to 3439 after the coalesce (merge) operation, while the structure of branch branch blocks and root root blocks will not change. At the same time, the coalesc command does not release excess space on the index, but the actual space occupied by the index structure BTREE_SPACE is reduced to 27570496 bytes * /

/ * Clean up the test site * /

SQL > drop table YOUYUS

Table dropped.

Elapsed: 00:00:01.42

SQL >

SQL > create table YOUYUS as select rownum T1 from dual connect by level ('Achilles dagger 20 recorder B') T2 from dual connect by level

SQL > create index ind_youyus on youyus (T1 and T2) nologging

Index created.

Elapsed: 00:00:03.68

-- Delete the data again: 1Compact 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

-- query the current amount of redo generated:

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

-use the shrink space clause to recycle the index:

SQL > alter index ind_youyus shrink space

Index altered.

Elapsed: 00:00:05.30

-- query the current redo generation again:

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

Before and after comparison, the amount of redo generated is about 90MB, which is about 28% more than that of coalesce.

-- analyze the index again:

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

/ * the index structure is the same as after the coalesce command is maintained, but the shrink space operation frees up free space on the index * /

/ * Clean up the test site * /

SQL > drop table YOUYUS

Table dropped.

Elapsed: 00:00:00.51

SQL >

SQL > create table YOUYUS as select rownum T1 from dual connect by level ('Achilles dagger 20 recorder B') T2 from dual connect by level

SQL >

SQL > create index ind_youyus on youyus (T1 and T2) nologging

Index created.

Elapsed: 00:00:03.60

-- Delete data:

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

-- query the current amount of redo generated:

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

-use the shrink space compact clause to recycle the index:

SQL > alter index ind_youyus shrink space compact

Index altered.

Elapsed: 00:00:04.95

-- query the current redo generation again:

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

Before and after comparison, it is found that the amount of redo generated is about 90mb, which is the same as the shrink space clause.

-- analyze the index again:

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 plays exactly the same role as coalesce, but it still produces 28% more redo than coalesce, the same as shrink space * /

Summary:

Coalesce has a significant advantage over the shrink space command to rebuild the index (rebuild index): it does not cause the index to degrade. From the above tests, we can see that coalesce and shrink space compact function are exactly the same; in the OLTP environment, in most cases we do not want to reclaim the free space on the index, then coalesce or shrink space compact (not shrink space) can be a good choice for us, although there are many differences in the resources consumed by the two in practice. This is not to say that coalesce will necessarily consume less resources, which needs to be tested in your actual environment, and the right one is the best!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.