What is ANALYZE do?

[AdusumalliGopikumar]

How we can use Analyze to know our table statics,,


i mean explain the steps involved.

[amitpatel66]

http://www-eleves-isia.cma.fr/documentation/OracleDoc/ANALYZE.html

[debasisdas]

syntax for analysing a table is :

ANALYZE TABLE emp ESTIMATE STATISTICS;

Analyze statement generate statistaics that help CBO to estimate the cost of execution plan.

The full syntax (not using DBMS package) is

ANALYZE TABLE xxxxx ESTIMATE STATISTICS SAMPLE 20 PERCENT;

In case you do not mention the sample size, Oracle will only use 1064 rows to sample, which might be very small for a large table.

Once you start analyzing some of your tables you really ought to analyze all of them. Otherwise you will find yourself in a situation in which you join several tables one of which has stats and the others lack them: because one of the tables has been analyzed Oracle will estimate (i.e. make up) statistics for the other tables. This is not good idea.

[debasisdas]

Validating the structure

analyze table table-name validate structure ;
analyze table table-name validate structure online;
analyze table table-name validate structure cascade;
analyze table table-name validate structure cascade online;

This command validates the integrity of the db blocks of the underlying table.
If cascade is specified, the command also checks the index
ORA-01499 is thrown if the validation fails.
analyze table requires a temporary segment if the statement cannot be completed in memory.


Using ANALYZE command is little bit older now. If you are using oracle >8i then you have new package called dbms_stats.

To analyze schema use

dbms_stats.gather_schema_stats

For table use the following

dbms_stats.gather_table_stats

To make use of CBO for your queries, you need to give upto date statistics of your tables,indexes to optimizer. By collecting them, optimizer gives the best execution plan for the query.