Oracle 9i で analyze してみた

emp表を analyze する。

SQL> analyze table emp compute statistics;

Table analyzed.

emp表にはられているインデックスを調べる。

SQL> select index_name from user_indexes where table_name ='EMP';

INDEX_NAME
------------------------------
PK_EMP

インデックスも analyze する。

SQL> analyze index PK_EMP compute statistics;

Index analyzed.

Oracle Corporation strongly recommends that you use the DBMS_STATS package rather than ANALYZE to collect optimizer statistics. That package lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. Further, the cost-based optimizer, which depends upon statistics, will eventually use only statistics that have been collected by DBMS_STATS. See Oracle9i Supplied PL/SQL Packages and Types Reference for more information on this package.

However, you must use the ANALYZE statement (rather than DBMS_STATS) for statistics collection not related to the cost-based optimizer, such as:

To use the VALIDATE or LIST CHAINED ROWS clauses
To collect information on freelist blocks

General Management of Schema Objects

9i のときから DBMS_STATS package が推奨されていたのか。知らなかった。