Oracle ANALYZE Statement: Old-Style Table and Index Statistics Collection Explained

Oracle ANALYZE Statement: Old-Style Table and Index Statistics Collection

The ANALYZE statement in Oracle Database is a legacy tool used to collect statistics on tables and indexes. These statistics help the database optimizer make decisions about the most efficient way to execute SQL queries. Although Oracle now recommends using the DBMS_STATS package for statistics gathering, understanding ANALYZE is essential for maintaining legacy systems or interpreting older scripts.

Purpose of the ANALYZE Statement

• Collect statistics about tables, indexes, and clusters to help the optimizer generate efficient execution plans.

• Validate structures of database objects.

• Identify migrated and chained rows in tables or clusters.

• **Delete existing statistics** from objects.

Note: The use of ANALYZE for optimizer statistics is now obsolete. Oracle recommends DBMS_STATS for all modern statistics collection tasks

Code Breakdown and Explanation

Sample SQL Command

1analyze table employees compute statistics;
2
3analyze table employees estimate statistics sample 100 rows;
4
5analyze table employees estimate statistics sample 15 percent;
6
7analyze index employees_ind compute statistics;

1. analyze table employees compute statistics;

• Action: Computes exact statistics for the employees table by scanning all rows.
• Usage: Ensures the most accurate statistics but can be resource-intensive on large tables.

2. analyze table employees estimate statistics sample 100 rows;

• Action: Estimates statistics by sampling 100 rows from the employees table.
• Usage: Faster than a full scan, but the accuracy depends on how representative the sample is

3. analyze table employees estimate statistics sample 15 percent;

• Action: Estimates statistics by sampling 15% of the rows in the employees table.
• Usage: Balances speed and accuracy; larger samples yield more reliable statistics but take more time

4. analyze index employees_ind compute statistics;

• Action: Computes exact statistics for the employees_ind index.
• Usage: Helps the optimizer understand index characteristics, such as clustering factor and distinct keys

Key Points and Insights

• Obsolescence: The ANALYZE command is considered obsolete for gathering optimizer statistics. Use DBMS_STATS for modern Oracle databases
• Sampling: Estimating statistics with a sample (by row count or percentage) is faster but less accurate than computing statistics by scanning all data
• Index Statistics: ANALYZE INDEX ... COMPUTE STATISTICS provides important information for the optimizer, such as the number of distinct keys and clustering factor
• Legacy Use: ANALYZE is still used for structure validation and identifying chained rows, but not for routine statistics gathering
• Performance Impact: Computing statistics on large tables can be resource-intensive; sampling is often used to reduce the load, especially for very large datasets

Best Practices

• Use DBMS_STATS:** For current and future Oracle databases, always use the DBMS_STATS package to gather statistics. It offers more options, better performance, and is fully supported by Oracle

• Legacy Support: Only use ANALYZE for specific legacy requirements, such as validating structures or collecting non-optimizer statistics.

References

• Oracle SQL Language Reference: ANALYZE
• Oracle Forums: ANALYZE TABLE command
• Oracle Docs: Optimizing Queries with Statistics
• Ask TOM: What kind of analyze is best
• Stack Overflow: ANALYZE vs GATHER_TABLE_STATS

By understanding the legacy ANALYZE statement and its modern alternatives, you can better maintain older Oracle systems and ensure your database statistics are always up to date for optimal performance.