Lab on Partitioning using Oracle 18C

Partitioning helps in Query Optimization, and data organization. Partitioning in Oracle allows to subdivide a whole large logical object like table into smaller physical objects termed as partitions. The working principles of these physical partitioning helps to improve the performance, availability and manageability of applications that is built using this feature.

The scripts that follows shows the various types of partitioning:

RANGE PARTITIONING

CREATE TABLE SALES1
(
customer_id NUMBER,
sales_date DATE,
order_amount NUMBER,
region NVARCHAR2(10)
)
PARTITION BY RANGE(sales_date)
(
partition p1 VALUES LESS THAN(to_date('01-03-2015', 'dd-mm-yyyy')),
partition p2 VALUES LESS THAN(to_date('01-05-2016', 'dd-mm-yyyy')),
partition p3 VALUES LESS THAN(to_date('01-07-2017', 'dd-mm-yyyy')),
partition p4 VALUES LESS THAN(MAXVALUE)
);

The MAXVALUE is a value that a sequence can generate. This a way of asking Oracle to store any value other than the listed values and less than the MAXVALUE will be stored in this partition.

-- insert the multiple records, using this syntax
INSERT INTO sales1
VALUES
(100, '10-OCT-2014', 200,'EAST');
INSERT INTO sales1
VALUES
(110, '01-MAY-2015', 300,'WEST');
INSERT INTO sales1
VALUES
(120, '21-AUG-2015', 1200,'EAST');
INSERT INTO sales1
VALUES
(130, '13-FEB-2015', 2149,'WEST');
INSERT INTO sales1
VALUES
(140, '13-OCT-2016', 2180,'EAST');
INSERT INTO sales1
VALUES
(150, '15-FEB-2019', 2200,'EAST');
-- let's commit the data on the disk
COMMIT;
-- view the data normally
SELECT * FROM sales1;
-- view the data partition wise
SELECT * FROM sales1 PARTITION(p4);

The clause PARTITION(<partition_name>) will fetch the data from that particular partition only. You can use EXPLAIN to see the Query plan.

https://youtu.be/nJr-Ymn0_jk

Dhiraj

He specializes on Database Technologies, from Analysis and Design of Enterprise Level Database Architecture to it's implementation. He is responsible for implementation of Oracle 19C or 21C On premises and Cloud, Backup and Recovery, Performance Tuning and Query Optimization. He also has hands-on experience as a PL/SQL Developer on enterprise level application. Apart from Oracle, he conducts lectures, seminars and workshops on MySQLi Replication, High Availability, Persona, Postgres and MongoDB. His current research is based on Scalability using CockroachDB. As a technology enthusiast, he primarily develops solutions as a back-end developer using PHP/Laravel framework. He is more inclined towards FOSS and an admirer of Linux System Administration and secretly practices DevOps as a remote Engineer.

2 thoughts to “Lab on Partitioning using Oracle 18C”

Leave a Reply