Partition creation for a existing table

Posted on 2012-08-17
Last Modified: 2012-08-17
Hello Experts,

I have table which have 131 million records. Now I want to add the partition to it, for which I am following the below process, and I want to know :

The data movement will be done while switching the segment from old to new partition , but how much time it will take to complete for 131 million records ?

CREATE TABLE my_table (
  id           NUMBER,
  description  VARCHAR2(50)

-- This table will have 131 million records. 

--Next we create a new partitioned table with a single partition to act as our destination table.
CREATE TABLE my_table_2 (
  id           NUMBER,
  description  VARCHAR2(50)

---Next we switch the original table segment with the partition segment.
ALTER TABLE my_table_2
  EXCHANGE PARTITION my_table_part
  WITH TABLE my_table

 --We can now drop the original table and rename the partitioned table.
DROP TABLE my_table;

RENAME my_table_2 TO my_table;

--Finally we can split the partitioned table into multiple partitions as required and gather new statistics.
ALTER TABLE my_table SPLIT PARTITION my_table_part AT (3)
INTO (PARTITION my_table_part_1,
      PARTITION my_table_part_2);
EXEC DBMS_STATS.gather_table_stats(USER, 'MY_TABLE', cascade => TRUE);

Open in new window

Question by:Swadhin Ray
    LVL 76

    Accepted Solution

    >>how much time it will take to complete for 131 million records ?

    To many variables involved for us to answer.  Disk speed of your system, current workload of the system, CPU, memory, etc...

    I suggest you create a small test case then scale it up and see if the times scale as well.  Then you can estimate the full 131 million yourself.

    Create an example with say, 100,000 records.  Migrate them and get the time.  Then double it, get the time, then double it again and get the time.

    See if the times also double.  Then you can estimate the real time.
    LVL 16

    Author Comment

    by:Swadhin Ray
    I will check and close this soon.
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    Leave it open as long as you need to.  Maybe another Expert will have ideas on a time estimate.
    LVL 16

    Author Closing Comment

    by:Swadhin Ray

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Article by: Swadhin
    From the Oracle SQL Reference ( we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now