ANALYZE

Syntax

for_clause::=

Purpose

This statement lets you

• Collect or delete statistics about an index or index partition, table or table partition, index-organized table, cluster, or scalar object attribute.

• Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF).

• Identify migrated and chained rows of a table or cluster.

For most statistics collection purposes, Oracle Corporation recommends that you use the DBMS_STATS package. That package lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. See Oracle8i Supplied PL/SQL Packages Reference for more information on this package.

However, you can use this statement for any of the purposes described in this section, and you must use this statement (rather than the DBMS_STATS package) for the following purposes:

• To use the VALIDATE or LIST CHAINED ROWS clauses

• To sample a number (rather than a percentage) of rows

• To collect statistics not used by the optimizer (such as information on freelist blocks)

Prerequisites

The schema object to be analyzed must be local, and it must be in your own schema or you must have the ANALYZE ANY system privilege.

If you want to list chained rows of a table or cluster into a list table, the list table must be in your own schema, or you must have INSERT privilege on the list table, or you must have INSERT ANY TABLE system privilege.

If you want to validate a partitioned table, you must have INSERT privilege on the table into which you list analyzed rowids, or you must have INSERT ANY TABLE system privilege.

Keywords and Parameters

schema	is the schema containing the index, table, or cluster. If you omit schema, Oracle assumes the index, table, or cluster is in your own schema.
INDEX index	identifies an index to be analyzed (if no for_clause is used).
	Oracle collects the following statistics for an index (statistics marked with an asterisk are always computed exactly): Depth of the index from its root block to its leaf blocks* Number of leaf blocks Number of distinct index values Average number of leaf blocks per index value Average number of data blocks per index value (for an index on a table) Clustering factor (how well ordered the rows are about the indexed values) Index statistics appear in the data dictionary views USER_INDEXES, ALL_INDEXES, and DBA_INDEXES.
	For a domain index, this statement invokes the user-defined statistics collection function specified in the statistics type associated with the index (see "ASSOCIATE STATISTICS"). If no statistics type is associated with the domain index, the statistics type associated with its indextype is used. If no statistics type exists for either the index or its indextype, no user-defined statistics are collected. User-defined index statistics appear in the data dictionary views USER_USTATS, ALL_USTATS, and DBA_USTATS. Restriction: You cannot analyze a domain index that is marked LOADING or FAILED. See Also: "CREATE INDEX" for more information on domain indexes.
TABLE table	identifies a table to be analyzed. When you collect statistics for a table, Oracle also automatically collects the statistics for each of the table's indexes and domain indexes, provided that no for_clauses are used. When you analyze a table, Oracle collects statistics about expressions occurring in any function-based indexes as well. Therefore, be sure to create function-based indexes on the table before analyzing the table. See Also: "CREATE INDEX" for more information about function-based indexes.
	When analyzing a table, Oracle skips all domain indexes marked LOADING or FAILED. Table statistics, including the status of domain indexes, appear in the data dictionary views USER_TABLES, ALL_TABLES, and DBA_TABLES.
	Oracle collects the following statistics for a table (statistics marked with an asterisk are always computed exactly): Number of rows * Number of data blocks below the high water mark (that is, the number of data blocks that have been formatted to receive data, regardless whether they currently contain data or are empty) * Number of data blocks allocated to the table that have never been used Average available free space in each data block in bytes Number of chained rows Average row length, including the row's overhead, in bytes
	Restrictions: You cannot use ANALYZE to collect statistics on data dictionary tables. You cannot use ANALYZE to collect default statistics on a temporary table. However, if you have created an association between one or more columns of a temporary table and a user-defined statistics type, you can use ANALYZE to collect the user-defined statistics on the temporary table. (The association must already exist.) You cannot compute or estimate statistics for the following column types: REFs, varrays, nested tables, LOBs (LOBs are not analyzed, they are skipped), LONGs, or object types. However, if a statistics type is associated with such a column, user-defined statistics are collected. See Also: "ASSOCIATE STATISTICS".
PARTITION | SUBPARTITION	specifies that statistics will be gathered for partition or subpartition. You cannot use this clause when analyzing clusters. If you specify PARTITION and table is composite-partitioned, Oracle analyzes all the subpartitions within the specified partition.
CLUSTER cluster	identifies a cluster to be analyzed. When you collect statistics for a cluster, Oracle also automatically collects the statistics for all the cluster's tables and all their indexes, including the cluster index.
	For an indexed cluster, Oracle collects the average number of data blocks taken up by a single cluster key value and all of its rows. For a hash cluster, Oracle collects the average number of data blocks taken up by a single hash key value and all of its rows. These statistics appear in the data dictionary views USER_CLUSTERS and DBA_CLUSTERS.
