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= Output
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= Output
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=
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=
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= Output
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= Output
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= Output
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= Output
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= Output
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= Output
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= Output
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= Output
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= Output
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 1
edb=
UPDATE 1 This DELETE
statement removes rows from the partition q1_2020
or subpartition q1_europe
of the sales
table:
edb=
DELETE 1
edb=
DELETE 1