Oracle7 Server SQL Reference

ANALYZE

Purpose

To perform one of the following functions on an index, table, or cluster:

to collect statistics about the object used by the optimizer and store them in the data dictionary

to delete statistics about the object from the data dictionary

to validate the structure of the object

to identify migrated and chained rows of the table or cluster

Prerequisites

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

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the creation label of the object to be analyzed or you must satisfy one of the following criteria:

If the object's creation label is higher than your DBMS label, you must have READUP and WRITEUP system privileges

If the object's creation label is lower than your DBMS label, you must have WRITEDOWN system privilege.

If the object's creation label and your DBMS label are not comparable, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

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 are using Trusted Oracle7 in DBMS MAC mode, the list table must also meet the criteria for the analyzed object described above.

Syntax

Keywords and Parameters

INDEX

identifies an index to be analyzed (if no FOR clause is used). If you omit schema, Oracle7 assumes the index is in your own schema.

TABLE

identifies a table to be analyzed. If you omit schema, Oracle7 assumes the table is in your own schema. When you collect statistics for a table, Oracle7 also automatically collects the statistics for each of the table's indexes, provided that no FOR clauses are used.

CLUSTER

identifies a cluster to be analyzed. If you omit schema, Oracle7 assumes the cluster is in your own schema. When you collect statistics for a cluster, Oracle7 also automatically collects the statistics for all the cluster's tables and all their indexes, including the cluster index.

COMPUTE STATISTICS

computes exact statistics about the analyzed object and stores them in the data dictionary.

ESTIMATE STATISTICS

estimates statistics about the analyzed object and stores them in the data dictionary.

SAMPLE specifies the amount of data from the analyzed object Oracle7 samples to estimate statistics. If you omit this parameter, Oracle7 samples 1064 rows. If you specify more than half of the data, Oracle7 reads all the data and computes the statistics.

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

PERCENT causes Oracle7 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.

Histogram statistics are described in Oracle7 Server Tuning. The following clauses only apply to the ANALYZE TABLE version of this command:

FOR TABLE collect table statistics for the table.

FOR ALL COLUMNS

collect column statistics for all columns in the table.

FOR ALL INDEXED COLUMNS

collect column statistics for all indexed columns in the table.

FOR COLUMNS

collect column statistics for the specified columns.

FOR ALL INDEXES

all indexes associated with the table will be analyzed.

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.

VALIDATE STRUCTURE

validates the structure of the analyzed object. If you use this option when analyzing a cluster, Oracle7 automatically validates the structure of the cluster's tables.

CASCADE

validates the structure of the indexes associated with the table or cluster. If you use this option when validating a table, Oracle7 also validates the table's indexes. If you use this option when validating a cluster, Oracle7 also validates all the clustered tables' indexes, including the cluster index.

LIST CHAINED ROWS

identifies migrated and chained rows of the analyzed table or cluster. You cannot use this option when analyzing an index.

INTO specifies a table into which Oracle7 lists the migrated and chained rows. If you omit schema, Oracle7 assumes the list table is in your own schema. If you omit this clause altogether, Oracle7 assumes that the table is named CHAINED_ROWS. The list table must be on your local database.

Collecting Statistics

You can collect statistics about the physical storage characteristics and data distribution of an index, table, column, or cluster and store them in the data dictionary. For computing or estimating statistics

Computation always provides exact values, but can take longer than estimation.

Estimation is often much faster than computation and the results are usually nearly exact.

Use estimation, rather than computation, unless you feel you need exact values. Some statistics are always computed exactly, regardless of whether you specify computation or estimation. If you choose estimation and the time saved by estimating a statistic is negligible, Oracle7 computes the statistic exactly.

If the data dictionary already contains statistics for the analyzed object, Oracle7 updates the existing statistics with the new ones.

The statistics are used by the Oracle7 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 Oracle7 Server Tuning.

The following sections list the statistics for indexes, tables, columns, and clusters.

Indexes

For an index, Oracle7 collects the following statistics:

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 are the rows about the indexed values)

The statistics marked with asterisks (*) are always computed exactly.

Index statistics appear in the data dictionary views USER_INDEXES, ALL_INDEXES , and DBA_INDEXES .

Tables

For a table, Oracle7 collects the following statistics:

number of rows

number of data blocks currently containing data *

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

The statistics marked with asterisks (*) are always computed exactly.

Table statistics appear in the data dictionary views USER_TABLES, ALL_TABLES , and DBA_TABLES .

Columns

Column statistics can be based on the entire column or can use a histogram. A histogram partitions the values in the column into bands, so that all column values in a band fall within the same range In some cases, it is useful to see how many values fall in various ranges. Oracle's histograms are height balanced as opposed to width balanced. This means that the column values are divided into bands so that each band contains approximately the same number of values. The useful information the histogram provides, then, is where in the range of values the endpoints fall. Width-balanced histograms, on the other hand, divide the data into a number of ranges, all of which are the same size, and then count the number of values falling into each range.

The size parameter specifies how many bands the column should be divided into. A size of 1 treats the entire column as a single band, which is equivalent to not using histograms at all.

The column statistics that Oracle7 collects are the following:

number of distinct values in the column as a whole

maximum and minimum values in each band

