[Go] Oracle analyze command analysis

Transferred from: http://blog.sina.com.cn/s/blog_682841ba0101bncp.html

1.analyze table T1 compute statistics for table; -->user_tables

(Statistics only on the overall information of the table, such as the number of rows, etc., does not involve table fields)

2.analyze table T2 Compute statistics for all columns; -->user_tab_columns

(Only table field information is collected)

3.analyze table T3 compute statistics for all indexed columns; -->user_tab_columns

(Only the field information for the index in the table is collected)

4.analyze table T4 Compute statistics for all indexes;àuser_indexes

(Gather information for table indexes only)

5.analyze table T5 Compute statistics;

(Collect tables, table fields, index information)

In addition, you can delete profiling data:

sql> Analyze table my_table Delete statistics;

sql> Analyze table my_table Delete statistics for table for all indexes for all indexed columns;

Example:

CREATE TABLE T1 as select * from User_objects;

CREATE TABLE t2 as select * from User_objects;

CREATE TABLE t3 as select * from User_objects;

CREATE TABLE T4 as select * from User_objects;

CREATE TABLE T5 as select * from User_objects;

CREATE TABLE T6 as SELECT * from User_objects;

Create unique index pk_t1_idx on T1 (object_id);

Create unique index pk_t2_idx on T2 (object_id);

Create unique index pk_t3_idx on t3 (object_id);

Create unique index pk_t4_idx on t4 (object_id);

Create unique index PK_T5_IDX on T5 (object_id);

Create unique index PK_T6_IDX on T6 (OBJECT_ID);

<</B> just finished building the table >

(1) Viewing the statistics of a table

Select table_name, num_rows, blocks, empty_blocks

From User_tables

where table_name in (' T1 ', ' T2 ', ' T3 ', ' T4 ', ' T5 ', ' T6 ');

(2) View statistics for a field

Select table_name,

COLUMN_NAME,

Num_distinct,

Low_value,

High_value,

Density

From User_tab_columns

where table_name in (' T1 ', ' T2 ', ' T3 ', ' T4 ', ' T5 ', ' T6 ');

(3) Viewing the statistics of the index

Select table_name,

Index_name,

Blevel,

Leaf_blocks,

Distinct_keys,

Avg_leaf_blocks_per_key Avg_leaf_blocks,

Avg_data_blocks_per_key Avg_data_blocks,

Clustering_factor,

Num_rows

From User_indexes

where table_name in (' T1 ', ' T2 ', ' T3 ', ' T4 ', ' T5 ', ' T6 ');

Two Execute analyze command

Analyze table T1 compute statistics for table; -- collecting information for tables

Analyze table T2 Compute statistics for all columns; -- collecting information for table fields

Analyze table T3 compute statistics for all indexes columns; -- Collect index field information

Analyze table T4 compute statistics; -- Collect tables, table fields, index information

Analyze table T5 Compute statistics for all indexes; -- Collecting Index information

Analyze table T6 compute Statistics for table for all indexes for all columns; -- collect tables, indexes, table field information

(1) Statistical information of the table

Select table_name, num_rows, blocks, empty_blocks

From User_tables

where table_name in (' T1 ', ' T2 ', ' T3 ', ' T4 ', ' T5 ', ' T6 ');

(2) Statistics of the fields in the table

Select table_name,

COLUMN_NAME,

Num_distinct,

Low_value,

High_value,

Density

From User_tab_columns

where table_name in (' T1 ', ' T2 ', ' T3 ', ' T4 ', ' T5 ', ' T6 ');

<</B> It collects T2 tablefield information,T3 is the fields informationof the index,T4 table information, T6 table field information >

(3) Statistical information of the index

No change, stating that Oracle has collected relevant information when the index is created

[Go] Oracle analyze command analysis