DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2001

    Partition Exhange?

    Hi kids!

    Anyone out there have direct experience with exchanging partitions?

    Oracle 10.2

    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
    Ft. Lauderdale, FL
    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
    Bangalore ( India )
    But only that global indexes need rebuild.. or updated while exchange itself.. this may eat some time.. (but depends on how many u have)..


    "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
    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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.