Index Rebuild and Rebuild Online details

Index Rebuild and Rebuild Online details

In the Oracle O & M field, two concepts around indexes have been discussed on the Internet. One is the necessity of regular Index reconstruction, and the other is the Rebuild and Rebuild Online discussions. Many predecessors of the former have had a deep discussion on various occasions, including Oracle MOS.

The latter is mainly discussed in two aspects:

• For systems with big data and high availability, the index rebuild action must be used with caution. It is best to choose a time window with fewer DML operations to avoid affecting the business system;
• The difference between Rebuild online and rebuild processing. Compared with rebuild, rebuild online has a relatively small lock action on DML operations, but the corresponding operation time is also relatively large. For a high availability system, rebuild online is often an acceptable compromise;

This article mainly analyzes the features of the two rebuild indexes from the execution plan and tracking execution perspectives.

1. Environment Introduction

I chose Oracle 11gR2 for testing. The specific version is 11.2.0.4.

SQL> select * from v $ version;

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-Production

PL/SQL Release 11.2.0.4.0-Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0-Production

NLSRTL Version 11.2.0.4.0-Production

Create a data table T.

SQL> create table t as select * from dba_objects;

Table created

 

SQL> create index idx_t_id on t (object_id );

Index created

SQL> exec dbms_stats.gather_table_stats (user, 't', cascade => true );

PL/SQL procedure successfully completed

Next, we will analyze and study the execution plan.

2. Explain Plan study execution Plan

The Explain Plan method is often used to analyze the execution Plan of SQL statements. The author finds that the Explain statement can still analyze the corresponding results for DDL operations such as alert index.

First, test the rebuild statement.

SQL> explain plan for alter index idx_t_id rebuild;

Explained

 

SQL> select * from table (dbms_xplan.display );

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1483129259

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time

--------------------------------------------------------------------------------

| 0 | alter index statement | 86129 | 420K | 336 (1) |

| 1 | index build non unique | IDX_T_ID |

| 2 | sort create index | 86129 | 420K |

| 3 | index fast full scan | IDX_T_ID |

--------------------------------------------------------------------------------

 

10 rows selected

 

 

Here, we first see the Index Fast Full Scan action. In my previous articles, I have analyzed in detail the differences between Index Fast Full Scan and Index Full Scan. To put it simply, the differences are as follows:

Ü Index Fast Full Scan is a standard multi-Fast read operation; Index Full Scan is a single read operation;

Ü Index Fast Full Scan returns unordered results; Index Full Scan returns an ordered result set;

Ü Index Fast Full Scan can perform parallel operations; Index Full Scan can only perform single-process read operations;

In the above execution plan, we found that the rebuild operation was not based on the data table, but the data indexed IDX_T_ID (of course the leaf node) as the creation basis. Because the unordered result set returned by Index Fast Full Scan, the Sort Create Index action is called to form a new Index object.

In summary, for the rebuild action, the leaf node data of the index is used as the data basis during the indexing process. Furthermore, if the rebuild index and data table are inconsistent, the newly generated index must be inconsistent.

The following shows the rebuild online analysis:

SQL> explain plan for alter index idx_t_id rebuild online;

Explained

 

SQL> select * from table (dbms_xplan.display );

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1193657316

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time

--------------------------------------------------------------------------------

| 0 | alter index statement | 86129 | 420K | 336 (1) |

| 1 | index build non unique | IDX_T_ID |

| 2 | sort create index | 86129 | 420K |

| 3 | table access full | T | 86129 | 420K | 336 (1) |

--------------------------------------------------------------------------------

10 rows selected

From the execution plan, the difference between the two is mainly in step 3, that is, the Table Access Full operation, and the operation is based on the data Table T. Therefore, rebuild online is based on the data collection of the original data table, and is a full table scan operation for the data table.

This partially explains why rebuild online takes a longer time than rebuild, because the Table Access Full operation will Access all data segment structures, while Index Fast Full Scan will Access all Index segment structures. Generally, the index segment is far smaller than the data segment.

In summary, rebuild online is based on the content of the data table. The Retrieval time is a little longer, but the lock action is relatively small.

Next, I will analyze the differences in data reading between rebuild and rebuild online from the perspective of practice tracking.

For more details, please continue to read the highlights on the next page:

1 2 Next Page