Oracle: Analyze Table or Index

 Analyze command 

We use "analyze" command to store fresh statistics of table or index . We can do the same DBMS_UTILITY package as well. Statistics created by "analyze" command or by DBMS_UTILITY package are used by CBO (Cost based Optimizer).

Some time same query taking huge time for execution, at that time we have to check that table or index used in query have latest statistics in data dictionary table or not, if not then we have to analyze table and index and re-execute the query .   

Below are commands syntax for same

Syntax:
For Table

analyze table <table_Name> {compute|estimate|delete) statistics;

For Index

analyze index <index_Name> {compute|estimate|delete) statistics ;

For Cluster

analyze cluster <cluster_Name> {compute|estimate|delete) statistics ;

Examples:-

DBMS_UTILITY package - If we want schema level or database level then we use this utility while we can use this utility for single table and single index as well

exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE');

exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_rows => 1000);

exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_percent => 25);

exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','DELETE');

exec DBMS_UTILITY.ANALYZE_DATABASE('COMPUTE');