how do we analyse a table

Posted on 2001-07-06
Last Modified: 2007-12-19
how do we analyse a table for looking at the statistics and what is the syntax on sql prompt.
Question by:shikhadh

Expert Comment

ID: 6258676
you should check your documentation for ANALYZE TABLE and ANALYZE INDEX statements, everything is explained thoroughly.

I add the examples section for the documentation :

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, 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.

Example I
 The following statement calculates statistics for a scalar object attribute:


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 Oracle8 Tuning.

The following sections list the statistics for that are collected for indexes, tables, columns, and clusters. The statistics marked with asterisks (*) are always computed exactly.

Indexes :
For an index, Oracle 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 the rows are about the indexed values)
Index statistics appear in the data dictionary views USER_INDEXES, ALL_INDEXES, and DBA_INDEXES.

Tables :
For a table, Oracle 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
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, in contrast, 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.

Oracle collects the following column statistics:

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.

Histograms are persistent objects, so there is a maintenance and space cost for using them. You should compute histograms only 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 command. For example, if you want to create a 10-band histogram on the SAL column of the EMP table, issue the following statement:


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


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, 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 II
The following statement estimates statistics for the CUST_HISTORY table and all of its indexes:

ANALYZE TABLE cust_history

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.

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

ANALYZE TABLE cust_history

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 re-create the object.

Validating the structure of a object prevents SELECT, INSERT, UPDATE, and DELETE statements from concurrently accessing the object. Therefore, 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 with the CASCADE option.

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 INDEX_STATS view is described in the Oracle8 Reference.

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 I
The following statement validates the structure of the index PARTS_INDEX:

ANALYZE INDEX parts_index

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 as well 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 table'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 must identify a row in the table. The indexed column value in the index entry must match that of the identified row.

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


 For a table, the VALIDATE REF UPDATE option 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.

Example III
 The following statement validates the REFs in the EMP table:


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 as well, including the cluster index.

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


Partitioned Tables :
There is no rule-based optimizer for partitioned tables, so it is important to analyze partitioned tables and indexes regularly.

For a partitioned table, the VALIDATE STRUCTURE option verifies each row in the partition to verify whether the column values of the partitioning columns collate less than the partition bound of that partition and greater than the partition bound of the previous partition (except the first partition). If the row does not collate correctly, the ROWID is inserted into the INVALID_ROWS table.

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 to eliminate them. For information on eliminating migrated and chained rows, see Oracle8 Tuning.

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 depends on your operating system.

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

ANALYZE TABLE order_hist

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

    FROM cr
----------  ----------  ------------  ------------------ ---------
SCOTT       ORDER_HIST                AAAAZzAABAAABrXAAA 15-MAR-96
Related Topics
Oracle8 Tuning

Best Regards
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6353676
just a friendly reminder...

Author Comment

ID: 6358249
but how do we write a command analyse ....i have tried to execute the command on sql prompt it says unknown command??
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now


Expert Comment

ID: 7038071
Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.



** Mindphaser - Community Support Moderator **

P.S.  Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.
LVL 49

Expert Comment

ID: 7052798
Lacking timely response from shikhadh or contributing experts, but seeing that there is some useful info here, I recommend:
    Refund points and save as a 0-pt PAQ.

DanRollins -- EE database cleanup volunteer

Accepted Solution

Mindphaser earned 0 total points
ID: 7052846
Points refunded and moved to PAQ

** Mindphaser - Community Support Moderator **

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle SQL - Query help 7 80
how to double quote a string for an inline sql statement. 8 95
subtr returning incorrect value 8 70
Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question