RE: Global index on partitioned table
- From: "Sheehan, Jeremy" <JEREMY.SHEEHAN@xxxxxxxxxxxxxxxxx>
- To: Adam Musch <ahmusch@xxxxxxxxx>, "hemantkchitale@xxxxxxxxx" <hemantkchitale@xxxxxxxxx>
- Date: Mon, 22 Nov 2010 13:30:25 -0500
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, 2010 1:14 PM
To: hemantkchitale@xxxxxxxxx
Cc: Sheehan, Jeremy; oracle-l-freelists
Subject: Re: Global index on partitioned table
It should generate less undo and redo - it won't generate any for the
table rows - but it will have to generate undo and redo for all of the
index entries. Depending on the number of indexes and scope of those
indexes, you may not see a significant decrease in UNDO and REDO
volume. Also, I don't know how your purge process ran before (I know
I'd have been looping, deleting the first N records in the window and
committing), but now that's exactly one transaction, so all that UNDO
and REDO is in a single rollback segment. And if you run out of UNDO,
the entire transaction fails, and rolls back. (It could leave the
global in an unusable state, and you have to rebuild it yourself. I
don't know.)
If partitioning the table, consider making as many indexes as possible
LOCAL indexes to avoid the maintenance on global indexes, especially
if that maintenance is causing you problems in the first place. You
may need to make application changes as well - implementing
partitioning on a table doesn't simply set GO_FASTER=TRUE.
On Mon, Nov 22, 2010 at 9:54 AM, Hemant K Chitale
<hemantkchitale@xxxxxxxxx> wrote:
> 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@xxxxxxxxxxxxxxxxx> 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
--
Adam Musch
ahmusch@xxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
