Global index on partitioned table
- From: "Sheehan, Jeremy" <JEREMY.SHEEHAN@xxxxxxxxxxxxxxxxx>
- To: oracle-l-freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 22 Nov 2010 10:43:07 -0500
Hello listers,
Got a question about Global Indexes on partitioned tables.
We have a monster table (400+ GB) and we're now converting it to be partitioned
(YAY - Been a work in progress and a nightmare to work with the weekly
archiving).
Our weekly archive process called to delete 5 days worth of data, but each day
contained 220mm records and was approximately 10-14 GB in size. We're setup to
handle this, but we always had problems with archivelog backups on Saturdays.
Long story short, we're converting this table to partitioned so we can avoid
the problem with the delete and archivelogs, but it still leave the issue with
rebuilding the global indexes on the table.
I came across something in 9i documentation and wanted to see if anyone has
used this before. Does the below statement just rebuild the global index or
does it make it so we can truncate the partition and have the global index not
marked as invalid? Let me know if anyone has used this before!
Method 3:
Specify UPDATE GLOBAL INDEXES in the ALTER TABLE statement. This causes the
global index to be truncated at the time the partition is truncated.
ALTER TABLE sales TRUNCATE PARTITION dec98
UPDATE GLOBAL INDEXES;
Thanks in advance!
Jeremy
Other related posts:
