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

A lot of junior DBAs like to rebuild indexes with reuildonline because they think it won't affect their business.

There have been several failures, when the business is used, rebuild the online index, resulting in most of the business can not be used, database hang.

In fact, rebulid index is simple

drop index index_name

Alter indexindex_name rebuild;

Alter indexindex_name rebuild online;

As a DBA, when performing any operation on the production system, it is still far from enough to understand these. It is necessary to clarify the following points:

1. What impact will the execution of this command have on the business? Is it business time? If it is, do not perform similar operations. Because creating an index in a large object requires not only a large temp table space, but also DDL operations, the execution plan of SQL statements based on the original index of the table will change, resulting in a large number of parses that consume a large amount of CPU resources.

2. If the index is deleted or invalid, a large number of full table scans will occur. This not only puts pressure on system I/O, but also on CPU and memory.

3. Understand the difference between rebuild and rebuild online.

alter index rebuild online essentially scans the table rather than scanning existing index blocks to rebuild the index.

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

rebuild index online does not block DML operations during execution, but TM locks with mode 4 are required at the start and end phases. Therefore, if there is something else running for a long time before or at the end of rebuild index online, it is likely to cause a lot of lock waiting. This means that blocks will still occur before execution and exclusive locks should be avoided. If it is done during the business period, it may cause the online execution to take a long time. When rebuilding index online, it is full table scan. At this time, sorting is needed, consuming a lot of temp space. Rebuild online requires twice the index space.

Rebuild index blocks DML operations during execution, but it is faster. Rebulidindex goes to index ffs, and ffs search order is related according to the physical storage order of leaf block, which also needs sorting. It also consumes a lot of temporary table space.

In short, do not perform DDL (rebulid and rebulid online index) during business, or use DROP INDEX create index instead of rebuild index.

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.