Analyse takes a long time

Database consists of about 8 tables, currently updated daily. An ANALYSE COMPUTE for the schema is run twice weekly but now takes over 24 hours to complete. (I tried using OEM on a 9.2 box with DBMS_STATS but this took nearly 48 hours)

The main table has 25 million rows (rows about 50,000 per day) with 7 indexes and is partitioned. There is an isolated table with nearly 70 million rows and 3 indices. How can I speed this up a bit?

The database is 816 (moving to 9.2). Can I flag to just analyse the partitions with new data in and if so how? (I know I can specify the partitions to analyse. How can I, for example, always analyse the latest partition?) Does MONITORING take up lots of disk space?

Maybe there is a better way to get data out. I am thinking of keeping data in a small database for the reporting period and then bulk moving to this larger database once a month. The ANALYSE then is done less frequently.

The data is static once added to the database. Received on Tue Jul 30 2002 - 09:50:34 CDT