Re: Global index on partitioned table

From: Hemant K Chitale <hemantkchitale_at_gmail.com>
Date: Mon, 22 Nov 2010 23:54:29 +0800
Message-ID: <AANLkTikFfJcOhruGn38wi6G85BW=0MgQ65K4DWx-=iBS_at_mail.gmail.com>

The Global Index won't be "truncated" but Oracle will "delete" all the entries corresponding to that partition. This will generate undo and redo.

Hemant K Chitale
http://hemantoracledba.blogspot.com
sent from my smartphone

On Nov 22, 2010 11:49 PM, "Sheehan, Jeremy" < JEREMY.SHEEHAN_at_nexteraenergy.com> wrote:

 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

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Nov 22 2010 - 09:54:29 CST