概念描述
从Oracle Database 19c开始，Oracle数据库支持Hybridpartitioned tables，也就是混合分区表，进一步扩展了Oracle分区技术。这里的混合指的是数据的分布，一些分区可以位于数据库中，另一些可以是位于数据库外部的文件
测试验证
示例：创建一个按年分区，名为trade_record的交易表混合分区表。
创建外部数据存放目录
[oracle@devin-enmo ~]$ mkdir /home/oracle/trade
SQL> create directory trade_record as '/home/oracle/trade';
Directory created.
构造历史数据
[oracle@devin-enmo trade]$ vi trade_2020.txt
1,2020-01-01,email1@gmail.com
2,2020-02-02,email2@gmail.com
3,2020-03-03,email3@gmail.com
4,2020-04-04,email4@gmail.com
5,2020-05-05,email5@gmail.com
6,2020-06-06,email6@gmail.com
[oracle@devin-enmo trade]$ vi trade_2021.txt
11,2021-01-01,email11@gmail.com
12,2021-02-02,email12@gmail.com
13,2021-03-03,email13@gmail.com
14,2021-04-04,email14@gmail.com
15,2021-05-05,email15@gmail.com
16,2021-06-06,email16@gmail.com
创建混合分区表
注：混合分区表由于不支持全局唯一，主键等，所以创建分区表的时候不能指定主键，否则报错ORA-14354: operation not supported for a hybrid-partitioned table
SQL> create table trade_record
(
trade_no number not null,
trade_date date,
trade_email nvarchar2(50)
)
external partition attributes
(
type oracle_loader
default directory trade_record
access parameters (
fields terminated by ','
(trade_no,trade_date DATE 'YYYY-MM-DD',trade_email)
)
reject limit unlimited
)
partition by range(trade_date)
(
partition p_2020 values less than (to_date('2021-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) external location('trade_2020.txt'),
partition p_2021 values less than (to_date('2022-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) external location('trade_2021.txt'),
partition p_2022 values less than (to_date('2023-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition pmax values less than (maxvalue)
);
Table created.
## type:指定外部表的访问驱动，Oracle数据库提供的访问驱动包括:ORACLE_LOADER、ORACLE_DATAPUMP、ORACLE_HDFS、ORACLE_HIVE。如果没有指定TYPE，那么数据库将使用ORACLE_LOADER作为默认访问驱动程序。
## default directory:指定外部数据默认的存放目录
## access parameters：指定外部表访问驱动的参数，比如列映射，分隔符等等
## reject limit：指定在查询外部数据时发生多少转换错误，然后返回Oracle数据库错误并终止查询。缺省值为0。为0的时候如果外部数据加载错误，查询则会报ORA-30653: reject limit reached错误，并且加载错误时日志及bad文件会记录在当前外部数据目录下，便于排查。
## location：指定一个或多个数据源
## 往内部分区插入数据
SQL> insert all
into trade_record values (21,to_date('2022-01-01','yyyy-mm-dd'),'email21@gmail.com')
into trade_record values (22,to_date('2022-02-02','yyyy-mm-dd'),'email22@gmail.com')
into trade_record values (23,to_date('2022-03-03','yyyy-mm-dd'),'email23@gmail.com')
select 1 from dual;
3 rows created.
SQL> commit;
Commit complete.
数据验证
SQL> select table_name,partitioned,hybrid from user_tables where table_name='TRADE_RECORD';
TABLE_NAME PAR HYB
-------------------- --- ---
TRADE_RECORD YES YES
## HYB=YES，表示为混合分区表
SQL> select count(*) from trade_record;
COUNT(*)
----------
15
SQL> select * from trade_record;
TRADE_NO TRADE_DAT TRADE_EMAIL
---------- --------- --------------------------------------------------
1 01-JAN-20 username1
2 02-FEB-20 username2
3 03-MAR-20 username3
4 04-APR-20 username4
5 05-MAY-20 username5
6 06-JUN-20 username6
11 01-JAN-21 username11
12 02-FEB-21 username12
13 03-MAR-21 username13
14 04-APR-21 username14
15 05-MAY-21 username15
16 06-JUN-21 username16
21 01-JAN-22 email21@gmail.com
22 02-FEB-22 email22@gmail.com
23 03-MAR-22 email23@gmail.com
15 rows selected.
SQL> select * from trade_record partition(p_2020);
TRADE_NO TRADE_DAT TRADE_EMAIL
---------- --------- --------------------------------------------------
1 01-JAN-20 username1
2 02-FEB-20 username2
3 03-MAR-20 username3
4 04-APR-20 username4
5 05-MAY-20 username5
6 06-JUN-20 username6
6 rows selected.
DML测试
## INSERT
SQL> insert into trade_record values(24,to_date('2022-04-04','yyyy-mm-dd'),'email24@gmail.com');
1 row created.
SQL> insert into trade_record values(7,to_date('2020-07-07','yyyy-mm-dd'),'email7@gmail.com');
insert into trade_record values(7,to_date('2020-07-07','yyyy-mm-dd'),'email7@gmail.com')
*
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.
## DELETE
SQL> delete from trade_record where trade_no=1;
delete from trade_record where trade_no=1
*
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.
## UPDATE
SQL> update trade_record set trade_email='email01@gmail.com' where trade_no=1;
update trade_record set trade_email='email01@gmail.com' where trade_no=1
*
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.
## 结论：外部分区不支持DML操作
索引及约束相关测试
## 主键约束
SQL> alter table trade_record add constraints pk_trade_record primary key (trade_no);
alter table trade_record add constraints pk_trade_record primary key (trade_no)
*
ERROR at line 1:
ORA-14354: operation not supported for a hybrid-partitioned table
## 全局唯一索引
SQL> create unique index idx_tradeno on trade_record(trade_no);
create unique index idx_tradeno on trade_record(trade_no)
*
ERROR at line 1:
ORA-14354: operation not supported for a hybrid-partitioned table
## 全局普通索引
SQL> create index idx_tradeno on trade_record(trade_no);
create index idx_tradeno on trade_record(trade_no)
*
ERROR at line 1:
ORA-14354: operation not supported for a hybrid-partitioned table
## local索引
SQL> create index idx_tradeno on trade_record(trade_no) local indexing partial;
Index created.
SQL> select index_name,partition_name,status from user_ind_partitions where index_name='IDX_TRADENO';
INDEX_NAME PARTITION_ STATUS
-------------------- ---------- --------
IDX_TRADENO P_2020 UNUSABLE
IDX_TRADENO P_2021 UNUSABLE
IDX_TRADENO PMAX USABLE
IDX_TRADENO P_2022 USABLE
## 全局的部分索引：只对那些需要索引的分区创建索引，别的分区不会创建
SQL> create index idx_tradeno on trade_record(trade_no) global indexing partial;
Index created.
SQL> select index_name,status,partitioned,indexing from user_indexes where table_name='TRADE_RECORD';
INDEX_NAME STATUS PAR INDEXIN
--------------- -------- --- -------
IDX_TRADENO VALID NO PARTIAL
### 总结：
# 混合分区表不支持主键
# 不支持全局唯一索引
# 不支持全局普通索引
# 支持部分分区的全局索引
# 支持local索引，但是外部分区部分为unusable状态。
混合分区表和传统分区表的相互转换
混合分区表转化为传统分区表
## 删除外部分区
SQL> alter table trade_record drop partition p_2020;
Table altered.
SQL> alter table trade_record drop partition p_2021;
Table altered.
## 删除外部分区相关属性
SQL> alter table trade_record drop external partition attributes();
Table altered.
SQL> select table_name,partitioned,hybrid from user_tables where table_name='TRADE_RECORD';
TABLE_NAME PAR HYB
-------------------- --- ---
TRADE_RECORD YES NO
## HYB=NO，表示普通分区表
传统分区表转化为混合分区表
## 增加外部分区属性
SQL> alter table trade_record
add external partition attributes
(type oracle_loader
default directory trade_record
access parameters (
fields terminated by ','
(trade_no,trade_date DATE 'YYYY-MM-DD',trade_email)
)
reject limit unlimited);
Table altered.
## 查看当前数据，都是2022年的
SQL> select * from trade_record;
TRADE_NO TRADE_DAT TRADE_EMAIL
---------- --------- --------------------------------------------------
21 01-JAN-22 email21@gmail.com
22 02-FEB-22 email22@gmail.com
23 03-MAR-22 email23@gmail.com
24 04-APR-22 email24@gmail.com
## 由于新增分区无法小于当前分区最大值，所以只能通过split的方式来增加外部分区
SQL> alter table trade_record split partition p_2022 into
(partition p_2020 values less than (to_date('2021-01-01','yyyy-mm-dd')) external location ('trade_2020.txt'),
partition p_2022
);
Table altered.
SQL> alter table trade_record split partition p_2022 into
(partition p_2021 values less than (to_date('2022-01-01','yyyy-mm-dd')) external location ('trade_2021.txt'),
partition p_2022
);
Table altered.
## 数据验证
SQL> select table_name,partitioned,hybrid from user_tables where table_name='TRADE_RECORD';
TABLE_NAME PAR HYB
--------------- --- ---
TRADE_RECORD YES YES <-----
SQL> select * from trade_record;
TRADE_NO TRADE_DAT TRADE_EMAIL
---------- --------- --------------------------------------------------
1 01-JAN-20 username1
2 02-FEB-20 username2
3 03-MAR-20 username3
4 04-APR-20 username4
5 05-MAY-20 username5
6 06-JUN-20 username6
11 01-JAN-21 username11
12 02-FEB-21 username12
13 03-MAR-21 username13
14 04-APR-21 username14
15 05-MAY-21 username15
16 06-JUN-21 username16
21 01-JAN-22 email21@gmail.com
22 02-FEB-22 email22@gmail.com
23 03-MAR-22 email23@gmail.com
24 04-APR-22 email24@gmail.com
16 rows selected.
## 可以看到外部数据已经加载成功
混合分区表查询的执行计划
SQL> select index_name,partition_name,status from user_ind_partitions where index_name='IDX_TRADENO';
INDEX_NAME PARTITION_ STATUS
-------------------- ---------- --------
IDX_TRADENO P_2020 UNUSABLE
IDX_TRADENO P_2021 UNUSABLE
IDX_TRADENO P_2022 USABLE
SQL> set linesize 500
SQL> set autot trace
SQL> select * from trade_record where trade_no = 13;
Execution Plan
----------------------------------------------------------
Plan hash value: 1768832168
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 985 | 97515 | 58 (0)| 00:00:01 | | |
| 1 | VIEW | VW_TE_2 | 10 | 990 | 58 (0)| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
| 3 | PARTITION RANGE SINGLE | | 2 | 198 | 2 (0)| 00:00:01 | 3 | 3 |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TRADE_RECORD | 2 | 198 | 2 (0)| 00:00:01 | 3 | 3 |
|* 5 | INDEX RANGE SCAN | IDX_TRADENO | 329 | | 1 (0)| 00:00:01 | 3 | 3 |
| 6 | PARTITION RANGE ITERATOR | | 8 | 792 | 56 (0)| 00:00:01 | 1 | 2 |
|* 7 | EXTERNAL TABLE ACCESS FULL | TRADE_RECORD | 8 | 792 | 56 (0)| 00:00:01 | 1 | 2 |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("TRADE_NO"=13)
filter(SYS_OP_XTNN("TRADE_RECORD"."TRADE_NO"))
7 - filter(SYS_OP_XTNN("TRADE_RECORD"."TRADE_NO") AND "TRADE_NO"=13)
## 总结：由于混合分区表的外部分区部分是不支持索引的，所以在数据查询的时候，执行计划会拆分成两部分，内部分区走索引扫描，外部分区全表扫描，然后进行union all
不规范数据验证
当外部数据中存在不规范的数据，比如不符合分区条件的数据，外部分区是否能加载成功？查询的时候是否能正确筛选，过滤？？继续验证：
## 1，构建不规范数据，将2021分区中添加2022和2020年的数据
[oracle@devin-enmo trade]$ cat trade_2021.txt
11,2021-01-01,email11@gmail.com
12,2021-02-02,email12@gmail.com
13,2021-03-03,email13@gmail.com
14,2021-04-04,email14@gmail.com
15,2021-05-05,email15@gmail.com
16,2021-06-06,email16@gmail.com
17,2022-06-06,email17@gmail.com <-----
18,2020-06-06,email18@gmail.com <-----
## 验证不规范数据是否加载成功
SQL> select * from trade_record partition(p_2021);
TRADE_NO TRADE_DAT TRADE_EMAIL
---------- --------- --------------------------------------------------
11 01-JAN-21 email11@gmail.com
12 02-FEB-21 email12@gmail.com
13 03-MAR-21 email13@gmail.com
14 04-APR-21 email14@gmail.com
15 05-MAY-21 email15@gmail.com
16 06-JUN-21 email16@gmail.com
17 06-JUN-22 email17@gmail.com <-----
18 06-JUN-20 email18@gmail.com <-----
8 rows selected.
# 可以看到不规范的数据是能加载成功的，即便违反了分区条件
## 验证查询是否能筛选，过滤不规范数据
SQL> select * from trade_record where trade_date >= to_date('2022-01-01','yyyy-mm-dd');
TRADE_NO TRADE_DAT TRADE_EMAIL
---------- --------- --------------------------------------------------
21 01-JAN-22 email21@gmail.com
22 02-FEB-22 email22@gmail.com
23 03-MAR-22 email23@gmail.com
6 rows selected.
## PARTITION P_2021中的不规范数据，即便符合where条件，也未能查询到
SQL> select * from trade_record where trade_date < to_date('2022-01-01','yyyy-mm-dd');
TRADE_NO TRADE_DAT TRADE_EMAIL
---------- --------- --------------------------------------------------
1 01-JAN-20 email1@gmail.com
2 02-FEB-20 email2@gmail.com
3 03-MAR-20 email3@gmail.com
4 04-APR-20 email4@gmail.com
5 05-MAY-20 email5@gmail.com
6 06-JUN-20 email6@gmail.com
11 01-JAN-21 email11@gmail.com
12 02-FEB-21 email12@gmail.com
13 03-MAR-21 email13@gmail.com
14 04-APR-21 email14@gmail.com
15 05-MAY-21 email15@gmail.com
16 06-JUN-21 email16@gmail.com
17 06-JUN-22 email17@gmail.com <-----
18 06-JUN-20 email18@gmail.com
14 rows selected.
## partition p_2021中的不规范数据，即便不符合where条件，也未能过滤掉
## 总结，当外部数据源存在不规范数据时，即使不符合分区条件规则，混合分区表同样能加载到指定分区，但是在查询时，这些不规范的数据并不能正常筛选及过滤。
知识总结
混合分区表特性扩展了Oracle分区，使分区既可以驻留在Oracle数据库段中，也可以驻留在外部文件和源中。这个特性显著地增强了大数据SQL的分区功能，其中表的大部分可以驻留在外部分区中。混合分区表使您能够将内部分区和外部分区集成到单个分区表中。有了这个特性，您还可以将非活动分区移动到外部文件，例如Oracle Data Pump文件，从而获得更便宜的存储解决方案。
混合分区表也存在很多限制，如下：
除非显式说明，否则适用于外部表的限制也适用于混合分区表
不支持REFERENCE和SYSTEM分区方法
只支持单级LIST和RANGE分区
没有唯一索引或全局唯一索引。只允许部分索引，唯一索引不能是部分索引
HIVE只支持单级列表分区
不允许属性聚类(clustering子句)
仅在混合分区表的内部分区上进行DML操作(外部分区被视为只读分区)
在表级别上定义的in-memory中只对混合分区表的内部分区有影响
无列默认值
不允许有不可见的列
不允许使用CELLMEMORY子句
不允许对外部分区进行SPLIT、MERGE和MOVE维护操作
不允许使用LOB、LONG和ADT类型
只允许使用RELY约束
最后修改时间：2022-12-19 10:52:28
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
