We're running ANALYZE against all our tables and against the table's indexed columns every night. I know you shouldn't need to be we've been told to by Oracle.
Commands used:
ANALYZE TABLE XXXXX COMPUTE STATISTICS;
ANALYZE TABLE XXXXX COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;
Our problem relates to a table that doesn't seem to be correctly analyzed. After it is analyzed it doesn't seem to want to use an index when joining to another table. We have to run the indexed columns analyze command again. I have tried running individual analyze command for each index in the offending table then testing the result using explain plan but I have discovered nothing unusual. If I use the ANALYZE TABLE XXXXX COMPUTE STATISTICS FOR ALL INDEXED COLUMNS command the problem is resolved.
Questions:
1)Does anyone have any insight into why we have to run this command twice to get the correct result?
2)Why doen't running the individual commands for all the indexes in that table have the same affect and running the all emcompassing command? What does the ALL INDEXED COLUMNS command do that the individual one don't?
DATABASE DETAILS
COMPAQ DATABASE SERVER running Tru64
DB 8.1.7.0
The database is taken down each night and on startup the a[i]
Long postings are being truncated to ~1 kB at this time.