DZone
Database Zone
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
  • Refcardz
  • Trend Reports
  • Webinars
  • Zones
  • |
    • Agile
    • AI
    • Big Data
    • Cloud
    • Database
    • DevOps
    • Integration
    • IoT
    • Java
    • Microservices
    • Open Source
    • Performance
    • Security
    • Web Dev
DZone > Database Zone > 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 · Database Zone · Tutorial
Like (3)
Save
Tweet
5.69K 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

  • Servlets Listeners Introduction and Examples
  • How to Configure Git in Eclipse IDE
  • Instancio: Random Test Data Generator for Java (Part 1)
  • Toying With Kotlin’s Context Receivers

Comments

Database Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • MVB Program
  • 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:

DZone.com is powered by 

AnswerHub logo