For a partitioned table, you can access the partition or subpartition using PARTITION part_name
or SUBPARTITION subpart_name
. This example creates a partitioned table sales
that's range partitioned by date
and subpartitioned using list partitioning by the country
column:
CREATE TABLE sales
(
dept_no number,
part_no varchar2,
country varchar2(20),
date date,
amount number
)
PARTITION BY RANGE(date)
SUBPARTITION BY LIST(country)
(
PARTITION q1_2020
VALUES LESS THAN('2020-Apr-01')
(
SUBPARTITION q1_europe VALUES ('FRANCE', 'ITALY'),
SUBPARTITION q1_asia VALUES ('INDIA', 'PAKISTAN'),
SUBPARTITION q1_americas VALUES ('US', 'CANADA')
),
PARTITION q2_2020
VALUES LESS THAN('2020-Jul-01')
(
SUBPARTITION q2_europe VALUES ('FRANCE', 'ITALY'),
SUBPARTITION q2_asia VALUES ('INDIA', 'PAKISTAN'),
SUBPARTITION q2_americas VALUES ('US', 'CANADA')
)
); The SELECT
statement shows two partitions. Each partition has three subpartitions.
edb=# SELECT subpartition_name, high_value, partition_name FROM ALL_TAB_SUBPARTITIONS;
subpartition_name | high_value | partition_name
-------------------+---------------------+----------------
Q1_EUROPE | 'FRANCE', 'ITALY' | Q1_2020
Q1_ASIA | 'INDIA', 'PAKISTAN' | Q1_2020
Q1_AMERICAS | 'US', 'CANADA' | Q1_2020
Q2_EUROPE | 'FRANCE', 'ITALY' | Q2_2020
Q2_ASIA | 'INDIA', 'PAKISTAN' | Q2_2020
Q2_AMERICAS | 'US', 'CANADA' | Q2_2020
(6 rows) This INSERT
statement inserts rows into the sales
table using specific PARTITION part_name
or SUBPARTITION subpart_name
values:
INSERT INTO sales PARTITION (q1_2020) VALUES (10, 'q1_2020', 'FRANCE', '2020-Feb-01', '500000');
INSERT INTO sales PARTITION (q2_2020) VALUES (10, 'q2_2020', 'ITALY', '2020-Apr-01', '550000');
INSERT INTO sales SUBPARTITION (q1_europe) VALUES (10, 'q1_europe', 'FRANCE', '2020-Feb-01', '600000');
INSERT INTO sales SUBPARTITION (q2_asia) VALUES (10, 'q2_asia', 'INDIA', '2020-Apr-01', '650000');
edb=# SELECT tableoid::regclass, * FROM sales ORDER BY date;
tableoid | dept_no | part_no | country | date | amount
-----------------+---------+-----------+---------+--------------------+--------
sales_q1_europe | 10 | q1_2020 | FRANCE | 01-FEB-20 00:00:00 | 500000
sales_q1_europe | 10 | q1_europe | FRANCE | 01-FEB-20 00:00:00 | 600000
sales_q2_europe | 10 | q2_2020 | ITALY | 01-APR-20 00:00:00 | 550000
sales_q2_asia | 10 | q2_asia | INDIA | 01-APR-20 00:00:00 | 650000
(4 rows) Use this query to fetch the values from a specific partition q1_2020
or subpartition q1_europe
:
edb=# SELECT tableoid::regclass, * FROM sales PARTITION (q1_2020) ORDER BY date;
tableoid | dept_no | part_no | country | date | amount
-----------------+---------+-----------+---------+--------------------+--------
sales_q1_europe | 10 | q1_2020 | FRANCE | 01-FEB-20 00:00:00 | 500000
sales_q1_europe | 10 | q1_europe | FRANCE | 01-FEB-20 00:00:00 | 600000
(2 rows)
edb=# SELECT tableoid::regclass, country FROM sales SUBPARTITION (q1_europe) ORDER BY date;
tableoid | country
-----------------+---------
sales_q1_europe | FRANCE
sales_q1_europe | FRANCE
(2 rows) This SELECT
statement selects rows from a specific partition or subpartition of a partitioned table by specifying the keyword PARTITION
or SUBPARTITION
:
edb=# SELECT tableoid::regclass, * FROM sales PARTITION (q2_2020) ORDER BY country;
tableoid | dept_no | part_no | country | date | amount
-----------------+---------+---------+---------+--------------------+--------
sales_q2_asia | 10 | q2_asia | INDIA | 01-APR-20 00:00:00 | 650000
sales_q2_europe | 10 | q2_2020 | ITALY | 01-APR-20 00:00:00 | 550000
(2 rows)
edb=# SELECT tableoid::regclass, date FROM sales SUBPARTITION (q2_asia) ORDER BY country;
tableoid | date
---------------+--------------------
sales_q2_asia | 01-APR-20 00:00:00
(1 row) This UPDATE
statement updates values in a partition or subpartition of the sales
table:
edb=# UPDATE sales PARTITION (q1_2020) SET amount = 10000 WHERE amount = 500000 RETURNING tableoid::regclass, *;
tableoid | dept_no | part_no | country | date | amount
-----------------+---------+---------+---------+--------------------+--------
sales_q1_europe | 10 | q1_2020 | FRANCE | 01-FEB-20 00:00:00 | 10000
(1 row)
UPDATE 1
edb=# UPDATE sales SUBPARTITION (q1_europe) SET amount = 5000 WHERE amount = 600000 RETURNING tableoid::regclass, *;
tableoid | dept_no | part_no | country | date | amount
-----------------+---------+-----------+---------+--------------------+--------
sales_q1_europe | 10 | q1_europe | FRANCE | 01-FEB-20 00:00:00 | 5000
(1 row)
UPDATE 1 This DELETE
statement removes rows from the partition q2_2020
or subpartition q2_asia
of the sales
table:
edb=# DELETE FROM sales PARTITION (q2_2020) WHERE amount = 550000 RETURNING tableoid::regclass, *;
tableoid | dept_no | part_no | country | date | amount
-----------------+---------+---------+---------+--------------------+--------
sales_q2_europe | 10 | q2_2020 | ITALY | 01-APR-20 00:00:00 | 550000
(1 row)
DELETE 1
edb=# DELETE FROM sales SUBPARTITION (q2_asia) RETURNING tableoid::regclass, *;
tableoid | dept_no | part_no | country | date | amount
---------------+---------+---------+---------+--------------------+--------
sales_q2_asia | 10 | q2_asia | INDIA | 01-APR-20 00:00:00 | 650000
(1 row)
DELETE 1 Using alias for accessing PARTITION or SUBPARTITION You can use aliases with SELECT
, INSERT
, UPDATE
, or DELETE
statements to access a partition or subpartition. This example creates a partitioned table sales
that's range partitioned by date and subpartitioned using list partitioning by the country
column:
CREATE TABLE sales
(
dept_no number,
part_no varchar2,
country varchar2(20),
date date,
amount number
)
PARTITION BY RANGE(date)
SUBPARTITION BY LIST(country)
(
PARTITION q1_2020
VALUES LESS THAN('2020-Apr-01')
(
SUBPARTITION q1_europe VALUES ('FRANCE', 'ITALY'),
SUBPARTITION q1_asia VALUES ('INDIA', 'PAKISTAN'),
SUBPARTITION q1_americas VALUES ('US', 'CANADA')
),
PARTITION q2_2020
VALUES LESS THAN('2020-Jul-01')
(
SUBPARTITION q2_europe VALUES ('FRANCE', 'ITALY'),
SUBPARTITION q2_asia VALUES ('INDIA', 'PAKISTAN'),
SUBPARTITION q2_americas VALUES ('US', 'CANADA')
)
); The SELECT
statement shows two partitions. Each partition has three subpartitions.
edb=# SELECT subpartition_name, high_value, partition_name FROM ALL_TAB_SUBPARTITIONS;
subpartition_name | high_value | partition_name
-------------------+---------------------+----------------
Q1_EUROPE | 'FRANCE', 'ITALY' | Q1_2020
Q1_ASIA | 'INDIA', 'PAKISTAN' | Q1_2020
Q1_AMERICAS | 'US', 'CANADA' | Q1_2020
Q2_EUROPE | 'FRANCE', 'ITALY' | Q2_2020
Q2_ASIA | 'INDIA', 'PAKISTAN' | Q2_2020
Q2_AMERICAS | 'US', 'CANADA' | Q2_2020
(6 rows) This INSERT
statement creates an alias of the sales
table and inserts rows into partition q1_2020
and q2_2020
or subpartition q1_europe
and q1_asia
:
INSERT INTO sales PARTITION (q1_2020) AS q1_sales VALUES (10, 'q1_2020', 'FRANCE', '2020-Feb-01', '500000');
INSERT INTO sales PARTITION (q2_2020) q2_sales (q2_sales.dept_no, q2_sales.part_no, q2_sales.country, q2_sales.date, q2_sales.amount) VALUES (20, 'q2_2020', 'ITALY', '2020-Apr-01', '550000');
INSERT INTO sales SUBPARTITION (q1_europe) AS sales_q1_europe VALUES (10, 'q1_europe', 'FRANCE', '2020-Feb-01', '600000');
INSERT INTO sales SUBPARTITION (q1_asia) sales_q1_asia (sales_q1_asia.dept_no, sales_q1_asia.part_no, sales_q1_asia.country, sales_q1_asia.date, sales_q1_asia.amount) VALUES (20, 'q1_asia', 'INDIA', '2020-Mar-01', '650000'); This SELECT
statement selects rows from a specific partition or subpartition of a sales
table:
edb=# SELECT tableoid::regclass, * FROM sales PARTITION (q1_2020) AS q1_sales ORDER BY country;
tableoid | dept_no | part_no | country | date | amount
-----------------+---------+-----------+---------+--------------------+--------
sales_q1_europe | 10 | q1_2020 | FRANCE | 01-FEB-20 00:00:00 | 500000
sales_q1_europe | 10 | q1_europe | FRANCE | 01-FEB-20 00:00:00 | 600000
sales_q1_asia | 20 | q1_asia | INDIA | 01-MAR-20 00:00:00 | 650000
(3 rows)
edb=# SELECT tableoid::regclass, * FROM sales SUBPARTITION (q1_europe) sales_q1_europe ORDER BY country;
tableoid | dept_no | part_no | country | date | amount
-----------------+---------+-----------+---------+--------------------+--------
sales_q1_europe | 10 | q1_2020 | FRANCE | 01-FEB-20 00:00:00 | 500000
sales_q1_europe | 10 | q1_europe | FRANCE | 01-FEB-20 00:00:00 | 600000
(2 rows) This UPDATE
statement updates values in a partition or subpartition of the sales
table:
edb=# UPDATE sales PARTITION (q1_2020) AS q1_sales SET q1_sales.amount = 10000 WHERE q1_sales.amount = 500000;
UPDATE 1
edb=# UPDATE sales SUBPARTITION (q1_europe) sales_q1_europe SET sales_q1_europe.amount = 5000 WHERE sales_q1_europe.amount = 600000;
UPDATE 1 This DELETE
statement removes rows from the partition q1_2020
or subpartition q1_europe
of the sales
table:
edb=# DELETE FROM sales PARTITION (q1_2020) q1_sales WHERE q1_sales.amount = 10000;
DELETE 1
edb=# DELETE FROM sales SUBPARTITION (q1_europe) AS sales_q1_europe WHERE sales_q1_europe.amount = 5000;
DELETE 1