Re-indexing (from itpub)

I. Consider re-Indexing
1: frequent update and delete operations on tables
2: The alter table .. move operation is performed on the table (the move operation causes rowid changes)
Ii. Criteria for re-Indexing
Whether re-indexing is necessary depends on whether the index is heavily skewed and whether space is wasted;
How can we determine whether the index is skewed seriously and whether it is a waste of space:
1. Structure Analysis of Indexes
Analyze index indexname validate structure;
2. query the index_stats table in the session where Step 1 is executed. Do not query the index_stats table in another session.
Select height, del_lf_rows/lf_rows from index_stats;
3. When the height> = 4 or del_lf_rows/lf_rows> 0.2 queried in step 2, the index should be rebuilt;
Example:
SQL> select count (*) from test_index;
Count (*)
----------
2072327
SQL> analyze index pk_t_test validate structure;
Index analyzed
SQL> select height, del_lf_rows/lf_rows from index_stats;
Height del_lf_rows/lf_rows
-----------------------------
3 0
SQL> Delete from test_index where rownum <250000;
249999 rows deleted
SQL> select height, del_lf_rows/lf_rows from index_stats;
Height del_lf_rows/lf_rows
-----------------------------
3 0
SQL> analyze index pk_t_test validate structure;
Index analyzed
SQL> select height, del_lf_rows/lf_rows from index_stats;
Height del_lf_rows/lf_rows
-----------------------------
3 0.0777430939338362
Iii. Index Reconstruction
1: Drop the original index and create an index;
2: Alter index indexname rebuild (online );
Method 1: time-consuming, which cannot be implemented in 24*7 Environments
Method 2: fast. It can be implemented in the 24*7 environment.
Recommended method 2
Iv. Alter index rebuid internal processes and notes
1: differences between alter index rebuild and alter index rebuil online
(1) different scanning methods
Rebuild reads data from the original index in the index fast full scan (or table full scan) mode to build a new index with sorting operations. Rebuild online performs table scanning to obtain data, sorted operations;
Rebuild method (index fast full scan or table full scan depends on the cost of the statistics)
Eg1:
SQL> explain Plan for alter index idx_policy_id2 rebuild;
Explained
SQL> select * from table (dbms_xplan.display );
Plan_table_output
---------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost |
---------------------------------------------------------------------
| 0 | alter index statement | 999k | 4882k | 3219 |
| 1 | index build non unique | idx_policy_id2 |
| 2 | sort create index | 999k | 4882k |
| 3 | index fast full scan | idx_policy_id2 | 999k | 4882k |
---------------------------------------------------------------------
Eg2:
SQL> explain Plan for alter index idx_policy_id rebuild;
Explained
SQL> select * from table (dbms_xplan.display );
Plan_table_output
---------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost |
---------------------------------------------------------------------
| 0 | alter index statement | 2072k | 9m | 461 |
| 1 | index build non unique | idx_policy_id |
| 2 | sort create index | 2072k | 9m |
| 3 | table access full | test_index | 2072k | 9m | 461 |

Eg3: (compare with eg1)
Rebuil online mode:
SQL> explain Plan for alter index idx_policy_id2 rebuild online;
Explained
SQL> select * from table (dbms_xplan.display );
Plan_table_output
---------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost |
--------------------------------------------------------------------- | 0 | alter index statement | 999k | 4882k | 3219 |
| 1 | index build non unique | idx_policy_id2 |
| 2 | sort create index | 999k | 4882k |
| 3 | table access full | test_index2 | 999k | 4882k | 3219 |
(2) rebuild will block DML operations, while rebuil online will not block DML operations;
(3) During rebuild online, the system will generate a sys_journal_xxx Iot-type temporary system log table. All index changes during rebuild online are recorded in this table, after the new index is created, maintain the record of the table to the new index, drop the old index, and rebuild online is complete.

Note:
1. Check whether the space is sufficient during rebuild;
2. Although the rebuild online operation allows DML operations, it is recommended that DML operations be performed during busy business hours;
3. the rebuild operation produces a large number of redo logs;

V. Rebuilding the partition index on the partition table
1: How to re-create a partition index:
Alter index indexname rebuild partition paritionname tablespace tablespacename;
Alter index indexname rebuild subpartition partitioname tablespace tablespacename;
The partition name can be found in user_ind_partitions.
The tablepace parameter allows the alter index operation to change the index storage space;

6. Index status description

You can view the index status in the data dictionary in three ways:
Valid
N/
Unusable

Valid: the current index is valid.
N/a: The partition index is valid.
Unusable: The index is invalid.

7. Terms

High base: Simply put, the table has multiple columns with different values.
Low base: the ticket creation comprehension means that the table has fewer columns with different values.
Number of leaf nodes to be deleted: the number of index nodes logically deleted by the delete operation on the Data row. RememberOracleAfter deleting the data row, keep the "dead" node in the index.SQLThe speed of the delete operation. Therefore, Oracle does not have to rebalance the index after deleting data rows.
Index height: The index height refers to the number of layers of indexes produced by data row insertion. When a large amount of data is added to a table, Oracle generates a new index level to adapt to the added data rows, therefore, there may be four layers of Oracle indexes, but this will only appear in areas with a large number of insert operations in the index count. The three-tier structure of Oracle indexes can support millions of projects, with 4 or more layers requiring reconstruction.
Number of reads per index access: refers to the logical I/O operations required to read a data row using the index. Logical reads do not have to be physical reads, because many of the index content has been stored in the data buffer, however, any index with more than 10 data needs to be rebuilt.

So when will it be rebuilt? We can use analyze index ........ Compute statistics analyzes tables. Check the blevel in dba_indexes. This column indicates the index speed or depth from the root block to the leaf. If the level is greater than or equal to 4. You need to re-build it, as shown below:
Select index_name, blevel from dba_indexes where blevel> = 4.
Another indicator benefiting from reconstruction is clearly the percentage of deleted items in the index to the total number of items. If it is more than 20%, it should be rebuilt as follows:
SQL> anlyze index ------ validate Structure
SQL> select (del_lf_rows_len/lf_rows_len) * 100 from index_stats where name = '------'
You can see whether the index is deleted.
The above is just a judgment, so how can reconstruction be better?
How to create an index:
A. Delete and create an index from scratch.
B. Use the alter index -------- rebuild command to recreate the index.
C. Use the alter index -------- coalesce command to recreate the index.
The following describes the advantages and disadvantages of the three methods:
1). Delete and create an index from scratch: The method is the slowest and most time-consuming. It is generally not recommended.
2 ). alter index ---- rebuild an effective way to quickly re-create an index, because existing index items are used to re-create an index. If other users operate on this table, try to use the online parameter to minimize any locking problems that may occur during index reconstruction. Alter index ------- rebuild online. however, because the new and old indexes exist at the same time, using this technique requires additional disk space for temporary use. After the index is created, the old index will be deleted. If it fails, and will not affect the original index. This method can be used to take an index to the new tablespace.
Alter index ------ rebuild tablespace -----.
The command is executed as follows:
First, read the existing indexes one by one to obtain the index keywords.
Second, enter a temporary data segment based on the new structure.
Finally, once the operation is successful, delete the original index tree and rename the temporary data segment as the new index.
Note that the alter index-rebuild command must use the tablespace clause to ensure that the reconstruction is performed in the tablespace with the same existing indexes.
3 ). alter index ----- coalesce requires no extra space during reconstruction when using the coalesce parameter. It only concatenates leaf blocks in the same index branch during index reconstruction, this minimizes the potential locking problems related to the query process. However, the coalesce option cannot be used to move an index to other tablespaces.