Oracle 19c 新特性 —— 混合分区表Hybrid partitioned tables

最新推荐文章于 2022-12-13 17:01:15 发布

Hehuyi_In

最新推荐文章于 2022-12-13 17:01:15 发布

阅读量2.5k

点赞数

分类专栏： Oracle 19c 新特性

原文链接：http://www.sohu.com/a/297399785_505827

版权

Oracle 同时被 3 个专栏收录

189 篇文章 33 订阅

订阅专栏

新特性

22 篇文章 0 订阅

订阅专栏

19c

5 篇文章 2 订阅

订阅专栏

简介

从19c开始，Oracle数据库支持Hybrid partitioned tables，也就是混合分区表，进一步扩展了Oracle分区技术。这里的混合指的是数据的分布，分区表的分区可以一些位于数据库中，另一些位于数据库外部的文件（比如操作系统文件或HDFS文件）。这个特性的出现，其实一点也不奇怪，因为从12.2开始就支持了外部表分区、只读分区，在19c中只是将内部分区（internal partitions）与Oracle外部分区（external partitions）特性结合起来，形成一个更通用的分区，称为混合分区表。

混合分区的现实意义还是比较明显的，首先通过混合分区可以轻松地将内部（驻留在Oracle表空间中--internal partitions）和外部的数据（external partitions）集成到单个分区表中，其次可以方便地将非活跃数据移动到外部文件，在而降低存储成本的同时也更加方便数据交换。

特点

1) 混合分区表支持外部分区的所有现有外部表类型

ORACLE_DATAPUMP
ORACLE_LOADER
ORACLE_HDFS
ORACLE_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_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;

2）定义DIRECTORY

CREATE DIRECTORY sales_data as '/u01/app/oracle/oradata/extfiles';       
CREATE DIRECTORY sales_data_2016 as '/u01/app/oracle/oradata/extfiles/2016';       
CREATE DIRECTORY sales_data_2017 as '/u01/app/oracle/oradata/extfiles/2017';

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_LOADER
DEFAULT DIRECTORY sales_data
ACCESS PARAMETERS( FIELDS TERMINATED BY ','
(region,time_id DATE 'yyyy-mm-dd',amount)
)
REJECT LIMIT UNLIMITED
)
PARTITION BY RANGE (time_id)
( 
PARTITION sales_2015 VALUES LESS THAN (TO_DATE('2016-01-01','yyyy-mm-dd')) EXTERNAL, --空的外部分区
PARTITION sales_2016 VALUES LESS THAN (TO_DATE('2017-01-01','yyyy-mm-dd'))
EXTERNAL DEFAULT DIRECTORY sales_data_2016 LOCATION ('sales_2016.txt'),
PARTITION sales_2017 VALUES LESS THAN (TO_DATE('2018-01-01','yyyy-mm-dd'))
EXTERNAL DEFAULT DIRECTORY sales_data_2017 LOCATION ('sales_2017.txt'),
PARTITION sales_2018 VALUES LESS THAN (TO_DATE('2019-01-01','yyyy-mm-dd')) EXTERNAL LOCATION ('sales_2018.txt'),
PARTITION sales_2019 VALUES LESS THAN (TO_DATE('2020-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 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.

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 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 SYSTEM NO YES
HYBRID_TEST SALES_2016 SYSTEM NO YES
HYBRID_TEST SALES_2017 SYSTEM NO YES
HYBRID_TEST SALES_2018 SYSTEM NO YES
HYBRID_TEST SALES_2019 USERS YES NO

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

已经转换为传统分区表

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_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

Hehuyi_In

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
Oracle 19c 新特性 —— 混合分区表Hybrid partitioned tables

简介从19c开始，Oracle数据库支持Hybrid partitioned tables，也就是混合分区表，进一步扩展了Oracle分区技术。这里的混合指的是数据的分布，分区表的分区可以一些位于数据库中，另一些位于数据库外部的文件（比如操作系统文件或HDFS文件）。这个特性的出现，其实一点也不奇怪，因为从12.2开始就支持了外部表分区、只读分区，在19c中只是将内部分区（internal p...
复制链接

扫一扫