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.
Join the DZone community and get the full member experience.
Join For FreeIn 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.
Opinions expressed by DZone contributors are their own.
Comments