Adding Partition To Existing Table In Oracle

How do I alter my existing table to create a range partition in Oracle

Creating partition is on a table is very important when you want to see performance improvement especially on DATE columns.

In this post, We will learn how to alter existing table with partition.

What is Partition in Oracle:

Oracle Partitioning allows tables and indexes to be partitioned into smaller, more manageable units, providing database administrators with the ability to pursue a "divide and conquer" approach to data management. ... That table could be range- partitioned so that each partition contains one day of data.

Creating Partitioning using range partition:

CREATE TABLE table_name
   (column_name DATE)
 PARTITION BY RANGE (column_name)
(PARTITION partition_name VALUES LESS THAN (value));

Example:

CREATE TABLE STUDENT
  (
    STUDENT_ID   NUMBER(22,0) NOT NULL,
    STUDENT_NAME VARCHAR2(64) NOT NULL,
    STUDENT_JOINING_DATE DATE NOT NULL
  )
  PARTITION BY RANGE
  (
    STUDENT_JOINING_DATE
  )
  (
    PARTITION JOINING_20190701 VALUES LESS THAN (TO_DATE('2019-07-01', 'YYYY-MM-DD')),
    PARTITION JOINING_20200101 VALUES LESS THAN (TO_DATE('2020-01-01', 'YYYY-MM-DD')),
    PARTITION JOINING_20200701 VALUES LESS THAN (TO_DATE('2020-07-01', 'YYYY-MM-DD')),
    PARTITION JOINING_20210101 VALUES LESS THAN (TO_DATE('2021-01-01', 'YYYY-MM-DD')),
    PARTITION JOINING_20210701 VALUES LESS THAN (TO_DATE('2021-07-01', 'YYYY-MM-DD')),
    PARTITION JOINING_20220101 VALUES LESS THAN (TO_DATE('2022-01-01', 'YYYY-MM-DD')),
    PARTITION JOINING_20220701 VALUES LESS THAN (TO_DATE('2022-07-01', 'YYYY-MM-DD')),
    PARTITION JOINING_20230101 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),
    PARTITION JOINING_20230701 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
    PARTITION JOINING_20240101 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION JOINING_20240701 VALUES LESS THAN (TO_DATE('2024-07-01', 'YYYY-MM-DD')),
    PARTITION JOINING_20250101 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
    PARTITION JOINING_MAX VALUES LESS THAN (MAXVALUE)
  );

Now this new table is deployed in production enviornment and it is there in prod for 1 year. This table has now10 million records in it. Later now, we need to add a partition on STUDENT_JOINING_DATE column. We will see now how to handle this case.

Is it possible to add a Partion on existing table:

Well, one way or another, you'll have to recreate the table -- there is quite simply NO WAY AROUND that. Think about it -- you have an existing table full of data. Rows with jan are next to feb are next to mar. The entire table needs to be rewritten.

This is not possible in Oracle database.

We should do the following.

1) export the table
2) create a new empty table that has the partition definition on it
3) import the table with IGNORE=Y

You must take care of following.

here you would need recreate

1) index
2) grant
3) add constraints
4) add triggers
5) add EVERYTHING you need to make

Where as adding partition to existing table is allowed from oracle 12c release.

Partitioning Enhancements in Oracle Database 12c Release 2 (12.2)

Conversion of a Non-Partitioned Table to a Partitioned Table in Oracle:

We will do the following steps to create a partition on existing table in oracle.

1) Drop table if exists
2) Create table
3) Creating index on partition column
4) Inserting 10000 records into our existing table which is created in step 2

Create and populate a test table. You will need to repeat this between each test.

Drop table if exists:

DROP TABLE Emp ;

Creating table:

CREATE TABLE Emp (
  id           NUMBER,
  name  VARCHAR2(50),
  join_date DATE,
  CONSTRAINT emp_pk PRIMARY KEY (id)
);

Creating index:

CREATE INDEX emp_join_date_idx ON t1(join_date);

Inserting Data:

Here this script inserts 10000 records into Emp table.

INSERT INTO Emp
SELECT level,
       'Name for ' || level,
       ADD_MONTHS(TO_DATE('01-JAN-2019', 'DD-MON-YYYY'), -TRUNC(DBMS_RANDOM.value(1,4)-1)*12)
FROM   dual
CONNECT BY level <= 10000;
COMMIT;

See the data based on the year:

We can see the data is spread across three years.

SELECT join_date, COUNT(*)
FROM   Emp
GROUP BY join_date
ORDER BY 1;

Output:

join_date   COUNT(*)
--------- ----------
01-JAN-15       3340
01-JAN-16       3290
01-JAN-17       3370

Partition an existing Table:

We can convert the table to a partitioned table using the ALTER TABLE ... MODIFY command. Here are some basic examples of this operation. Adding the ONLINE keyword allows the operation to be completed online.

This can be done in 3 ways.

1) Basic offline operation
2) Online operation
3) Online operation with modification of index partitioning

1) Basic offline operation:

ALTER TABLE Emp MODIFY
  PARTITION BY RANGE (join_date) (
    PARTITION Emp_part_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')),
    PARTITION Emp_part_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')),
    PARTITION Emp_part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY'))
  );

2) Online operation

ALTER TABLE Emp MODIFY
  PARTITION BY RANGE (join_date) (
    PARTITION Emp_part_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')),
    PARTITION Emp_part_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')),
    PARTITION Emp_part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY'))
  ) ONLINE;

3) Online operation with modification of index partitioning

ALTER TABLE Emp MODIFY
  PARTITION BY RANGE (join_date) (
    PARTITION Emp_part_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')),
    PARTITION Emp_part_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')),
    PARTITION Emp_part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY'))
  ) ONLINE
  UPDATE INDEXES
  (
    Emp_pk GLOBAL,
    Emp_join_date_idx LOCAL
  );