Re: Composite partitioned table purge causes need for index rebuild
Date: Mon, 14 Sep 2009 16:07:08 -0700 (PDT)
Message-ID: <8d11913e-a08a-4b26-b28e-66f12cdaf150_at_e4g2000prn.googlegroups.com>
> 1. Oracle needs exclusive lock for very brief period at the beginning
> and end of the rebuild process. What matters is not number of transactions per second
> but transaction durations. If transactions are brief (seconds) them it
> should be fine, if you have any long transactions (hours) then rebuild process
> may get delayed.
The transactions execute in about 10-20ms so this should be no problem for us.
> 2. Index rebuild creates a challenge for space monitoring as during
> rebuilt you have two copies of the index. So make sure that free space in the
> tablespace (if you don't use autoextend) or on the filesystem (if using autoextend) is sufficient.
The newly built index is about 4GB so we need this amount extra in the tablespace. This index is growing at a rate of 3GB per month anyway so the rebuild will inhibit this growth (at least in the medium term).
> 3. During rebuild Oracle creates Index-Organized table to keep track
> of the changes occurring in the base table. From memory it gets created in the default tablespace of
> the user that owns the index. Make sure there is enough free space.
During testing the temporary IOT was only a few megabytes (enough to hold about 5,000 primary key entries).
> 5. I suggest testing the process for various error conditions: make it
> run out of space, kill with alter session and with 'kill -9' etc and check that Oracle
> reverts to the original index.
Excellent idea, thanks. I'll plan to do some failure testing in the staging environment. Received on Mon Sep 14 2009 - 18:07:08 CDT