COMPUTE STATISTICS	computes exact statistics about the analyzed object and stores them in the data dictionary. When you analyze a table, both table and column statistics are collected.
ESTIMATE STATISTICS	estimates statistics about the analyzed object and stores them in the data dictionary.
Both computed and estimated statistics are used by the Oracle optimizer to choose the execution plan for SQL statements that access analyzed objects. These statistics may also be useful to application developers who write such statements. For information on how these statistics are used, see Oracle8i Designing and Tuning for Performance.
	SAMPLE integer	specifies the amount of data from the analyzed object Oracle samples to estimate statistics. If you omit this parameter, Oracle samples 1064 rows. The default sample value is adequate for tables up to a few thousand rows. If your tables are larger, specify a higher value for SAMPLE. If you specify more than half of the data, Oracle reads all the data and computes the statistics.
	ROWS	causes 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 Oracle to sample integer percent of the rows from the table or cluster or integer percent of the index entries. The integer can range from 1 to 99.
for_clause	specifies whether an entire table or index, or just particular columns, will be analyzed. The following clauses apply only to the ANALYZE TABLE version of this statement:
	FOR TABLE	restricts the statistics collected to only table statistics rather than table and column statistics.
	FOR COLUMNS	restricts the statistics collected to only column statistics for the specified columns and scalar object attributes, rather than for all columns and attributes; attribute specifies the qualified column name of an item in an object.
	FOR ALL COLUMNS	collects column statistics for all columns and scalar object attributes.
	FOR ALL INDEXED COLUMNS	collects column statistics for all indexed columns in the table.
	Column statistics can be based on the entire column or can use a histogram by specifying SIZE (see below). Oracle collects the following column statistics: Number of distinct values in the column as a whole Maximum and minimum values in each band See Also: Oracle8i Designing and Tuning for Performance and "Histogram Examples" for more information on histograms.
	Column statistics appear in the data dictionary views USER_TAB_COLUMNS, ALL_TAB_COLUMNS, and DBA_TAB_COLUMNS. Histograms appear in the data dictionary views USER_TAB_HISTOGRAMS, DBA_TAB_HISTOGRAMS, and ALL_TAB_HISTOGRAMS; USER_PART_HISTOGRAMS, DBA_PART_HISTOGRAMS, and ALL_PART_HISTOGRAMS; and USER_SUBPART_HISTOGRAMS, DBA_SUBPART_HISTOGRAMS, and ALL_SUBPART_HISTOGRAMS.
	Note: The MAXVALUE and MINVALUE columns of USER_, DBA_, and ALL_TAB_COLUMNS have a length of 32 bytes. If you analyze columns with a length >32 bytes, and if the columns are padded with leading blanks, Oracle may take into account only the leading blanks and return unexpected statistics.
	If a user-defined statistics type has been associated with any columns, the for_clause collects user-defined statistics using that statistics type. If no statistics type is associated with a column, Oracle checks to see if any statistics type has been associated with the type of the column, and uses that statistics type. If no statistics type has been associated with either the column or its user-defined type, no user-defined statistics are collected. User-defined column statistics appear in the data dictionary views USER_USTATS, ALL_USTATS, and DBA_USTATS.
	If you want to collect statistics on both the table as a whole and on one or more columns, be sure to generate the statistics for the table first, and then for the columns. Otherwise, the table-only ANALYZE will overwrite the histograms generated by the column ANALYZE. For example, issue the following statements: ANALYZE TABLE emp ESTIMATE STATISTICS; ANALYZE TABLE emp ESTIMATE STATISTICS FOR ALL COLUMNS;
	FOR ALL INDEXES	specifies that all indexes associated with the table will be analyzed.
	FOR ALL LOCAL INDEXES	specifies that all local index partitions are analyzed. You must specify the keyword LOCAL if the PARTITION clause and INDEX are specified.
	SIZE	specifies the maximum number of partitions in the histogram. The default value is 75, minimum value is 1, and maximum value is 254.
DELETE STATISTICS	deletes any statistics about the analyzed object that are currently stored in the data dictionary. Use this statement when you no longer want Oracle to use the statistics. When you use this clause on a table, Oracle also automatically removes statistics for all the table's indexes. When you use this clause on a cluster, Oracle also automatically removes statistics for all the cluster's tables and all their indexes, including the cluster index.
	If user-defined column or index statistics were collected for an object, Oracle also removes the user-defined statistics by invoking the statistics deletion function specified in the statistics type that was used to collect the statistics.
VALIDATE REF UPDATE	validates the REFs in the specified table, checks the rowid portion in each REF, compares it with the true rowid, and corrects, if necessary. You can use this clause only when analyzing a table.
	SET DANGLING TO NULL	sets to NULL any REFs (whether or not scoped) in the specified table that are found to point to an invalid or nonexistent object.
		Note: If the owner of the table does not have SELECT object privilege on the referenced objects, Oracle will consider them invalid and set them to NULL. Subsequently these REFs will not be available in a query, even if it is issued by user with appropriate privileges on the objects.
