8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

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.

Multi-Column List Partition

Related articles.

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:

Hope this helps. Regards Tim...

Back to the Top.