Re: Composite partitioned table purge causes need for index rebuild
From: vsevolod afanassiev <vsevolod.afanassiev_at_gmail.com>
Date: Mon, 14 Sep 2009 05:11:24 -0700 (PDT)
Message-ID: <9cf06d84-75e1-42f4-ae30-a0d6a1fe32fc_at_a37g2000prf.googlegroups.com>
In my experience this should work without problems. Possible issues:
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.
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. 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.
4. Make sure the is enough space for archivelogs that rebuild will generate (assuming db is in
archivelog mode).
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. Received on Mon Sep 14 2009 - 07:11:24 CDT
Date: Mon, 14 Sep 2009 05:11:24 -0700 (PDT)
Message-ID: <9cf06d84-75e1-42f4-ae30-a0d6a1fe32fc_at_a37g2000prf.googlegroups.com>
In my experience this should work without problems. Possible issues:
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.
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. 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.
4. Make sure the is enough space for archivelogs that rebuild will generate (assuming db is in
archivelog mode).
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. Received on Mon Sep 14 2009 - 07:11:24 CDT