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
- LIST PARTITION BASED ON LIST OF VALUES
- HASH PARTIOTIONING BASED ON HASH KEY
- COMPOSITE PARTITIONING BY MIXING THINGS UP
- INTERVAL 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.
2 thoughts to “Lab on Partitioning using Oracle 18C”