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.
How to Create User and Grants in Oracle
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.Oracle 18C installation step by step
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 );
Ref
These commands are not working on existing table.
ReplyDeleteError report -
SQL Error: ORA-14006: invalid partition name
14006. 00000 - "invalid partition name"
*Cause: a partition name of the form is
expected but not present.
*Action: enter an appropriate partition name.