Analyze This

In the good old days you analyzed a table using a statement along the lines of: -

ANALYZE TABLE tablename COMPUTE STATISTICS;

With the advent of Oracle 8i the DBMS_STATS utility was introduced to gather object statistics. This is Oracles preffered method of gathering the information.

Using it the equivalent of the above analyze statement would be: -

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SYSADM',TABNAME=>'tablename',CASCADE=>TRUE);

This should be executed after the table you need to analyze has been populated.

To cut back on the time it takes to execute, you can limit the number of rows it uses in its stats gathering by adding estimate_percent => nn (where nn is a number between 1 and 100) this will only use the given percentage of the total rows in the table when performing its anaysis.

e.g.

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SYSADM',TABNAME=>'tablename',CASCADE=>TRUE, estimate_percent => 20);

Will only use 20% of the tables rows. The higher the percentage, the better the analysis, but the longer it will take. When used Oracle randomly selects the rows it uses from the table to make them more representative.

N.B. This statement doesn't work from Oracle Developer (I've not tried it from Toad). As such execute it from SQL Plus instead.