Partition Exhange?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Partition Exhange?

  1. #1
    Join Date
    Jan 2001
    Posts
    3,131

    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.

  2. #2
    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.

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  5. #5
    Join Date
    Jan 2001
    Posts
    3,131
    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
  •  


Click Here to Expand Forum to Full Width