When to use Histograms For uniformly distributed data, the cost-based approach makes fairly accurate guesses at the cost of executing a particular statement. For non-uniformly distributed data, Oracle allows you to store histograms describing the data distribution of a particular column. These histograms are stored in the dictionary and can be used by the cost-based optimizer.

Since they are persistent objects, there is a maintenance and space cost for using histograms. You should only compute histograms for columns that you know have highly-skewed data distribution. Also, be aware that histograms, as well as all optimizer statistics, are static. If the data distribution of a column changes frequently, you must reissue the ANALYZE command to recompute the histogram for that column.

Histograms are not useful for columns with the following characteristics:

all predicates on the column use bind variables

the column data is uniformly distributed

the column is not used in WHERE clauses of queries

the column is unique and is used only with equality predicates

Create histograms on columns that are frequently used in WHERE clauses of queries and have a highly-skewed data distribution. You create a histogram by using the ANALYZE TABLE option of this command. For example, if you want to create a 10-band histogram on the SAL column of the EMP table, issue the following statement:

ANALYZE TABLE emp 
	COMPUTE STATISTICS FOR COLUMNS sal SIZE 10;

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_HISTOGRAMS, DBA_HISTOGRAMS, and ALL_HISTOGRAMS.

Clusters

For an indexed cluster, Oracle7 collects the average number of data blocks taken up by a single cluster key value and all of its rows. For a hash clusters, Oracle7 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 .

Example I

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

ANALYZE TABLE cust_history

 	ESTIMATE STATISTICS

Deleting Statistics

With the DELETE STATISTICS option of the ANALYZE command, you can remove existing statistics about an object from the data dictionary. You may want to remove statistics if you no longer want the Oracle7 optimizer to use them.

When you use the DELETE STATISTICS option on a table, Oracle7 also automatically removes statistics for all the table's indexes. When you use the DELETE STATISTICS option on a cluster, Oracle7 also automatically removes statistics for all the cluster's tables and all their indexes, including the cluster index.

Example II

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

ANALYZE TABLE cust_history

 	DELETE STATISTICS

Validating Structures

With the VALIDATE STRUCTURE option of the ANALYZE command, you can verify the integrity of the structure of an index, table, or cluster. If Oracle7 successfully validates the structure, a message confirming its validation is returned to you. If Oracle7 encounters corruption in the structure of the object, an error message is returned to you. In this case, drop and recreate the object.

Since the validating the structure of a object prevents SELECT, INSERT, UPDATE, and DELETE statements from concurrently accessing the object, do not use this option on the tables, clusters, and indexes of your production applications during periods of high database activity.

Indexes

For an index, the VALIDATE STRUCTURE option verifies the integrity of each data block in the index and checks for block corruption. Note that this option 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.

When you use the VALIDATE STRUCTURE option on an index, Oracle7 also collects statistics about the index and stores them in the data dictionary view INDEX_STATS. Oracle7 overwrites any existing statistics about previously validated indexes. At any time, INDEX_STATS can contain only one row describing only one index. The INDEX_STATS view is described in the Oracle7 Server Reference.

The statistics collected by this option are not used by the Oracle7 optimizer. Do not confuse these statistics with the statistics collected by the COMPUTE STATISTICS and ESTIMATE STATISTICS options.

Example III

The following statement validates the structure of the index PARTS_INDEX:

ANALYZE INDEX parts_index

 	VALIDATE STRUCTURE

Tables

For a table, the VALIDATE STRUCTURE option verifies the integrity of each of the table's data blocks and rows. You can use the CASCADE option to also validate the structure of all indexes on the table and to perform cross-referencing between the table and each of its indexes. For each index, the cross-referencing involves the following validations:

Each value of the tables' indexed column must match the indexed column value of an index entry. The matching index entry must also identify the row in the table by the correct ROWID.

Each entry in the index identifies a row in the table. The indexed column value in the index entry must match that of the identified row.

Example IV

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

ANALYZE TABLE emp

 	VALIDATE STRUCTURE CASCADE

Clusters

For a cluster, the VALIDATE STRUCTURE option verifies the integrity of each row in the cluster and automatically validates the structure of each of the cluster's tables. You can use the CASCADE option to also validate the structure of all indexes on the cluster's tables, including the cluster index.

Example V

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

Listing Chained Rows

With the LIST option of the ANALYZE command, you can collect information about the migrated and chained rows in a table or cluster. A migrated row is one that has been moved from one data block to another. For example, Oracle7 migrates a row in a cluster if its cluster key value is updated. A chained row is one that is contained in more than one data block. For example, Oracle7 chains a row of a table or cluster if the row is too long to fit in a single data block. Migrated and chained rows may cause excessive I/O. You may want to identify such rows to eliminate them. For information on eliminating migrated and chained rows, see Oracle7 Server Tuning.

You can use the INTO clause to specify an output table into which Oracle7 places this information. The definition of a sample output table CHAINED_ROWS is provided in a SQL script available on your distribution media. Your list table must have the same column names, types, and sizes as the CHAINED_ROWS table. On many operating systems, the name of this script is UTLCHAIN.SQL. The actual name and location of this script may vary depending on your operating system.

Example VI

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                0000346A.000C.0003 15-MAR-93

ANALYZE

Purpose

Prerequisites

Syntax

Keywords and Parameters

Collecting Statistics

Indexes

Tables

Columns

Clusters

Deleting Statistics

Validating Structures

Indexes

Tables

Clusters

Listing Chained Rows

Related Topics