Lab on LIST PARTITIONING in ORACLE 18C

The earlier blog post RANGE PARTITIONING has already described about Range Partitioning. In this blog post I shall discuss about the LIST PARTITIONING and how it 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 using some list of values as well . The working principles of these physical partitioning helps to improve the performance, availability and manageability of applications that is built using this feature.

CREATE TABLE SALES
(
customer_id NUMBER,
sales_date DATE,
order_amount NUMBER,
region NVARCHAR2(10)
)
PARTITION BY LIST(REGION)
(
partition p1 VALUES ('EAST'),
partition p2 VALUES ('WEST'),
partition p3 VALUES ('NORTH'),
partition p4 VALUES ('SOUTH')
);
-- INSERT THE RECORDS
-- Note that string values are case sensitive, too critical, I've seen systems
-- failing because of this reason.
-- insert the multiple records, using this syntax
INSERT INTO sales
VALUES
(1, '11-OCT-2014', 2100,'EAST');
INSERT INTO sales
VALUES
(12, '12-MAY-2015', 1300,'WEST');
INSERT INTO sales
VALUES
(13, '3-AUG-2015', 11200,'EAST');
INSERT INTO sales
VALUES
(14, '16-FEB-2015', 21149,'WEST');
INSERT INTO sales
VALUES
(15, '10-OCT-2016', 21180,'NORTH');
INSERT INTO sales
VALUES
(16, '17-MAR-2019', 22010,'SOUTH');
INSERT INTO sales
VALUES
(17, '17-DEC-2020', 3211,'SOUTH');
INSERT INTO sales
VALUES
(18, '17-FEB-2018', 4322,'SOUTH');
INSERT INTO sales
VALUES
(19, '17-FEB-2021', 5433,'SOUTH');
-- let's commit the data on the disk
COMMIT;
-- view the data normally
SELECT * FROM sales;
-- view the data partition wise
SELECT * FROM sales PARTITION(p4);

Here, you should note that you cannot store any values that is not specified within the list.