Oracle tool: Analyze

(I) three major functions

① Collect and delete index, table, and cluster statistics

② Verify the structure of tables, indexes, and clusters

③ Identification table and cluster and row migration and row Link

For analyze's function of collecting and deleting statistics

In Oracle, we recommend that you use the DBMS_STATS package instead of analyze to collect optimization information.

DBMS_STATS can collect information in parallel and collect global information of partition tables.

Further, CBO only uses the information collected by the DBMS_STATS package.

(Ii) Prerequisites

① In your own solution

② Or have the permission for analyze any system

(Iii) syntax

ANALYZE
{TABLE [schema.] table
[PARTITION (partition) | SUBPARTITION (subpartition)]
| INDEX [schema.] index
[PARTITION (partition) | SUBPARTITION (subpartition)]
| CLUSTER [schema.] cluster
}

{COMPUTE [SYSTEM] STATISTICS [for_clause]
| ESTIMATE [SYSTEM] STATISTICS [for_clause] [SAMPLE integer {ROWS | PERCENT}]
| Validation_clauses
| List chained rows [comment _clause]
| DELETE [SYSTEM] STATISTICS
};
Note:

① INDEX index: analyze the INDEX. The analysis result is stored in USER_INDEXES, ALL_INDEXES, or DBA_INDEXES.

② TABLE table: analyze the TABLE. The analysis results are stored in the USER_TABLES, ALL_TABLES, and DBA_TABLES tables.

Analysis table restrictions:

Data Dictionary tables cannot be analyzed.

Extension tables cannot be analyzed, but DBMS_STATS can be used for this purpose.

Temporary tables cannot be analyzed.

The following field types cannot be calculated or estimated:

REFs, varrays, nested tables, LOBs, LONGs, or object types

③ CLUSTER cluster: analyze the CLUSTER. The analysis results are stored in ALL_CLUSTERS, USER_CLUSTERS, and DBA_CLUSTERS.

④ Compute_statistics_clause

Syntax: COMPUTE [SYSTEM] STATISTICS [for_clause]

Make accurate statistics on the analysis object, and then store the information in the data dictionary. You can select tables or analyze fields.

Both computed and estimated statistical data are used by the optimizer to influence the SQL Execution Plan.

If the system option is specified, only information generated by the system is counted.

For_clause:

For table: only statistical tables

For columns: only count a field

For all columns: Count ALL fields

For all indexed columns: Count ALL fields of the Index

⑤ ESTIMATE [SYSTEM] STATISTICS [for_clause] [SAMPLE integer {ROWS | PERCENT}]

Just make a rough statistics on some rows. Applicable to large tables

SAMPLE: specifies the number of rows for statistics. If this parameter is ignored, oracle will set the default value to 1064 rows.

ROWS causes: number of rows Oracle to sample integer ROWS of the table or cluster or integer entries from the index. The integer must be at least 1.

PERCENT causes: Percentage

⑥ Validation_clses

Analyze the REF or image structure

For example:

Analyze table employees validate structure cascade;

Analyze table MERs validate ref update;

It is usually used for physical and logical consistency checks.