Skip to Main Content
  • Questions
  • Partition existing non-partitioned tables in 12.1.0.2.0

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Mamta.

Asked: September 21, 2020 - 12:01 pm UTC

Answered by: Chris Saxon - Last updated: September 21, 2020 - 12:30 pm UTC

Category: Database Development - Version: 12.1.0.2.0

Viewed 100+ times

You Asked

Which one is recommended in terms of performance(time taken to perform this activity) from below for a table with 18 GB data?
https://oracle-base.com/articles/misc/partitioning-an-existing-table-using-exchange-partition
or
https://oracle-base.com/articles/misc/partitioning-an-existing-table

and we said...

With dbms_redefinition you have to copy the rows from the old table to the new.

Whereas partition exchange is a metadata operation, so is "instant".

So it'll be faster to add partitioning to a table using the exchange method. Assuming you're happy to load all existing rows into one giant partition.

Splitting the existing rows into new partitions will take a while. So overall this method may take as long/longer than dbms_redefintion. You'll have to test on your data to find out.

If you use partition exchange you'll also have to switch the tables over, either by renaming tables, repointing synonyms or similar. You'll need to take an outage to complete this safely.

Dbms_redefinition is designed to be an online process (though it does briefly take an exclusive table lock at the end) so you can leave the application running while this process completes.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database