ORACLE 19C的混合分区表使用场景

介绍

       数据库分区技术是业务需要的产物，一方面可以通过分区提升并发IO能力，另一方面也是解决历史数据维护难点。

       从11g开始，分区技术趋于成熟，特别是间隔分区和复合分区的出现，将过去外部触发实现分区逻辑的变更的方式变成了自动实现新分区的增加，一定程度上也降低了外部分区程序故障导致无法添加新分区而导致的数据无法插入问题。

      在后来的12C、18C、19C中分区都有新功能实现，从分区交换合并的便捷性到外部表和分区表的技术融合，目标就是适应大体量数据的并发和历史数据维护。

      分区类型很多，但目标其实只有两个，并发和时间维度的数据拆分。

      本次主要是结合19C的混合分区表技术探讨历史数据维护的新思路。

技术要点

1、创建一个目录对象，提供外部表保存

2、创建一个分区表和普通表，表结构一致，后续用来数据交换

2、手动编写一个csv文件作为外表数据表的数据

3、创建混合分区表，测试数据交换新建

• 表信息

     --创建时间分区表
   create table P_TPA_RADIO_SUM 
(id RAW (16) NOT NULL,
 stime date default sysdate,
 p1 integer,
 p2 integer,
 p3 integer,
 p4 integer
 )
  partition by range(stime)( 
  PARTITION stime_p1 VALUES LESS THAN (TO_DATE('01-01-2015','dd-mm-yyyy')),
  PARTITION stime_p2 VALUES LESS THAN (TO_DATE('01-01-2016','dd-mm-yyyy')),
  PARTITION stime_p3 VALUES LESS THAN (TO_DATE('01-01-2017','dd-mm-yyyy')),
  PARTITION stime_p4 VALUES LESS THAN (TO_DATE('01-01-2018','dd-mm-yyyy')),
  PARTITION stime_p5 VALUES LESS THAN (TO_DATE('01-01-2019','dd-mm-yyyy')),
  PARTITION stime_p6 VALUES LESS THAN (TO_DATE('01-01-2020','dd-mm-yyyy'))
   );  
    
    --创建普通表，用来和分区表交换数据

    create table TPA_RADIO_SUM as select * from P_TPA_RADIO_SUM;


复制

• 插入模拟数据

$
insert into P_TPA_RADIO_SUM 
SELECT 
SYS_GUID(),
SYSDATE-dbms_random.value(0,2000),   --随机生成时间数据
dbms_random.random random,
dbms_random.random random,
dbms_random.random random,
dbms_random.random random
from dual
CONNECT BY LEVEL<=1000
复制

• 通过命令进行数据交换

    alter table P_TPA_RADIO_SUM exchange partition  stime_p1  with table TPA_RADIO_SUM;


复制

• 两次执行分区交换命令

  第一次执行将分区 stime_p1 的数据交换给表 TPA_RADIO_SUM

  第二次执行，将表 TPA_RADIO_SUM 的数据添加到P_TPA_RADIO_SUM 的stime_p1分区

  
  

• 创建外部表环境和外部表

   CREATE DIRECTORY TPA_DIR AS '/opt/oracle/product/19c/dbhome_1/test';
GRANT READ,WRITE ON DIRECTORY TPA_DIR TO c##boco;  --给c#boco用户权限
   
   create table E_P_TPA_RADIO_SUM 
     (id RAW (16) NOT NULL,
     stime date default sysdate,
     p1 integer,
     p2 integer,
     p3 integer,
     p4 integer
    )
 EXTERNAL PARTITION ATTRIBUTES (
      TYPE ORACLE_LOADER 
      DEFAULT DIRECTORY TPA_DIR
       ACCESS PARAMETERS(
         FIELDS TERMINATED BY ','
         (id,stime DATE 'yyyy-mm-dd',p1,p2,p3,p4)
       ) 
      REJECT LIMIT UNLIMITED
     ) 

 partition by range(stime)( 
  PARTITION stime_ep1 VALUES LESS THAN (TO_DATE('01-01-2015','dd-mm-yyyy')) ,
  PARTITION stime_ep2 VALUES LESS THAN (TO_DATE('01-01-2016','dd-mm-yyyy')),
  PARTITION stime_ep3 VALUES LESS THAN (TO_DATE('01-01-2017','dd-mm-yyyy')),
  PARTITION stime_ep4 VALUES LESS THAN (TO_DATE('01-01-2018','dd-mm-yyyy')),
  PARTITION stime_ep5 VALUES LESS THAN (TO_DATE('01-01-2019','dd-mm-yyyy')),
  PARTITION stime_ep6 VALUES LESS THAN (TO_DATE('01-01-2020','dd-mm-yyyy')),
  PARTITION stime_ep7 VALUES LESS THAN (TO_DATE('01-01-2021','dd-mm-yyyy'))  external   LOCATION ('test.csv')

   ); 
复制

把  stime_ep7 分区设置为外部分局表

• 查询复合分区表 E_P_TPA_RADIO_SUM

• 通过分区交换实现数据迁移

    alter table P_TPA_RADIO_SUM exchange partition  stime_p1  with table TPA_RADIO_SUM;
   alter table E_P_TPA_RADIO_SUM exchange partition  stime_ep1  with table TPA_RADIO_SUM;


复制

应用场景总结

既然可以创建csv文件作为分片的混合分区表，就可以将历史数据导出为csv文件存储在外部存储上，通过分区交换的方式进行快速的迁移。这样的好处为：历史数据访问次数相对较少，外部存储即可保证了历史数据的可访问，但又减少了数据库实际存储的大小，有利于数据的维护管理和并缩短备份恢复时长。

那csv文件怎么生成呢，不妨看看这篇文章

轻量级ORACLE数据库工具