简介
从19c开始，Oracle数据库支持Hybrid partitioned tables，也就是混合分区表，进一步扩展了Oracle分区技术。这里的混合指的是数据的分布，分区表的分区可以一些位于数据库中，另一些位于数据库外部的文件（比如操作系统文件或HDFS文件）。这个特性的出现，其实一点也不奇怪，因为从12.2开始就支持了外部表分区、只读分区，在19c中只是将内部分区（internal partitions）与Oracle外部分区（external partitions）特性结合起来，形成一个更通用的分区，称为混合分区表。
混合分区的现实意义还是比较明显的，首先通过混合分区可以轻松地将内部（驻留在Oracle表空间中--internal partitions）和外部的数据（external partitions）集成到单个分区表中，其次可以方便地将非活跃数据移动到外部文件，在而降低存储成本的同时也更加方便数据交换。
特点
1) 混合分区表支持外部分区的所有现有外部表类型
ORACLE_DATAPUMPORACLE_LOADERORACLE_HDFSORACLE_HIVE
2）所有外部表参数均适用于混合分区表的外部分区。
3）混合分区表可以跨内部、外部分区使用基于分区的优化技术，典型的比如：静态分区修剪、动态分区修剪、布隆修剪
混合分区表支持的操作
（1）当前仅支持创建single-level的range和list分区，其中只有single-level LIST分区支持HIVE
（2）可以使用alter table ...DDLs操作，比如ADD,DROP和RENAME partitions
（3）可以在分区级别修改external partitions的external data sources位置
（4）可以将既有的内部分区表修改为混合分区表
（5）可以修改现有的location到empty location形成一个空的external partition
（6）可以针对内部分区创建global partial non-unique indexes
（7）可以针对内部分区创建物化视图
(8)可以创建包含外部分区的物化视图，前提是QUERY_REWRITE_INTEGRITY必须为STALE_TOLERATED模式
（9）DML操作只能针对混合分区表的内部分区
(10) 使用ANALYZE TABLE ... VALIDATE STRUCTURE验证仅有内部分区的混合分区表
(11) 将一个无外部分区的混合分区表alter为仅有内部分区的（普通）分区表
(12) 外部分区可以与外部非分区表进行交换(exchanged)，内部分区也可以与内部非分区表进行交换
注意：
1）不支持存储在外部分区中的数据强制约束，例如不能在混合分区表上强制主键或外键约束。在混合分区表上，只支持RELYDISABLE约束，要使用基于这种约束的优化特性，需要配合会话参数QUERY_REWRITE_INTEGRITY（设置为TRUSTED或STALE_TOLERATED）。
2）在混合分区表级别定义的Automatic Data Optimization (ADO)策略只影响内部分区
混合分区表的限制
（1）除非特殊说明，否则外部表的限制同样适用于混合分区表
（2）不支持REFERENCE和SYSTEM分区方法
（3）不支持唯一索引和全局唯一索引，仅支持部分索引
（4）不支持集群属性（例如CLUSTERING子句）
（5）只能对混合分区表中的内部分区进行dml操作（外部分区只读）
（6）混合分区表使用In-memory时只能对内部分区有效
（7）不能用列默认值
（8）不允许不可见列
（9）不允许CELL MEMORY子句
（10）不允许对内部分区进行SPLIT,MERGE, and MOVE操作
（11）不支持LOB, LONG和ADT类型
（12）只允许RELY constraints
混合分区表初体验
1）准备测试数据
sale_.txt
region,time_id,amount
EAST,0101,6000
EAST,0102,3000
EAST,0103,9012
EAST,0104,2450
EAST,0105,6709
SOUTH,0101,4000
SOUTH,0102,2120
SOUTH,0103,6300
SOUTH,0104,3850
SOUTH,0105,2090
WEST,0101,2467
WEST,0102,2140
WEST,0103,5300
WEST,0104,2470
WEST,0105,4080
NORTH,0101,2600
NORTH,0102,1300
NORTH,0103,1250
NORTH,0104,4350
NORTH,0105,3190
sale_.txt
region,time_id,amount
EAST,0101,8000
EAST,0102,7000
EAST,0103,6500
EAST,0104,3450
EAST,0105,9000
SOUTH,0101,2000
SOUTH,0102,3120
SOUTH,0103,2300
SOUTH,0104,5850
SOUTH,0105,1900
WEST,0101,3400
WEST,0102,2400
WEST,0103,5900
WEST,0104,5450
WEST,0105,1780
NORTH,0101,2000
NORTH,0102,1000
NORTH,0103,3000
NORTH,0104,2350
NORTH,0105,2190
sale_.txt
region,time_id,amount
EAST,0101,2100
EAST,0102,7800
EAST,0103,6900
EAST,0104,9450
EAST,0105,9700
SOUTH,0101,1300
SOUTH,0102,2120
SOUTH,0103,6308
SOUTH,0104,2850
SOUTH,0105,7900
WEST,0101,3800
WEST,0102,2600
WEST,0103,5200
WEST,0104,5250
WEST,0105,2980
NORTH,0101,2120
NORTH,0102,1230
NORTH,0103,3500
NORTH,0104,2050
NORTH,0105,1060
sale_.sql
insert into hybrid_test values('EAST', to_date('0101','yyyy-mm-dd'),1032);insert into hybrid_test values('EAST', to_date('0102','yyyy-mm-dd'),2371);commit;
2）定义DIRECTORY
CREATE DIRECTORY sales_data as '/u01/app/oracle/oradata/extfiles'; CREATE DIRECTORY sales_data_ as '/u01/app/oracle/oradata/extfiles/'; CREATE DIRECTORY sales_data_ as '/u01/app/oracle/oradata/extfiles/';
3）创建Hybridpartitioned tables-混合分区表
CREATE TABLE hybrid_test(region varchar2(6) NOT NULL,time_id DATE NOT NULL,amount NUMBER(10,2))EXTERNAL PARTITION ATTRIBUTES --必须加这个子句声明(TYPE ORACLE_LOADERDEFAULT DIRECTORY sales_dataACCESS PARAMETERS( FIELDS TERMINATED BY ','(region,time_id DATE 'yyyy-mm-dd',amount))REJECT LIMIT UNLIMITED)PARTITION BY RANGE (time_id)( PARTITION sales_ VALUES LESS THAN (TO_DATE('-01-01','yyyy-mm-dd')) EXTERNAL, --空的外部分区PARTITION sales_ VALUES LESS THAN (TO_DATE('-01-01','yyyy-mm-dd'))EXTERNAL DEFAULT DIRECTORY sales_data_ LOCATION ('sales_.txt'),PARTITION sales_ VALUES LESS THAN (TO_DATE('-01-01','yyyy-mm-dd'))EXTERNAL DEFAULT DIRECTORY sales_data_ LOCATION ('sales_.txt'),PARTITION sales_ VALUES LESS THAN (TO_DATE('-01-01','yyyy-mm-dd')) EXTERNAL LOCATION ('sales_.txt'),PARTITION sales_ VALUES LESS THAN (TO_DATE('-01-01','yyyy-mm-dd')) --内部分区);Table created.
##通过dba_tables数据字典的hybrid字段可以看出是否是混合分区
PDB1@ZRP>select table_name,partitioned, hybrid from dba_tables where owner='ZRP' and table_name='HYBRID_TEST';TABLE_NAME PAR HYB--------------------HYBRID_TEST YES YESPDB1@ZRP>select table_name,partition_name,tablespace_name,logging,read_only from dba_tab_partitions where table_owner='ZRP';
##插入几行数据（只能插入到内部分区（internal partitions），我这里是sales_)
PDB1@ZRP>select * from hybrid_test partition (sales_);no rows selectedPDB1@ZRP>insert into hybrid_test values('EAST', to_date('0101','yyyy-mm-dd'),1032);1 row created.PDB1@ZRP>insert into hybrid_test values('EAST', to_date('0102','yyyy-mm-dd'),2371);1 row created.PDB1@ZRP>commit;Commit complete.
##查看各分区的数据
PDB1@ZRP>select * from hybrid_test partition (sales_);REGION TIME_ID AMOUNT------ ------------------- ----------EAST -01-01 00:00:00 1032EAST -01-02 00:00:00 2371PDB1@ZRP>select * from hybrid_test partition (sales_);no rows selectedPDB1@ZRP>select * from hybrid_test partition (sales_);REGION TIME_ID AMOUNT------ ------------------- ----------EAST -01-01 00:00:00 6000EAST -01-02 00:00:00 3000EAST -01-03 00:00:00 9012...NORTH -01-03 00:00:00 1250NORTH -01-04 00:00:00 4350NORTH -01-05 00:00:00 319020 rows selected.PDB1@ZRP>select * from hybrid_test partition (sales_);REGION TIME_ID AMOUNT------ ------------------- ----------EAST -01-01 00:00:00 8000EAST -01-02 00:00:00 7000EAST -01-03 00:00:00 6500...NORTH -01-03 00:00:00 3000NORTH -01-04 00:00:00 2350NORTH -01-05 00:00:00 219020 rows selected.PDB1@ZRP>select * from hybrid_test partition (sales_);REGION TIME_ID AMOUNT------ ------------------- ----------EAST -01-01 00:00:00 2100EAST -01-02 00:00:00 7800EAST -01-03 00:00:00 6900...NORTH -01-03 00:00:00 3500NORTH -01-04 00:00:00 2050NORTH -01-05 00:00:00 106020 rows selected.
4）将混合分区表转为Internal Partitioned Tables（传统分区）
PDB1@ZRP>select table_name,partitioned, hybrid from dba_tables where owner='ZRP' and table_name='HYBRID_TEST';TABLE_NAME PAR HYB-------------------- --- ---HYBRID_TEST YES YESPDB1@ZRP>select table_name,partition_name,tablespace_name,logging,read_only from dba_tab_partitions where table_owner='ZRP';TABLE_NAME PARTITION_NAME TABLESPACE_NAME LOGGING READ-------------------- -------------------- ------------------------------ ------- ----HYBRID_TEST SALES_ SYSTEM NO YESHYBRID_TEST SALES_ SYSTEM NO YESHYBRID_TEST SALES_ SYSTEM NO YESHYBRID_TEST SALES_ SYSTEM NO YESHYBRID_TEST SALES_ USERS YES NO
1）第一步删除external partitions
2）第二步删除external partition attributes
执行下面删除分区语句
alter table hybrid_test drop partition sales_;alter table hybrid_test drop partition sales_;alter table hybrid_test drop partition sales_;alter table hybrid_test drop partition sales_;alter table hybrid_test drop EXTERNAL PARTITION ATTRIBUTES();
再查看数据字典
PDB1@ZRP>select table_name,partitioned, hybrid from dba_tables where owner='ZRP' and table_name='HYBRID_TEST';TABLE_NAME PAR HYB-------------------- --- ---HYBRID_TEST YES NOPDB1@ZRP>select table_name,partition_name,tablespace_name,logging,read_only from dba_tab_partitions where table_owner='ZRP';TABLE_NAME PARTITION_NAME TABLESPACE_NAME LOGGING READ-------------------- -------------------- ------------------------------ ------- ----HYBRID_TEST SALES_ USERS YES NO
已经转换为传统分区表
5）将Internal Partitioned Tables（传统分区）转为混合分区表
1）首先要增加EXTERNAL PARTITION ATTRIBUTES
2）第二步增加external partitions
注：至少要有一个internal partition
PDB1@ZRP>alter table hybrid_test ADD EXTERNAL PARTITION ATTRIBUTES( TYPE ORACLE_LOADER DEFAULT DIRECTORY sales_data ACCESS PARAMETERS ( FIELDS TERMINATED BY ','(region,time_id DATE 'yyyy-mm-dd',amount))REJECT LIMIT UNLIMITED);Table altered.
然后将之前的测试外部数据作为外部分区添加进去
PDB1@ZRP>alter table hybrid_test ADD PARTITION sales_ VALUES LESS THAN (TO_DATE('-01-01','yyyy-mm-dd')) EXTERNAL;ADD PARTITION sales_ VALUES LESS THAN (TO_DATE('-01-01','yyyy-mm-dd')) EXTERNAL*ERROR at line 2:ORA-14074: partition bound must collate higher than that of the last partition
发现不行，因为我不想破坏数据，顺道练习了个split和exchange动作
##将hybrid_test的分区sales_拆分
alter table hybrid_test split partition sales_ into(partition sales_ VALUES LESS THAN (TO_DATE('-01-01','yyyy-mm-dd')),partition sales_);--创建一个中间表create table hybrid_test_temp(region varchar2(6) NOT NULL,time_id DATE NOT NULL,amount NUMBER(10,2));
-- 把里面的数据交换出去alter table hybrid_test exchange partition sales_ with table hybrid_test_temp;-- 然后删除这个sales_alter table hybrid_test drop partition sales_;-- 接下来就可以添加外部分区了PDB1@ZRP>alter table hybrid_test ADD PARTITION sales_ VALUES LESS THAN (TO_DATE('-01-01','yyyy-mm-dd')) EXTERNAL;Table altered.PDB1@ZRP>alter table hybrid_test ADD PARTITION sales_ VALUES LESS THAN (TO_DATE('-01-01','yyyy-mm-dd')) EXTERNAL DEFAULT DIRECTORY sales_data_ LOCATION ('sales_.txt');Table altered.PDB1@ZRP>alter table hybrid_test ADD PARTITION sales_ VALUES LESS THAN (TO_DATE('-01-01','yyyy-mm-dd')) EXTERNAL DEFAULT DIRECTORY sales_data_ LOCATION ('sales_.txt');Table altered.PDB1@ZRP>alter table hybrid_test ADD PARTITION sales_ VALUES LESS THAN (TO_DATE('-01-01','yyyy-mm-dd')) EXTERNAL LOCATION ('sales_.txt');Table altered.PDB1@ZRP>alter table hybrid_test ADD PARTITION sales_ VALUES LESS THAN (TO_DATE('-01-01','yyyy-mm-dd'));Table altered.-- 最后再把刚才交换出去的数据交换回来，这样就恢复原样了PDB1@ZRP>alter table hybrid_test exchange partition sales_ with table hybrid_test_temp;Table altered.PDB1@ZRP>select * from hybrid_test partition (sales_);REGION TIME_ID AMOUNT------ ------------------- ----------EAST -01-01 00:00:00 1032EAST -01-02 00:00:00 2371
-- 删除临时过渡分区PDB1@ZRP>alter table hybrid_test drop partition sales_;Table altered.这样就又恢复到最初的混合分区的样子了PDB1@ZRP>select table_name,partitioned, hybrid from dba_tables where owner='ZRP' and table_name='HYBRID_TEST';TABLE_NAME PAR HYB-------------------- --- ---HYBRID_TEST YES YESPDB1@ZRP>select table_name,partition_name,tablespace_name,logging,read_only from dba_tab_partitions where table_owner='ZRP';TABLE_NAME PARTITION_NAME TABLESPACE_NAME LOGGING READ-------------------- -------------------- ------------------------------ ------- ----HYBRID_TEST SALES_ USERS NO YESHYBRID_TEST SALES_ USERS NO YESHYBRID_TEST SALES_ USERS NO YESHYBRID_TEST SALES_ USERS NO YESHYBRID_TEST SALES_ USERS YES NOPDB1@ZRP>select * from hybrid_test partition(sales_);REGION TIME_ID AMOUNT------ ------------------- ----------EAST -01-01 00:00:00 6000EAST -01-02 00:00:00 3000EAST -01-03 00:00:00 9012...NORTH -01-03 00:00:00 1250NORTH -01-04 00:00:00 4350NORTH -01-05 00:00:00 319020 rows selected.
原文： /a/297399785_505827
