Oracle数仓分区表创建及其数据清理存储过程

关注

原创最新推荐文章于 2026-04-12 08:00:00 发布 924 阅读 AI 写同款· GEO 优化 ›

Oracle-大表改造分区表实施步骤对于业务交易繁忙的数据库，在运行了一定时间后往往会产生一些数据量较大的表，特别是对于每天新增数据较多的日志表或者流水表，单表数据量通常会达到几十G，甚至是上百G。大表对于日常的运维非常的不方便，特别是在表数据的清理、迁移，查询性能会随着数据量的增大而受到影响，因此，通常对于大表我们需要进行优化拆分，在Oracle数据库，比较常见的大表优化拆分是将大表改造为分区表，这种方式最主要的好处就是对于应用透明，应用层面的改动很少阅读详情

Oracle数仓中，由于客户要求不能有delete语句，delete语句会对每一条数据记录一条日志，造成很慢，日志太大的问题。同时由于传统数仓容量限制，需要对历史数据进行一个归档，归档之后需要对历史数据进行清理，因此Ods采用了分区表设计。ODS采用按天列表分区，重跑时需要清理已存在的分区数据，非重跑时需要增加按天分区。清理的Oracle存储过程如下：

-- 清理分区数据或创建分区
-- v_tabname 表名
-- v_tabpartition 分区名字
-- v_bizdate 分区值
CREATE OR REPLACE PROCEDURE ODS_DATA_PREPARESQL(
  v_tabname in varchar,
  v_tabpartition in varchar,
  v_bizdate in varchar
) AS
 cnt NUMBER;
 strSql varchar(2048);
BEGIN
  select count(1) into cnt from USER_TAB_PARTITIONS where TABLE_NAME = upper(v_tabname) and PARTITION_NAME = v_tabpartition;
  IF cnt > 0 then
    strSql := 'alter table ' || v_tabname || ' TRUNCATE PARTITION ' || v_tabpartition || ' update indexes';
    execute immediate strSql;
  ELSE 
    strSql := 'alter table '|| v_tabname || ' ADD PARTITION ' || v_tabpartition || ' values ('|| v_bizdate || ')';
    execute immediate strSql;
  END IF;
END;

创建数仓ODS层分区表的模板如下：

create table test_partition (
  ID int,
  name varchar2(64),
  age int ,
  data_dt varchar2(32)
)
partition by list(data_dt) (
  partition PART_19700101 VALUES('19700101')
)

收录于