Oracle tool: Analyze

Oracle tools: analyze (I) three major functions www.2cto.com ① collect and delete indexes, statistical information of tables and clusters ② verify the structure of tables, indexes and clusters ③ identification tables and cluster and row migration and row links for analyze collection in addition, we recommend that you use the DBMS_STATS package instead of analyze to collect optimization information, we can collect the global information of the partition table. Further, CBO only uses the information collected by the DBMS_STATS package (ii) Prerequisites ① In its own solution ② or has the permission for analyze any system (iii) syntax www.2cto.com ANALYZE {TABLE [schema.] table [PARTITION (partition) | SUBPARTITION (subpartition)] | INDEX [schema.] index [PARTIT ION (partition) | SUBPARTITION (subpartition)] | CLUSTER [schema.] cluster} {COMPUTE [SYSTEM] STATISTICS [for_clause] | ESTIMATE [SYSTEM] STATISTICS [for_clause] [SAMPLE integer {ROWS | PERCENT}] | validation_clses | list chained rows [pai_clause] | DELETE [SYSTEM] STATISTICS }; note: ① INDEX index: analyze the INDEX. The analysis results will be stored in USER_INDEXES, ALL_INDEXES, or DBA_INDEXES. ② TABLE: Analyze and analyze the table. The results will be placed in the table analysis table of USER_TABLES, ALL_TABLES, and DBA_TABLES: the data dictionary table cannot be analyzed, but the extended table cannot be analyzed, however, DBMS_STATS can be used to achieve this purpose. It is not possible to analyze the temporary table. The following field types cannot be calculated or estimated: REFs, varrays, nested tables, LOBs, LONGs, or object types ③ CLUSTER cluster CLUSTER: analyze the cluster, and the analysis results will be placed in ALL_CLUSTERS, USER_CLUSTERS and DBA_CLUSTERS ④ compute_statistics_clause Syntax: COMPUTE [SYSTEM] STATISTICS [for_clause] to perform accurate STATISTICS on the analysis object, then, store the information in the data dictionary. You can choose to analyze the statistical data in tables or fields by computed and estimated. The optimizer is used to affect the SQL Execution Plan. If the system option is specified, only the information generated by the system is counted. for_clause: for table: only statistical table 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}] only makes a rough STATISTICS on some ROWS. Apply to large table SAMPLE: specify the number of ROWS for statistics. If this parameter is ignored, oracle will default to 1064 ROWS of 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_clauses analyzes the REF or the STRUCTURE of the image, for example, analyze table employees validate structure cascade; analyze table customers validate ref update; usually used for physical and logical consistency checks.