oracle analyze原理,Oracle中的Analyze

Purpose

Use the ANALYZE statement to collect statistics, for example, to:

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.

大多数情况, 请用DBMS_STATS

package; ANALYZE 更加适用于:

To use the VALIDATE or LIST CHAINED ROWS clauses

To collect 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, then 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, then you must have the INSERT object privilege on the table into which you list analyzed rowids, or you must have the INSERT ANY TABLE system privilege.

Semantics

schema

Specify the schema containing the table, index, or cluster. If you omit schema, then Oracle Database assumes the table, index, or cluster is in your own schema.

TABLE table

Specify a table to be analyzed. When you analyze a table, the

database 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. When analyzing a table, the database skips all domain indexes marked LOADING or FAILED.partition_extension_clause

Specify the partition or subpartition, or the partition or

subpartition value, on which you want statistics to be gathered. You

cannot use this clause when analyzing clusters.If you specify PARTITION and table is composite-partitioned, then Oracle Database analyzes all the subpartitions within the specified partition.

INDEX index

Specify an index to be analyzed.Oracle Database collects the following statistics for an index.

Statistics marked with an asterisk are always computed exactly. For

conventional indexes, when you compute or estimate statistics, the

statistics appear in the data dictionary views USER_INDEXES, ALL_INDEXES, and DBA_INDEXES in the columns shown in parentheses.

CLUSTERcluster

Specify a cluster to be analyzed. When you collect statistics for a

cluster, Oracle Database also automatically collects the statistics for

all the tables in the cluster and all their indexes, including the

cluster index.For both indexed and hash clusters, the database collects the average number of data blocks taken up by a single cluster key (AVG_BLOCKS_PER_KEY). These statistics appear in the data dictionary views ALL_CLUSTERS, USER_CLUSTERS, and DBA_CLUSTERS.

validation_clauses

The validation clauses let you validate REF values and the structure of the analyzed object.

VALIDATE REF UPDATE Clause

Specify VALIDATE REF UPDATE to validate the REF values in the specified table, check the rowid portion in each REF, compare it with the true rowid, and correct it, if necessary. You can use this clause only when analyzing a table.

SET DANGLING TO NULLSET DANGLING TO NULL sets to null any REF values (whether or not scoped) in the specified table that are found to point to an invalid or nonexistent object.

VALIDATE STRUCTURE

Specify VALIDATE STRUCTURE to validate the

structure of the analyzed object. The statistics collected by this

clause are not used by the Oracle Database optimizer.

For a table, Oracle Database verifies the integrity of each of the

data blocks and rows. For an index-organized table, the database also

generates compression statistics (optimal prefix compression count) for

the primary key index on the table.

For a cluster, Oracle Database automatically validates the structure of the cluster tables.

For a partitioned table, Oracle Database also verifies that each row

belongs to the correct partition. If a row does not collate correctly,

then its rowid is inserted into the INVALID_ROWS table.

For a temporary table, Oracle Database validates the structure of the table and its indexes during the current session.

For an index, Oracle Database 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 clause.

Oracle Database also computes compression statistics (optimal prefix compression count) for all normal indexes.

Oracle Database stores statistics about the index in the data dictionary views INDEX_STATS and INDEX_HISTOGRAM.

If Oracle Database encounters corruption in the structure of the

object, then an error message is returned. In this case, drop and

re-create the object.

INTOThe INTO clause of VALIDATE STRUCTURE

is valid only for partitioned tables. Specify a table into which Oracle

Database lists the rowids of the partitions whose rows do not collate

correctly. If you omit schema,

then the database assumes the list is in your own schema. If you omit

this clause altogether, then the database assumes that the table is

named INVALID_ROWS. The SQL script used to create this table is UTLVALID.SQL.

CASCADESpecify CASCADE

if you want Oracle Database to validate the structure of the indexes

associated with the table or cluster. If you use this clause when

validating a table, then the database also validates the indexes defined

on the table. If you use this clause when validating a cluster, then

the database also validates all the cluster tables indexes, including

the cluster index.

