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.
Oracle 12cR2介绍了基于多列定义列表分区的特性,创建多列列表分区和常规的列表分区类似,除了在 PARTITION BY LIST子句中使用逗号分隔列表。
每个分区和定义这些列的有效组合相关联,还有一个可选的默认分区来捕获任何未指定的组合。
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
.
该示例以COUNTRY_CODE和
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>