Analysis of Oracle Tables and indexes

1. Analysis table and index (analyze will not rebuild the index)

Analyze Tabletablename COMPUTE Statistics
Equivalent to analyze table TableName compute statistics for table for all indexes ForAll

The statistics for table exist in the view: User_tables, All_tables, Dba_tables

The statistics for the all indexes exist in the view: User_indexes, All_indexes, dba_indexes

The statistics for the all columns exist in the view: User_tab_columns, All_tab_columns, Dba_tab_columns

Note: Analysis table and index see analyzealltable stored procedure

2, generally speaking, you can use the following three ways to manually analyze the index.
Analyze index idx_t Validate structure:
Analyze index idx_t COMPUTE statistics:
Analyze index idx_t estimate statistics sample percent

1) analyze index idx_t Validate structure:
This parsing statement is used to analyze whether there are any bad blocks in the index, so based on the analysis we can get the structure data of the index, which will be retained to
Index_stats, to determine if the index needs rebuild. It is to be noted that such an analysis does not collect index statistical information.

2 Validate structure has two kinds of modes: online, offline, generally speaking the default way is offline.
When an index is analyze in offline mode, a table-level shared lock is added to the table, which can have a certain effect on some real-time DML operations at the table.
While in online mode analysis, no lock is added, but no information is visible in the index_stats.

3) analyze index idx_t COMPUTE statistics:
Statistical information used for statistical indexing (full analysis), mainly for CBO services.

4) Analyze index idx_t estimate statisticssample percent
is mainly used to specify the proportion of sampling analysis, but also for the CBO service. Example of sampling 10%

3. Rebuilding Indexes
Alter index INDEX_NAME rebuild tablespace tablespace_name
Alter index INDEX_NAME rebuild tablespace tablespace_name joins the table space name and moves the specified index into the specified table space.

Note:
Analyze operations are just statistical information and store statistics for later analysis of SQL usage, not for specific implementations such as rebuilding, so to rebuild the index
Or are you going to use ALTER index INDEX_NAME rebuild

4. Other statistical methods1) Dbms_stats: This is, of course, the most powerful analysis package.
--Create a history of statistical information retention table
EXEC dbms_stats.create_stat_table (ownname => ' Scott ', Stattab => ' stat_table ');

--Export the entire scheme's statistics
EXEC dbms_stats.export_schema_stats (ownname => ' Scott ', Stattab => ' stat_table ');

--Analysis scheme
Exec dbms_stats.gather_schema_stats (ownname => ' test ', Options => ' Gatherauto ',
Estimate_percent => Dbms_stats.auto_sample_size,
Method_opt => ' For all indexed columns ',
Degree => 6);

--Analysis Table
EXEC dbms_stats.gather_table_stats (ownname => ' TEST ', tabname => ' Sm_user ', estimate_percent => 10,method_opt= > ' For all indexed columns ');

--Analysis Index
EXEC dbms_stats.gather_index_stats (ownname => ' TEST ', indname => ' Pk_user_index ', estimate_percent => ' 10 ', Degree => ' 4 ');

--Delete Table statistics If you find the execution plan goes wrong
EXEC dbms_stats.delete_table_stats (ownname => ' TEST ', tabname => ' Sm_user ');

--Import Table history statistics
EXEC dbms_stats.import_table_stats (ownname => ' TEST ', tabname => ' Sm_user ', Stattab => ' stat_table ');

--If most of the table's execution plans go wrong after analysis, you need to return the entire scheme's statistics
EXEC dbms_stats.import_schema_stats (ownname => ' TEST ', Stattab => ' Sm_user ');

--Import Index statistics
EXEC dbms_stats.import_index_stats (ownname => ' TEST ', indname => ' Pk_user_index ', Stattab => ' stat_table ')


Analyze and Dbms_stats are different places:
Analyze is the statistic that updates tables and indexes at the same time, and dbms_stats updates the statistics of the table before updating the statistics on the index.
The problem here is that when the statistics of the tables are updated and the statistics of the indexes are not updated, then the CBO may choose the wrong plan

2) dbms_utility. Analyze_schema: You can directly analyze all the objects in a SCHEMA
such as: EXEC dbms_utility. Analyze_schema (' Lttfm ', ' COMPUTE ');

3) Dbms_ddl. Analyze_object: Collect statistics on objects