DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. Oracle 12c R2: Partitioning Enhancements

Oracle 12c R2: Partitioning Enhancements

This innovation, coupled with Oracle 12c R2, can significantly improve your non-partitioned tables by removing the operational and downtime costs of partitioning.

Emrah Mete user avatar by
Emrah Mete
CORE ·
Jun. 16, 17 · Tutorial
Like (3)
Save
Tweet
Share
5.85K Views

Join the DZone community and get the full member experience.

Join For Free

In this article, I would like to talk about the improvements in partitioning that came with Oracle 12c R2.

Partitioning is one of Oracle's most basic and favorite features. It's a database feature that increases our overall query performance, especially for large tables.

Before Oracle 12c R2, we could create a table physically partitioned only during the creation phase. When we wanted to be partitioned a rapidly growing non-partitioned table, we could not perform this operation directly, and we were experiencing decreasing in our performance of accessing the data.

Look at the situation in Oracle 11g R2:

//db_version: 11g R2
CREATE TABLE partitionTest
(
   YEARMONTH        NUMBER,
   INVOICE_ID       NUMBER,
   PRICE            NUMBER
);

INSERT /*+append*/
      INTO  partitionTest
       SELECT TO_NUMBER (TO_CHAR (SYSDATE - MOD (LEVEL, 365), 'YYYYMM'))
                 YEARMONTH,
              LEVEL INVOCE_ID,
              MOD (LEVEL, 365) * 10 price
         FROM DUAL
   CONNECT BY LEVEL < 100000;

COMMIT;

ALTER TABLE partitionTest MODIFY
PARTITION BY RANGE (YEARMONTH)
  (PARTITION P201606 VALUES LESS THAN (201607),
      PARTITION P201607 VALUES LESS THAN (201608),
      PARTITION P201608 VALUES LESS THAN (201609),
      PARTITION P201609 VALUES LESS THAN (201610),
      PARTITION P201610 VALUES LESS THAN (201611),
      PARTITION P201611 VALUES LESS THAN (201612),
      PARTITION P201612 VALUES LESS THAN (201701),
      PARTITION P201701 VALUES LESS THAN (201702),
      PARTITION P201702 VALUES LESS THAN (201703),
      PARTITION P201703 VALUES LESS THAN (201704),
      PARTITION P201704 VALUES LESS THAN (201705),
      PARTITION P201705 VALUES LESS THAN (201706),
      PARTITION P201706 VALUES LESS THAN (201707))

ORA-14006: invalid partition name

Script Terminated on line 21.

If I want to create a partitioned structure in Oracle 11g R2 as a non-partitioned table created above, I can not do this directly on the table.

Now, let's try this operation with Oracle 12c R2:

//db_version: 12C R2
CREATE TABLE partitionTest
(
   YEARMONTH        NUMBER,
   INVOICE_ID       NUMBER,
   PRICE            NUMBER
);

INSERT /*+append*/
      INTO  partitionTest
       SELECT TO_NUMBER (TO_CHAR (SYSDATE - MOD (LEVEL, 365), 'YYYYMM'))
                 YEARMONTH,
              LEVEL INVOCE_ID,
              MOD (LEVEL, 365) * 10 price
         FROM DUAL
   CONNECT BY LEVEL < 100000;

   COMMIT;

ALTER TABLE partitionTest MODIFY
PARTITION BY RANGE (YEARMONTH)
  (PARTITION P201606 VALUES LESS THAN (201607),
      PARTITION P201607 VALUES LESS THAN (201608),
      PARTITION P201608 VALUES LESS THAN (201609),
      PARTITION P201609 VALUES LESS THAN (201610),
      PARTITION P201610 VALUES LESS THAN (201611),
      PARTITION P201611 VALUES LESS THAN (201612),
      PARTITION P201612 VALUES LESS THAN (201701),
      PARTITION P201701 VALUES LESS THAN (201702),
      PARTITION P201702 VALUES LESS THAN (201703),
      PARTITION P201703 VALUES LESS THAN (201704),
      PARTITION P201704 VALUES LESS THAN (201705),
      PARTITION P201705 VALUES LESS THAN (201706),
      PARTITION P201706 VALUES LESS THAN (201707)) ONLINE;

Table PARTITIONTEST created.

99.999 rows inserted.

Commit complete.

Table PARTITIONTEST altered.

Now, let's check the partitions:

SELECT table_name, partition_name, high_value
  FROM all_tab_partitions
 WHERE table_name = 'PARTITIONTEST';

Yes, as you can see, I could partition a non-partitioned table after writing the data into the table. The ONLINE command that I used at the end of the ALTER TABLE ... MODIFY clause to do this allowed the DML operations to be processed concurrently in the table while physically partitioning the table. So, I did not have any downtime while the table was doing a great physical change.

This innovation, coupled with Oracle 12c R2, has significantly improved the non-partitioned tables by removing the operational and downtime costs of partitioning.

Database

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Learning by Doing: An HTTP API With Rust
  • When Scrum Feels Like Dressing for Dinner
  • Apache Kafka vs. Memphis.dev
  • Pair Testing in Software Development

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: