oracle 12cR2 多列列表分区

来源：互联网发布：淘宝怎么查排名编辑：程序博客网时间：2024/06/01 14:51

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子句中使用逗号分隔列表。

每个分区和定义这些列的有效组合相关联，还有一个可选的默认分区来捕获任何未指定的组合。

Multi-Column List Partition

Multi-Column List Partitioning
Multi-Column List Partitioning and Automatic List Partitioning

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 120COLUMN table_name FORMAT A20COLUMN partition_name FORMAT A20COLUMN high_value FORMAT A50SELECT table_name,       partition_name,       high_value,       num_rowsFROM   user_tab_partitionsWHERE  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' )              3T1                   PART_IRE_AB          ( 'IRE', 'A' ), ( 'IRE', 'B' )                              2T1                   PART_OTHERS          DEFAULT                                                     4T1                   PART_USA_A           ( 'USA', 'A' )                                              1SQL>

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 120COLUMN table_name FORMAT A20COLUMN partition_name FORMAT A20COLUMN high_value FORMAT A50SELECT table_name,       partition_name,       high_value,       num_rowsFROM   user_tab_partitionsWHERE  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' )              3T1                   PART_IRE_AB          ( 'IRE', 'A' ), ( 'IRE', 'B' )                              2T1                   PART_USA_A           ( 'USA', 'A' )                                              1T1                   SYS_P688             ( 'BGR', 'Z' )                                              1T1                   SYS_P689             ( 'GBR', 'Z' )                                              1T1                   SYS_P690             ( 'IRE', 'Z' )                                              1T1                   SYS_P691             ( 'USA', 'Z' )                                              1SQL>

阅读全文

0 0