| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: analyze index
This is Oracle Corporation's Documentation for ANALYZE Command..
Regards
ANALYZE
Purpose
To perform one of these 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 ORACLE in DBMS MAC mode, your DBMS label must match
the creation label of the object to be analyzed or you must satisfy one of
these 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 noncomparable, 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 ORACLE 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 you omit schema, ORACLE assumes the index is in your own schema.
TABLE identifies a table to be analyzed. If you omit schema, ORACLE assumes the table is in your own schema. When you collect statistics for a table, ORACLE also automatically collects the statistics for each of the table's indexes.
CLUSTER identifies a cluster to be analyzed. If you omit schema, ORACLE assumes the cluster is in your own schema. 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.
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 ORACLE samples to estimate statistics. If you omit this parameter, ORACLE samples 1064 rows. 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.
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, ORACLE 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, ORACLE also validates the table's indexes. If you use this option when validating a cluster, ORACLE 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 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.
Collecting Statistics
You can collect statistics about the physical storage characteristicsand
data distribution of an index, table, or cluster and store themin the data
dictionary. You can use the COMPUTE STATISTICS or ESTIMATE STATISTICS option
to cause ORACLE to compute or estimate these 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, ORACLE computes the statistic
exactly.
If the data dictionary already contains statistics for the analyzed object,
ORACLE updates the existing statistics with the new ones.
The 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 Chapter 13 "The Optimizer" of the ORACLE7 Server Concepts Manual.
The following sections list the statistics for indexes, tables, and clusters.
Indexes
For an index, ORACLE collects these 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.
These statistics appear in the data dictionary views USER_INDEXES,
ALL_INDEXES, and DBA_INDEXES.
Tables
For a table, ORACLE collects these 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
number of distinct values for each column
maximum* and minimum* values for each column (Note that these are the second
greatest and second least values)
The statistics marked with asterisks (*) are always computed.
These statistics all appear in the data dictionary views USER_TABLES,
ALL_TABLES, and DBA_TABLES, except for the number of distinct values and the
maximum and minimum values for each column which appear in USER_TAB_COLUMNS,
ALL_TAB_COLUMNS, and DBA_TAB_COLUMNS.
Clusters
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
clusters, 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.
Example I
This 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 ORACLE optimizer to use them.
When you use the DELETE STATISTICS option on a table, ORACLE also
automatically removes statistics for all the table's indexes. When you use
the DELETE STATISTICS option on a cluster, ORACLE also automatically removes
statistics for all the cluster's tables and all their indexes, including the
cluster index.
Example II
This 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 ORACLE
successfully validates the structure, a message confirming its validation is
returned to you. If ORACLE 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, ORACLE also collects
statistics about the index and stores them in the data dictionary view
INDEX_STATS. ORACLE overwrites any existing statistics about previously
validated indexes. At any time, INDEX_STATS can contain only one row
describing only one index.
The statistics collected by this option are not used by the ORACLE optimizer. Do not confuse these statistics with the statistics collected by the COMPUTE STATISTICS and ESTIMATE STATISTICS options.
Example III
This 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 these validations:
Each value of the tables's 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 III
This 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 IV
This 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, ORACLE
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,
ORACLE 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 in order to eliminate them. For information
on eliminating migrated and chained rows, see Chapter 22 "Tuning I/O" of the
ORACLE7 Server Administrator's Guide.
You can use the INTO clause to specify an output table into which ORACLE
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 V
This statement collects information about all the chained rows of the table
ORDER_HIST:
ANALYZE TABLE order_hist
LIST CHAINED ROWS INTO cr
This statement places the information into the table CR.
You can then examine these rows with this query:
SELECT *
FROM cr
OWNER_NAME TABLE_NAME CLUSTER_NAME HEAD_ROWID TIMESTAMP
daud11_at_hotmail.com wrote in message <71pdhp$ate$1_at_nnrp1.dejanews.com>...
>Hi
>
>I found out that after doing 'analyze index <index-name> validate
structure'
>I can get some statistics on the index from index_stats. However, I do not
>know much to interpret what the numbers mean. In particular, I would like
to
>know when is it necessary to rebuild an index.
>
>Many thanks
>Daud
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
begin 666 Yassir Khogaly.vcf
M0D5'24XZ5D-!4D0-"E9%4E-)3TXZ,BXQ#0I..DMH;V=A;'D[66%S<VER#0I&
M3CI987-S:7(@2VAO9V%L>0T*5$E43$4Z4V5N:6]R($]R86-L92!$0D$-"E1%
M3#M(3TU%.U9/24-%.BLT-"@P*3$X,2 T-C T,#0R#0I414P[0T5,3#M63TE#
M13HK-#0T,3$Y,#8W-S8-"D%$4CM(3TU%.CL[.SM+96YT.SM%;F=L86YD#0I,
M04)%3#M(3TU%.T5.0T]$24Y'/5%53U1%1"U04DE.5$%"3$4Z2V5N=#TP1#TP
M045N9VQA;F0-"E523#IH='1P.B\O=W=W+FMH;V=A;'DN9G)E97-E<G9E+F-O
M+G5K#0I54DPZ:'1T<#HO+W=W=RYA965U+F]R9RYU:PT*14U!24P[4%)%1CM)
M3E1%4DY%5#IY87-S:7) :VAO9V%L>2YF<F5E<V5R=F4N8V\N=6L-"E)%5CHQ
<.3DX,3$P-%0Q,30S,C5:#0I%3D0Z5D-!4D0-"@``
`
end
Received on Wed Nov 04 1998 - 05:43:26 CST
 |
 |