By default, CASCADE performs a COMPLETE validation, which can be resource intensive. Specify FAST if you want the database to check for the existence of corruptions without reporting details about the corruption. If the FAST check finds a corruption, you can then use the CASCADE option without the FAST clause to locate and learn details about it.

If you use this clause to validate an enabled (but previously

disabled) function-based index, then validation errors may result. In

this case, you must rebuild the index.

ONLINE | OFFLINESpecify ONLINE

to enable Oracle Database to run the validation while DML operations

are ongoing within the object. The database reduces the amount of

validation performed to allow for concurrency.

Note:

When you validate the structure of an object ONLINE, Oracle Database does not collect any statistics, as it does when you validate the structure of the object OFFLINE.

Specify OFFLINE, to maximize the amount of validation performed. This setting prevents INSERT, UPDATE, and DELETE statements from concurrently accessing the object during validation but allows queries. This is the default.

Restriction on ONLINEYou cannot specify ONLINE when analyzing a cluster.

LIST CHAINED ROWS

LIST CHAINED ROWS lets you

identify migrated and chained rows of the analyzed table or cluster. You

cannot use this clause when analyzing an index.

In the INTO clause, specify a table into which Oracle Database lists the migrated and chained rows. If you omit schema,

then the database assumes the chained-rows table is in your own schema.

If you omit this clause altogether, then the database assumes that the

table is named CHAINED_ROWS. The chained-rows 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, then it must follow the format prescribed by one of these two scripts.

If you are analyzing index-organized tables based on primary keys

(rather than universal rowids), then 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.

DELETE STATISTICS

Specify DELETE STATISTICS to delete any

statistics about the analyzed object that are currently stored in the

data dictionary. Use this statement when you no longer want Oracle

Database to use the statistics.

When you use this clause on a table, the database also automatically

removes statistics for all the indexes defined on the table. When you

use this clause on a cluster, the database also automatically removes

statistics for all the cluster tables and all their indexes, including

the cluster index.

Specify SYSTEM if you want Oracle Database to delete only system (not user-defined) statistics. If you omit SYSTEM,

and if user-defined column or index statistics were collected for an

object, then the database also removes the user-defined statistics by

invoking the statistics deletion function specified in the statistics

type that was used to collect the statistics.

Examples

Deleting Statistics: ExampleThe following statement deletes statistics about the sample table oe.orders and all its indexes from the data dictionary:

ANALYZE TABLE orders DELETE STATISTICS;

Analyzing an Index: ExampleThe following statement validates the structure of the sample index oe.inv_product_ix:

ANALYZE INDEX inv_product_ix VALIDATE STRUCTURE;

Validating a Table: ExampleThe following statement analyzes the sample table hr.employees and all of its indexes:

ANALYZE TABLE employees VALIDATE STRUCTURE CASCADE;

For a table, the VALIDATE REF UPDATE clause verifies the REF values 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, then the REF is updated so that the rowid portion is correct.

The following statement validates the REF values in the sample table oe.customers:

ANALYZE TABLE customers VALIDATE REF UPDATE;

The following statement validates the structure of the sample table oe.customers while allowing simultaneous DML:

ANALYZE TABLE customers VALIDATE STRUCTURE ONLINE;

Analyzing a Cluster: ExampleThe following statement analyzes the personnel cluster , all of its tables, and all of their indexes, including the cluster index:

ANALYZE CLUSTER personnel

VALIDATE STRUCTURE CASCADE;

Listing Chained Rows: ExampleThe following statement collects information about all the chained rows in the table orders:

ANALYZE TABLE orders

LIST CHAINED ROWS INTO chained_rows;

The preceding statement places the information into the table chained_rows. You can then examine the rows with this query (no rows will be returned if the table contains no chained rows):

SELECT owner_name, table_name, head_rowid, analyze_timestamp

FROM chained_rows

ORDER BY owner_name, table_name, head_rowid, analyze_timestamp;

OWNER_NAME TABLE_NAME     HEAD_ROWID     ANALYZE_TIMESTAMP

---------- ---------- ------------------ -----------------

OE           ORDERS   AAAAZzAABAAABrXAAA 25-SEP-2000