$show=/label

Adding Partition To Existing Table In Oracle

SHARE:

Learn How To Add Partition To Existing Table In Oracle. New changes to partition in Oracle 12c. partitioned table using the ALTER TABLE ... MODIFY command in 3 ways. 1) Basic offline operation 2) Online operation 3) Online operation with modification of index partitioning

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.

Adding Partition To Existing Table In Oracle


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

COMMENTS

BLOGGER

About Us

Author: Venkatesh - I love to learn and share the technical stuff.
Name

accumulo,1,ActiveMQ,2,Adsense,1,API,37,ArrayList,16,Arrays,7,Bean Creation,3,Bean Scopes,1,BiConsumer,1,Blogger Tips,1,Books,1,C Programming,1,Collection,5,Collections,22,Collector,1,Command Line,1,Compile Errors,1,Configurations,7,Constants,1,Control Statements,8,Conversions,6,Core Java,81,Corona India,1,Create,2,CSS,1,Date,3,Date Time API,4,Dictionary,1,Difference,1,Download,1,Eclipse,2,Efficiently,1,Error,1,Errors,1,Exception,1,Exceptions,3,Fast,1,Files,9,Float,1,Font,1,Form,1,Freshers,1,Function,3,Functional Interface,2,Garbage Collector,1,Generics,4,Git,4,Grant,1,Grep,1,HashMap,1,HomeBrew,2,HTML,2,HttpClient,2,Immutable,1,Installation,1,Interview Questions,5,Iterate,2,Jackson API,3,Java,29,Java 10,1,Java 11,5,Java 12,5,Java 13,2,Java 14,2,Java 8,66,Java 8 Difference,2,Java 8 Stream Conversions,2,java 8 Stream Examples,3,Java 9,1,Java Conversions,11,Java Design Patterns,1,Java Files,1,Java Program,2,Java Programs,65,java.lang,5,java.util. function,1,jQuery,1,Kotlin,10,Kotlin Conversions,3,Kotlin Programs,6,Lambda,1,lang,29,Leap Year,1,live updates,1,Logging,1,Mac OS,2,Math,1,Maven,1,Method References,1,Mockito,1,MongoDB,3,New Features,1,Operations,1,Optional,6,Oracle,5,Oracle 18C,1,Partition,1,Patterns,1,Programs,1,Property,1,Python,2,Quarkus,1,Read,1,Real Time,1,Recursion,2,Remove,2,Rest API,1,Schedules,1,Serialization,1,Servlet,1,Sort,1,Sorting Techniques,8,Spring,2,Spring Boot,23,Spring Email,1,Spring MVC,1,Streams,21,String,58,String Programs,9,String Revese,1,Swing,1,System,1,Tags,1,Threads,10,Tomcat,1,Tomcat 8,1,Troubleshoot,16,Unix,2,Updates,3,util,5,While Loop,1,
ltr
item
JavaProgramTo.com: Adding Partition To Existing Table In Oracle
Adding Partition To Existing Table In Oracle
Learn How To Add Partition To Existing Table In Oracle. New changes to partition in Oracle 12c. partitioned table using the ALTER TABLE ... MODIFY command in 3 ways. 1) Basic offline operation 2) Online operation 3) Online operation with modification of index partitioning
https://3.bp.blogspot.com/-J-Fk8MhB0AI/XK7bgdvLrTI/AAAAAAAABU0/mMpWnE_ItO4WekDAIU7WjwawWkE4id1eQCLcBGAs/s640/Adding%2BPartition%2BTo%2BExisting%2BTable%2BIn%2BOracle.PNG
https://3.bp.blogspot.com/-J-Fk8MhB0AI/XK7bgdvLrTI/AAAAAAAABU0/mMpWnE_ItO4WekDAIU7WjwawWkE4id1eQCLcBGAs/s72-c/Adding%2BPartition%2BTo%2BExisting%2BTable%2BIn%2BOracle.PNG
JavaProgramTo.com
https://www.javaprogramto.com/2019/04/adding-partition-to-existing-table-oracle.html
https://www.javaprogramto.com/
https://www.javaprogramto.com/
https://www.javaprogramto.com/2019/04/adding-partition-to-existing-table-oracle.html
true
3124782013468838591
UTF-8
Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share to a social network STEP 2: Click the link on your social network Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy Table of Content