从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_HIVE2）所有外部表参数均适用于混合分区表的外部分区。
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
sale_2016.txt
region,time_id,amount EAST,20160101,6000 EAST,20160102,3000 EAST,20160103,9012 EAST,20160104,2450 EAST,20160105,6709 SOUTH,20160101,4000 SOUTH,20160102,2120 SOUTH,20160103,6300 SOUTH,20160104,3850 SOUTH,20160105,2090 WEST,20160101,2467 WEST,20160102,2140 WEST,20160103,5300 WEST,20160104,2470 WEST,20160105,4080 NORTH,20160101,2600 NORTH,20160102,1300 NORTH,20160103,1250 NORTH,20160104,4350 NORTH,20160105,3190
sale_2017.txt
region,time_id,amount EAST,20170101,8000 EAST,20170102,7000 EAST,20170103,6500 EAST,20170104,3450 EAST,20170105,9000 SOUTH,20170101,2000 SOUTH,20170102,3120 SOUTH,20170103,2300 SOUTH,20170104,5850 SOUTH,20170105,1900 WEST,20170101,3400 WEST,20170102,2400 WEST,20170103,5900 WEST,20170104,5450 WEST,20170105,1780 NORTH,20170101,2000 NORTH,20170102,1000 NORTH,20170103,3000 NORTH,20170104,2350 NORTH,20170105,2190
sale_2018.txt
region,time_id,amount EAST,20180101,2100 EAST,20180102,7800 EAST,20180103,6900 EAST,20180104,9450 EAST,20180105,9700 SOUTH,20180101,1300 SOUTH,20180102,2120 SOUTH,20180103,6308 SOUTH,20180104,2850 SOUTH,20180105,7900 WEST,20180101,3800 WEST,20180102,2600 WEST,20180103,5200 WEST,20180104,5250 WEST,20180105,2980 NORTH,20180101,2120 NORTH,20180102,1230 NORTH,20180103,3500 NORTH,20180104,2050 NORTH,20180105,1060
sale_2019.sql
insert into hybrid_test values('EAST', to_date('20190101','yyyy-mm-dd'),1032); insert into hybrid_test values('EAST', to_date('20190102','yyyy-mm-dd'),2371); commit;##通过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 YES PDB1@ZRP>select table_name,partition_name,tablespace_name,logging,read_only from dba_tab_partitions where table_owner='ZRP';##插入几行数据（只能插入到内部分区（internal partitions），我这里是sales_2019)
PDB1@ZRP>select * from hybrid_test partition (sales_2015); no rows selected PDB1@ZRP>insert into hybrid_test values('EAST', to_date('20190101','yyyy-mm-dd'),1032); 1 row created. PDB1@ZRP>insert into hybrid_test values('EAST', to_date('20190102','yyyy-mm-dd'),2371); 1 row created. PDB1@ZRP>commit; Commit complete.##查看各分区的数据
PDB1@ZRP>select * from hybrid_test partition (sales_2019); REGION TIME_ID AMOUNT ------ ------------------- ---------- EAST 2019-01-01 00:00:00 1032 EAST 2019-01-02 00:00:00 2371 PDB1@ZRP>select * from hybrid_test partition (sales_2015); no rows selected PDB1@ZRP>select * from hybrid_test partition (sales_2016); REGION TIME_ID AMOUNT ------ ------------------- ---------- EAST 2016-01-01 00:00:00 6000 EAST 2016-01-02 00:00:00 3000 EAST 2016-01-03 00:00:00 9012 ... NORTH 2016-01-03 00:00:00 1250 NORTH 2016-01-04 00:00:00 4350 NORTH 2016-01-05 00:00:00 3190 20 rows selected. PDB1@ZRP>select * from hybrid_test partition (sales_2017); REGION TIME_ID AMOUNT ------ ------------------- ---------- EAST 2017-01-01 00:00:00 8000 EAST 2017-01-02 00:00:00 7000 EAST 2017-01-03 00:00:00 6500 ... NORTH 2017-01-03 00:00:00 3000 NORTH 2017-01-04 00:00:00 2350 NORTH 2017-01-05 00:00:00 2190 20 rows selected. PDB1@ZRP>select * from hybrid_test partition (sales_2018); REGION TIME_ID AMOUNT ------ ------------------- ---------- EAST 2018-01-01 00:00:00 2100 EAST 2018-01-02 00:00:00 7800 EAST 2018-01-03 00:00:00 6900 ... NORTH 2018-01-03 00:00:00 3500 NORTH 2018-01-04 00:00:00 2050 NORTH 2018-01-05 00:00:00 1060 20 rows selected.1）第一步删除external partitions
2）第二步删除external partition attributes
执行下面删除分区语句
alter table hybrid_test drop partition sales_2015; alter table hybrid_test drop partition sales_2016; alter table hybrid_test drop partition sales_2017; alter table hybrid_test drop partition sales_2018; 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 NO PDB1@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_2019 USERS YES NO已经转换为传统分区表
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_2015 VALUES LESS THAN (TO_DATE('2016-01-01','yyyy-mm-dd')) EXTERNAL; ADD PARTITION sales_2015 VALUES LESS THAN (TO_DATE('2016-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_2019拆分
alter table hybrid_test split partition sales_2019 into ( partition sales_2014 VALUES LESS THAN (TO_DATE('2015-01-01','yyyy-mm-dd')), partition sales_2019 ); --创建一个中间表 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_2019 with table hybrid_test_temp; -- 然后删除这个sales_2019 alter table hybrid_test drop partition sales_2019; -- 接下来就可以添加外部分区了 PDB1@ZRP>alter table hybrid_test ADD PARTITION sales_2015 VALUES LESS THAN (TO_DATE('2016-01-01','yyyy-mm-dd')) EXTERNAL; Table altered. PDB1@ZRP>alter table hybrid_test ADD PARTITION sales_2016 VALUES LESS THAN (TO_DATE('2017-01-01','yyyy-mm-dd')) EXTERNAL DEFAULT DIRECTORY sales_data_2016 LOCATION ('sales_2016.txt'); Table altered. PDB1@ZRP>alter table hybrid_test ADD PARTITION sales_2017 VALUES LESS THAN (TO_DATE('2018-01-01','yyyy-mm-dd')) EXTERNAL DEFAULT DIRECTORY sales_data_2017 LOCATION ('sales_2017.txt'); Table altered. PDB1@ZRP>alter table hybrid_test ADD PARTITION sales_2018 VALUES LESS THAN (TO_DATE('2019-01-01','yyyy-mm-dd')) EXTERNAL LOCATION ('sales_2018.txt'); Table altered. PDB1@ZRP>alter table hybrid_test ADD PARTITION sales_2019 VALUES LESS THAN (TO_DATE('2020-01-01','yyyy-mm-dd')); Table altered. -- 最后再把刚才交换出去的数据交换回来，这样就恢复原样了 PDB1@ZRP>alter table hybrid_test exchange partition sales_2019 with table hybrid_test_temp; Table altered. PDB1@ZRP>select * from hybrid_test partition (sales_2019); REGION TIME_ID AMOUNT ------ ------------------- ---------- EAST 2019-01-01 00:00:00 1032 EAST 2019-01-02 00:00:00 2371
-- 删除临时过渡分区 PDB1@ZRP>alter table hybrid_test drop partition sales_2014; 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 YES PDB1@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_2015 USERS NO YES HYBRID_TEST SALES_2016 USERS NO YES HYBRID_TEST SALES_2017 USERS NO YES HYBRID_TEST SALES_2018 USERS NO YES HYBRID_TEST SALES_2019 USERS YES NO PDB1@ZRP>select * from hybrid_test partition(sales_2016); REGION TIME_ID AMOUNT ------ ------------------- ---------- EAST 2016-01-01 00:00:00 6000 EAST 2016-01-02 00:00:00 3000 EAST 2016-01-03 00:00:00 9012 ... NORTH 2016-01-03 00:00:00 1250 NORTH 2016-01-04 00:00:00 4350 NORTH 2016-01-05 00:00:00 3190 20 rows selected. 原文： http://www.sohu.com/a/297399785_505827