VALIDATE STRUCTURE	validates the structure of the analyzed object. The statistics collected by this clause are not used by the Oracle optimizer, as are statistics collected by the COMPUTE STATISTICS and ESTIMATE STATISTICS clauses. For a table, Oracle verifies the integrity of each of the table's data blocks and rows. For a cluster, Oracle automatically validates the structure of the cluster's tables. For a partitioned table, Oracle also verifies that the row belongs to the correct partition. If the row does not collate correctly, the rowid is inserted into the INVALID_ROWS table. For a temporary table, Oracle validates the structure of the table and its indexes during the current session.
	For an index, Oracle verifies the integrity of each data block in the index and checks for block corruption. This clause does not confirm that each row in the table has an index entry or that each index entry points to a row in the table. You can perform these operations by validating the structure of the table with the CASCADE clause. Oracle stores statistics about the index in the data dictionary views INDEX_STATS and INDEX_HISTOGRAM, which are described in Oracle8i Reference.
	Validating the structure of an object prevents SELECT, INSERT, UPDATE, and DELETE statements from concurrently accessing the object. Therefore, do not use this clause on the tables, clusters, and indexes of your production applications during periods of high database activity. If Oracle encounters corruption in the structure of the object, an error message is returned to you. In this case, drop and re-create the object.
	INTO	specifies a table into which Oracle lists the rowids of the partitions whose rows do not collate correctly. If you omit schema, Oracle assumes the list is in your own schema. If you omit this clause altogether, Oracle assumes that the table is named INVALID_ROWS. The SQL script used to create this table is UTLVALID.SQL.
	CASCADE	validates the structure of the indexes associated with the table or cluster. If you use this clause when validating a table, Oracle also validates the table's indexes. If you use this clause when validating a cluster, Oracle also validates all the clustered tables' indexes, including the cluster index.
		If you use this clause to validate an enabled (but previously disabled) function-based index, validation errors may result. In this case, you must rebuild the index.
LIST CHAINED ROWS	identifies migrated and chained rows of the analyzed table or cluster. You cannot use this clause when analyzing an index.
	INTO	specifies a table into which Oracle lists the migrated and chained rows. If you omit schema, Oracle assumes the list table is in your own schema. If you omit this clause altogether, Oracle assumes that the table is named CHAINED_ROWS. The list table must be on your local database.
		You can create the CHAINED_ROWS table using one of these scripts: UTLCHAIN.SQL uses physical rowids. Therefore it can accommodate rows from conventional tables but not from index-organized tables. (See the Note that follows.) UTLCHN1.SQL uses universal rowids, so it can accommodate rows from both conventional and index-organized tables. If you create your own chained-rows table, it must follow the format prescribed by one of these two scripts. See Oracle8i Migration for compatibility issues related to the use of these scripts.
	Note: If you are analyzing index-organized tables based on primary keys (rather than universal rowids), you must create a separate chained-rows table for each index-organized table to accommodate its primary-key storage. Use the SQL scripts DBMSIOTC.SQL and PRVTIOTC.PLB to define the BUILD_CHAIN_ROWS_TABLE procedure, and then execute this procedure to create an IOT_CHAINED_ROWS table for each such index-organized table. For information on the SQL scripts, see the DBMS_IOT package in Oracle8i Supplied PL/SQL Packages Reference. For information on eliminating migrated and chained rows, see Oracle8i Designing and Tuning for Performance.

Examples

Analyzing a Cluster

The following statement estimates statistics for the CUST_HISTORY table and all of its indexes:

ANALYZE TABLE cust_history
   ESTIMATE STATISTICS; 

Deleting Statistics

The following statement deletes statistics about the CUST_HISTORY table and all its indexes from the data dictionary:

ANALYZE TABLE cust_history
   DELETE STATISTICS; 

Histogram Examples

The following statement creates a 10-band histogram on the SAL column of the EMP table:

ANALYZE TABLE emp 
   COMPUTE STATISTICS FOR COLUMNS sal SIZE 10;

You can also collect histograms for a single partition of a table. The following statement analyzes the EMP table partition P1:

ANALYZE TABLE emp PARTITION (p1) COMPUTE STATISTICS;

Index Example

The following statement validates the structure of the index PARTS_INDEX:

ANALYZE INDEX parts_index VALIDATE STRUCTURE; 

Table Examples

The following statement analyzes the EMP table and all of its indexes:

ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE; 

For a table, the VALIDATE REF UPDATE clause verifies the REFs in the specified table, checks the rowid portion of each REF, and then compares it with the true rowid. If the result is an incorrect rowid, the REF is updated so that the rowid portion is correct.

The following statement validates the REFs in the EMP table:

ANALYZE TABLE emp 
   VALIDATE REF UPDATE;

Cluster Example

The following statement analyzes the ORDER_CUSTS cluster, all of its tables, and all of their indexes, including the cluster index:

ANALYZE CLUSTER order_custs
    VALIDATE STRUCTURE CASCADE; 

Chained Rows Example

The following statement collects information about all the chained rows of the table ORDER_HIST:

ANALYZE TABLE order_hist
    LIST CHAINED ROWS INTO cr; 

The preceding statement places the information into the table CR. You can then examine the rows with this query:

SELECT * 
    FROM cr; 

OWNER_NAME  TABLE_NAME  CLUSTER_NAME  HEAD_ROWID         TIMESTAMP
----------  ----------  ------------  ------------------ ---------
SCOTT       ORDER_HIST                AAAAZzAABAAABrXAAA 15-MAR-96 

COMPUTE Example

The following statement calculates statistics for a scalar object attribute:

ANALYZE TABLE emp COMPUTE STATISTICS FOR COLUMNS addr.street;