oracle 12cR2 多列列表分区
来源:互联网 发布:淘宝怎么查排名 编辑:程序博客网 时间:2024/06/02 08:32
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 Partitioning
- Multi-Column List Partitioning and Automatic List Partitioning
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 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>
- oracle 12cR2 多列列表分区
- Oracle 12cR2自动列表分区
- oracle 12cR2 过滤分区维护操作
- Oracle表分区分为四种:范围分区,散列分区,列表分区和复合分区
- Oracle表分区分为四种:范围分区,散列分区,列表分区和复合分区
- Oracle表分区分为四种:范围分区,散列分区,列表分区和复合分区
- MySQL分区--列表列分区
- oracle分区-散列分区
- Oracle.表分区:列表分区
- oracle范围分区多列和分区模板
- Oracle列表分区
- Oracle 12c 多租户专题|12cR2中PDB内存资源管理
- Oracle 12c 多租户专题|12cR2中PDB内存资源管理
- Install Oracle Database 12cR2 On Oracle Enterprise Linux7.3
- 复合分区 (范围-散列分区,范围-列表分区)
- Oracle.表分区:散列分区
- Oracle 12cR2发布,金融行业准备大规模上了
- Install Oracle Database 12cR2 On RHEL7.3
- java development kit即JDK的环境变量配置-解决javac没反应
- 无废话ExtJs 入门教程八[脚本调试Firefox:firebug]
- 域名解析的记录类型
- 实验四:用可重用的链表模块来实现命令行菜单小程序V2.5
- js中正则表达式小总结
- oracle 12cR2 多列列表分区
- hdu1908map 用法
- 怎么将静态网页放到tomcat服务器,并绑定域名,提供访问?
- 无废话ExtJs 入门教程九[数字字段:NumberField、隐藏字段Hidden、日期字段:DataFiedl]
- C# GridView 如何在没有资料时也可以呈现表头栏位
- chrome-headless使用示例(Python)——打开百度
- 数据结构第一次上机(学生信息管理&线性表&顺序表)
- 【深度分析】Java的反编译
- 【安全牛学习笔记】DNS放大攻击