8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Multi-Column List Partitioning in Oracle Database 12c Release 2 (12.2)
Oracle Database 12c Release 2 (12.2) introduced the ability to define a list partitioned table based on multiple columns. Creating a multi-column list partitioned table is similar to creating a regular list partitioned table, except the PARTITION BY LIST clause includes a comma separated list of columns. Each partition is associated with valid combinations of those columns, along with an optional single default partition to catch any unspecified combinations.
Related articles.
- All Partitioning Articles
- Automatic List Partitioning in Oracle Database 12c Release 2 (12.2)
- Partitioning Enhancements in Oracle Database 12c Release 2 (12.2)
Multi-Column List Partitioning
The example below creates a list-partitioned table based on COUNTRY_CODE and RECORD_TYPE.
DROP TABLE t1 PURGE;
CREATE TABLE t1 (
id NUMBER,
country_code VARCHAR2(3),
record_type VARCHAR2(5),
descriptions VARCHAR2(50),
CONSTRAINT t1_pk PRIMARY KEY (id)
)
PARTITION BY LIST (country_code, record_type)
(
PARTITION part_gbr_abc VALUES (('GBR','A'), ('GBR','B'), ('GBR','C')),
PARTITION part_ire_ab VALUES (('IRE','A'), ('IRE','B')),
PARTITION part_usa_a VALUES (('USA','A')),
PARTITION part_others VALUES (DEFAULT)
);
The following insert statements include one of each combination that matches the allowable combinations for the main partitions, as well as four that that don't match and will be stored in the default partition.
-- Defined partitions. INSERT INTO t1 VALUES ( 1, 'GBR', 'A', 'Description for GBR_A'); INSERT INTO t1 VALUES ( 2, 'GBR', 'B', 'Description for GBR_B'); INSERT INTO t1 VALUES ( 3, 'GBR', 'C', 'Description for GBR_C'); INSERT INTO t1 VALUES ( 4, 'IRE', 'A', 'Description for IRE_A'); INSERT INTO t1 VALUES ( 5, 'IRE', 'B', 'Description for IRE_B'); INSERT INTO t1 VALUES ( 6, 'USA', 'A', 'Description for USA_A'); -- Default default. INSERT INTO t1 VALUES ( 7, 'BGR', 'Z', 'Description for BGR_Z'); INSERT INTO t1 VALUES ( 8, 'GBR', 'Z', 'Description for GBR_Z'); INSERT INTO t1 VALUES ( 9, 'IRE', 'Z', 'Description for IRE_Z'); INSERT INTO t1 VALUES (10, 'USA', 'Z', 'Description for USA_Z'); COMMIT; EXEC DBMS_STATS.gather_table_stats(USER, 'T1');
Displaying the number of rows in each partition shows the partitioning scheme worked as expected.
SET LINESIZE 120
COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN high_value FORMAT A50
SELECT table_name,
partition_name,
high_value,
num_rows
FROM user_tab_partitions
WHERE table_name = 'T1'
ORDER BY 1, 2;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- -------------------------------------------------- ----------
T1 PART_GBR_ABC ( 'GBR', 'A' ), ( 'GBR', 'B' ), ( 'GBR', 'C' ) 3
T1 PART_IRE_AB ( 'IRE', 'A' ), ( 'IRE', 'B' ) 2
T1 PART_OTHERS DEFAULT 4
T1 PART_USA_A ( 'USA', 'A' ) 1
SQL>
Multi-Column List Partitioning and Automatic List Partitioning
You can use automatic list partitioning in combination with multi-column list partitioning provided you don't specify a default partition. If you try to use both you will receive the following error.
ERROR at line 1: ORA-14851: DEFAULT [sub]partition cannot be specified for AUTOLIST [sub]partitioned objects.
The following example defines a multi-column list partitioned table that uses automatic list partitioning. Notice the default partition has not been defined.
DROP TABLE t1 PURGE;
CREATE TABLE t1 (
id NUMBER,
country_code VARCHAR2(3),
record_type VARCHAR2(5),
descriptions VARCHAR2(50),
CONSTRAINT t1_pk PRIMARY KEY (id)
)
PARTITION BY LIST (country_code, record_type) AUTOMATIC
(
PARTITION part_gbr_abc VALUES (('GBR','A'), ('GBR','B'), ('GBR','C')),
PARTITION part_ire_ab VALUES (('IRE','A'), ('IRE','B')),
PARTITION part_usa_a VALUES (('USA','A'))
);
Repeat the inserts from the previous example.
-- Defined partitions. INSERT INTO t1 VALUES ( 1, 'GBR', 'A', 'Description for GBR_A'); INSERT INTO t1 VALUES ( 2, 'GBR', 'B', 'Description for GBR_B'); INSERT INTO t1 VALUES ( 3, 'GBR', 'C', 'Description for GBR_C'); INSERT INTO t1 VALUES ( 4, 'IRE', 'A', 'Description for IRE_A'); INSERT INTO t1 VALUES ( 5, 'IRE', 'B', 'Description for IRE_B'); INSERT INTO t1 VALUES ( 6, 'USA', 'A', 'Description for USA_A'); -- Default default. INSERT INTO t1 VALUES ( 7, 'BGR', 'Z', 'Description for BGR_Z'); INSERT INTO t1 VALUES ( 8, 'GBR', 'Z', 'Description for GBR_Z'); INSERT INTO t1 VALUES ( 9, 'IRE', 'Z', 'Description for IRE_Z'); INSERT INTO t1 VALUES (10, 'USA', 'Z', 'Description for USA_Z'); COMMIT; EXEC DBMS_STATS.gather_table_stats(USER, 'T1');
Displaying the number of rows in each partition shows the partitioning scheme worked as expected, with a new partition defined for every combination that was not defined in the original table definition.
SET LINESIZE 120
COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN high_value FORMAT A50
SELECT table_name,
partition_name,
high_value,
num_rows
FROM user_tab_partitions
WHERE table_name = 'T1'
ORDER BY 1, 2;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- -------------------------------------------------- ----------
T1 PART_GBR_ABC ( 'GBR', 'A' ), ( 'GBR', 'B' ), ( 'GBR', 'C' ) 3
T1 PART_IRE_AB ( 'IRE', 'A' ), ( 'IRE', 'B' ) 2
T1 PART_USA_A ( 'USA', 'A' ) 1
T1 SYS_P688 ( 'BGR', 'Z' ) 1
T1 SYS_P689 ( 'GBR', 'Z' ) 1
T1 SYS_P690 ( 'IRE', 'Z' ) 1
T1 SYS_P691 ( 'USA', 'Z' ) 1
SQL>
For more information see:
- Creating a Multi-column List-Partitioned Table
- CREATE TABLE
- All Partitioning Articles
- Automatic List Partitioning in Oracle Database 12c Release 2 (12.2)
- Partitioning Enhancements in Oracle Database 12c Release 2 (12.2)
Hope this helps. Regards Tim...