-
Partition Exhange?
Hi kids!
Anyone out there have direct experience with exchanging partitions?
Oracle 10.2
HP/UX
My apps team has a process where they load the latest monthly partition on a table by doing a select/ insert append on the same table. The problem is that one table in partitcular has 138+ million records, RI, and 17 indexes so this process takes 22 hours.
We kicked around a few ideas yesterday and came up with partition exchange which I will be playing with today if I have the time. Just wanted to see if anyone has used it successfully. I know we use it in house for DW but there is no RI in DW.
I remember when this place was cool.
-
I've used it when I had to do an update that would have resulted in the data moving partitions -- instead I partitioned-exchanged it out, did the update, then exchanged it back into the 'proper' partition, this avoiding a mass row movement.
-
We are using partition exchange as part of an archive and purge process; pretty much in the same way jhmartin does.
It works just fine, fast and smooth.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
But only that global indexes need rebuild.. or updated while exchange itself.. this may eat some time.. (but depends on how many u have)..
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Thanks guys, we are looking into it as a solution.
I remember when this place was cool.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|