Oracle index and Table Analysis Methods

There are several methods for analyzing tables and indexes. For more information, see. 1. analyzetabletablenamecomputestat

There are several methods for analyzing tables and indexes. For more information, see. 1. analysis table and index (analyze does not rebuild the index) analyze table tablename compute stat

There are several methods for analyzing tables and indexes. For more information, see.

1. analysis table and index (analyze does not rebuild the index)

Analyze table tablename compute statistics
Equivalent to analyze table tablename compute statistics for table for all indexes for all columns

For table statistics exist in views: user_tables, all_tables, dba_tables

For all indexes statistics exist in views: user_indexes, all_indexes, dba_indexes

Statistics for all columns exist in views: user_tab_columns, all_tab_columns, dba_tab_columns

Note: For analysis tables and indexes, see AnalyzeAllTable stored procedure.
2. You can use the following three methods to manually analyze indexes.
Analyze index idx_t validate structure:
Analyze index idx_t compute statistics:
Analyze index idx_t estimate statistics sample 10 percent

1) analyze index idx_t validate structure:
This analysis statement is used to analyze whether there are bad blocks in the index block. Based on the analysis, we can obtain the index structure data, which will be retained
In index_stats, to determine whether this index requires rebuild. Note that such analysis does not collect index statistics.

2) validate structure has two modes: online and offline. Generally, the default mode is offline.
When analyze indexes are performed in offline mode, a table-level shared lock is applied to the table, which affects some real-time table DMl operations.
During online analysis, no lock is added, but no information is visible in index_stats.

3) analyze index idx_t compute statistics:
Used to count the index statistics (full analysis), mainly for the CBO service.

4) analyze index idx_t estimate statistics sample 10 percent
It is mainly used to specify the proportion for sampling analysis, and is also used for CBO. In this example, sample 10%

3. Re-Indexing
Alter index index_name rebuild tablespace tablespace_name
Alter index index_name rebuild tablespace tablespace_name is added to the tablespace name, and the specified index is moved to the specified tablespace.

Note:
The analyze operation only collects statistics and stores the statistics for SQL analysis in the future. It does not perform reconstruction or other implementation operations.
Still use alter index index_name rebuild

For more details, please continue to read the 2nd page content:

Related reading:

Understanding ArcSDE indexes by Oracle Indexes

How to create an optimal index using Oracle Indexing Technology

Test example of the table where the NULL value of the Oracle index column triggers the execution plan

Oracle index primary key affects query speed

Oracle index Scanning