Re: RE: Global index on partitioned table
- From: Hemant K Chitale <hemantkchitale@xxxxxxxxx>
- To: "Sheehan, Jeremy" <JEREMY.SHEEHAN@xxxxxxxxxxxxxxxxx>
- Date: Tue, 23 Nov 2010 07:50:01 +0800
If you are issuing DELETEs *before* the TRUNCATE, then the UPDATE GLOBAL
INDEXES advantage is in preventing the indexes from becoming INVALID. The
undo and redo overhead was suffered during the DELETE, not in the TRUNCATE.
Hemant K Chitale
http://hemantoracledba.blogspot.com
sent from my smartphone
On Nov 23, 2010 2:30 AM, "Sheehan, Jeremy" <JEREMY.SHEEHAN@xxxxxxxxxxxxxxxxx>
wrote:
Right now it's using a simple statement for the deletes. NO loops or
anything fancy.
Delete from table where cob_date > date
It's a HUGE transaction as it's deleting about a week's worth of data at a
time. We normally don't have any problems with Undo, just the archivelog
backups. They fill up the backup directory real quick.
We asked to have the indexes partitioned, but the developers have been using
hints that reference the indexes needed so the transactions will go from
hours to minutes. The plan is to eventually get everything onto local
indexes so we can avoid creating so much undo/redo and just truncate the
partitions and let that be that.
I was planning on running some additional tests with this, but I wanted to
see if anyone had any experience with the following. That's all.
ALTER TABLE sales TRUNCATE PARTITION dec98 UPDATE GLOBAL INDEXES;
Jeremy
-----Original Message-----
From: Adam Musch [mailto:ahmusch@xxxxxxxxx]
Sent: Monday, November 22,...
Other related posts:
