【新特性】05.混合分区表
特性：混合分区表。简单来讲，可以认为是将之前的分区表和外部表结合起来，当然混合分区表的功能不只这些。一般情况下，我们使用分区表，是将各个分区对应不同的表空间，各个表空间可以对应不同位置、不同种类的存储，但一般都为常规文件系统。而19c当中的混合分区表，在建表的时候，直接可以使用外部表属性，将“冷”数据直接定位在廉价存储、HDFS或者公有云存储上。
创建混合分区表
一、环境准备
[oracle@T1 XieBro]$ pwd /opt/XieBro [oracle@T1 XieBro]$ cat stu2019.txt 1 ZhangSan 19 19-SEP-2019 2 Li 20 20-FEB-2019 [oracle@T1 XieBro]$ cat stu2020.txt 3 WangWu 20 20-FEB-2020 4 ZhaoLiu 21 23-JAN-2020
二、进入pdb创建mix_table用户，并授权
SQL> create user mix_table identified by mix_table default tablespace users quota unlimited on users; User created. SQL> grant dba,connect,resource to mix_table; Grant succeeded.
三、创建一个目录对象
SQL> create directory mix_table as '/opt/XieBro/'; Directory created. SQL> grant read,write on directory mix_table to public; Grant succeeded.
四、建表
CREATE TABLE mix_table.stu (id number(10), name varchar2(20), age number, enroll_dt date) EXTERNAL PARTITION ATTRIBUTES ( TYPE oracle_loader DEFAULT DIRECTORY mix_table ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY WHITESPACE) ) PARTITION BY RANGE (enroll_dt) (PARTITION stu2019 VALUES LESS THAN (TO_DATE('01-JAN-2020','DD-MON-YYYY')) EXTERNAL LOCATION ('stu2019.txt'), PARTITION stu2020 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY')) EXTERNAL LOCATION ('stu2020.txt'), PARTITION stu2021 VALUES LESS THAN (TO_DATE('01-JAN-2022','DD-MON-YYYY')) );
查看数据
SQL> select * from mix_table.stu; ID NAME AGE ENROLL_DT ---------- -------------------- ---------- --------- 1 ZhangSan 19 19-SEP-19 2 Li 20 20-FEB-19 3 WangWu 20 20-FEB-20 4 ZhaoLiu 21 23-JAN-20
五、插入数据
与以前版本的外部表不同，以前版本的外部表都是以查询为目的，不会对里面的数据进行更改，而在19c的混合分区表中，可以对其中的数据进行更改，这种更改包括插入、更新和删除。我们现在向“数据库内分区”插入数据看看。2021年这个分区是数据库内的分区。
SQL> insert into mix_table.stu values(100,'SunQi',30,to_date('2021-6-6','yyyy-mm-dd')); 1 row created. SQL> commit; Commit complete. SQL> select * from mix_table.stu; ID NAME AGE ENROLL_DT ---------- -------------------- ---------- --------- 1 ZhangSan 19 19-SEP-19 2 Li 20 20-FEB-19 3 WangWu 20 20-FEB-20 4 ZhaoLiu 21 23-JAN-20 100 SunQi 30 06-JUN-21
接下来，我们向外部分区插入数据看看。2019年和2020年的数据是被定义在外部分区上，我们对这个分区插入数据，看到如下结果。
SQL> insert into mix_table.stu values(101,'ZhouBa',30,to_date('2020-6-6','yyyy-mm-dd')); insert into mix_table.stu values(101,'ZhouBa',30,to_date('2020-6-6','yyyy-mm-dd')) * ERROR at line 1: ORA-14466: Data in a read-only partition or subpartition cannot be modified.
将混合分区变为普通分区
当不想使用混合分区表时，只要将他的外部分区都删除，然后再删除它的外部分区属性，那么就可以将混合分区表转换为普通分区表了。在下面的实验中，我们先创建一个混合分区表，然后看看它的属性，接下来将它的所有外部分区都删除，再删除它的外部分区属性，之后再看看这个表的属性。
第一步：创建一个混合分区表
CREATE TABLE mix_table.stu_normal (id number(10), name varchar2(20), age number, enroll_dt date) EXTERNAL PARTITION ATTRIBUTES ( TYPE oracle_loader DEFAULT DIRECTORY mix_table ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY WHITESPACE) ) PARTITION BY RANGE (enroll_dt) (PARTITION stu2019 VALUES LESS THAN (TO_DATE('01-JAN-2020','DD-MON-YYYY')) EXTERNAL LOCATION ('stu2019.txt'), PARTITION stu2020 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY')) EXTERNAL LOCATION ('stu2020.txt'), PARTITION stu2021 VALUES LESS THAN (TO_DATE('01-JAN-2022','DD-MON-YYYY')), PARTITION stu2022 VALUES LESS THAN (TO_DATE('01-JAN-2023','DD-MON-YYYY')), PARTITION stu2023 VALUES LESS THAN (TO_DATE('01-JAN-2024','DD-MON-YYYY')) );
第二步：看看这个表的分区属性
SQL> conn mix_table/mix_table@pdb Connected. SQL> SELECT HYBRID FROM DBA_TABLES WHERE TABLE_NAME='STU_NORMAL'; HYB --- YES
通过查询，我们发现，刚创建的stu_normal是一个混合分区表。
第三步：删除所有外部分区，并删除分区属性
SQL> ALTER TABLE MIX_TABLE.STU_NORMAL DROP PARTITION STU2019; Table altered. SQL> ALTER TABLE MIX_TABLE.STU_NORMAL DROP PARTITION STU2020; Table altered. SQL> ALTER TABLE MIX_TABLE.STU_NORMAL DROP EXTERNAL PARTITION ATTRIBUTES(); Table altered. SQL> SELECT HYBRID FROM DBA_TABLES WHERE TABLE_NAME='STU_NORMAL'; HYB --- NO
stu_normal有两个外部分区，我们先将这两个外部分区都删除，然后在删除这个表的外部分区属性。这时候查看表的混合分区属性时，就会得到下图中的“NO”，表示这个表已经不是一个混合分区表了。
混合分区表中的ADO
Automatic Data Optimization (ADO)，自动数据优化策略。在之前版本的数据库中就提供了，我们可以设定策略，比如对于很久不访问的数据进行压缩。在19c的混合分区表中，也可以使用这项技术，但仅限于数据库内的分区。我们在下面的例子中，为2021这个分区设定数据压缩策略，对那些6个月没有修改的数据进行压缩。
CREATE TABLE mix_table.stu_ado (id number(10), name varchar2(20), age number, enroll_dt date) EXTERNAL PARTITION ATTRIBUTES ( TYPE oracle_loader DEFAULT DIRECTORY mix_table ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY WHITESPACE) ) PARTITION BY RANGE (enroll_dt) (PARTITION stu2019 VALUES LESS THAN (TO_DATE('01-JAN-2020','DD-MON-YYYY')) EXTERNAL LOCATION ('stu2019.txt'), PARTITION stu2020 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY')) EXTERNAL LOCATION ('stu2020.txt'), PARTITION stu2021 VALUES LESS THAN (TO_DATE('01-JAN-2022','DD-MON-YYYY')) );
SQL> SELECT HYBRID FROM DBA_TABLES WHERE TABLE_NAME='STU_ADO'; HYB --- YES SQL> ALTER TABLE MIX_TABLE.STU_ADO MODIFY PARTITION STU2021 ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 6 MONTHS OF NO MODIFICATION; Table altered. SQL> COL POLICY_NAME FOR A5 POLIC POLICY_TYPE ENA ----- ------------- --- P1 DATA MOVEMENT YES
可以看到策略已经应用成功
混合分区表split分区
在混合分区表当中，我们可以像之前版本的数据库一样，对于默认分区进行拆分。比如下面的例子当中，2020年之后的数据都将进入默认分区stu_future。我们现在对这个分区进行拆分，拆分成2021分区和stu_future，stu_future将继续作为默认分区。
CREATE TABLE mix_table.stu_split (id number(10), name varchar2(20), age number, enroll_dt date) EXTERNAL PARTITION ATTRIBUTES ( TYPE oracle_loader DEFAULT DIRECTORY mix_table ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY WHITESPACE) ) PARTITION BY RANGE (enroll_dt) (PARTITION stu2019 VALUES LESS THAN (TO_DATE('01-JAN-2020','DD-MON-YYYY')) EXTERNAL LOCATION ('stu2019.txt'), PARTITION stu2020 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY')), PARTITION stufuture VALUES LESS THAN (MAXVALUE) );
在创建stu_split之后，查询一下它的混合分区属性，发现他是混合分区表。然后我们将它的默认分区拆分成2021分区和stu_future分区，将2021年之后的数据都放入默认分区stu_future。我们再次查询这个表的混合分区属性，发现它还是混合分区表，最后看看这个混合分区表的分区信息，我们发现新添加的2021分区已经显示出来了。
SQL> ALTER TABLE MIX_TABLE.STU_SPLIT SPLIT PARTITION stufuture INTO (PARTITION stu2021 VALUES LESS THAN(TO_DATE('01-01-2022','dd-mm-yyyy')), PARTITION stufuture); Table altered. SQL> col table_name for a10 SQL> col partition_name for a15 TABLE_NAME PARTITION_NAME ---------- --------------- STU_SPLIT STU2019 STU_SPLIT STU2020 STU_SPLIT STU2021 STU_SPLIT STUFUTURE
