We drop and recreate many indexes on our large partitioned fact table in our data warehouse nightly. The amount of data in the table increases gradually with time but there aren't huge fluctuations in the amount of data in the partitions from day to day.
The problem is that the amount of time that it takes to recreate the indexes varies widely. From 40 minutes to almost three hours. I am inclined to believe that this is a scheduling issue related to the time of the day and the day of the week. I think we may be experiencing competition from other batch jobs from other applications. One application, in particular, is sort of a black box because it is not on Oracle and it belongs to people who reside in a remote site. However, I want to make sure I've considered all of the possibilities. What factors might influence how long it takes to rebuild indexes on existing partitions? If I just did some maintenance to split out some data for a new time period into a new partition, might that make a difference in how long it takes to build the indexes? These indexes are all locally partitioned indexes. Thanks for any insight you can lend. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
