• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 556
  • Last Modified:

Partition creation for a existing table

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

Swadhin Ray
Swadhin Ray
  • 2
  • 2
1 Solution
slightwv (䄆 Netminder) Commented:
>>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.
Swadhin RaySenior Technical Engineer Author Commented:
I will check and close this soon.
slightwv (䄆 Netminder) Commented:
Leave it open as long as you need to.  Maybe another Expert will have ideas on a time estimate.
Swadhin RaySenior Technical Engineer Author Commented:

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now