前言：近日，公司的一套使用 postgresql 数据库的应用软件要兼容oracle。本文系统性地整理了PostgreSQL 和 Oracle的一些差异点，和应用程序中的改动点。
3 SQL脚本的改造
应用程序的每个子系统有自己的数据库初始化脚本。下面我们介绍初始化脚本中涉及到的PostgreSQL与Oracle的差异点及改造点。
3.1 数据类型
下面是PostgreSQL 中的各种数据类型与对应的Oracle数据类型。
|
|
PostgreSQL 11 |
Oracle 19c |
备注 |
|
短整型 |
smallint / int2 |
number(5) |
|
|
整型 |
integer / int / int4 |
number(10) |
|
|
长整型 |
bigint / int8 |
number(20) |
|
|
字符串(4000字节以下) |
varchar(n) |
varchar2(n char) |
pg的varchar(n)表示n个字符；Oracle的varchar(n)表示 n 字节；varchar(n char) 表示 n 个字符;
pg中varchar最多可存放1GB内容，Oracle中varchar最多存放4000字节的内容 |
|
长字符串(4000字节及其以上) |
varchar / text / varchar(2000) 及其以上长度 |
clob |
Oracle 中，4000字节以内的字符串，用 varchar(4000)；4000字节以上的，用clob |
|
数组类型 |
有原生的数组，可作为字段类型和变量。例如:
create table tb_test ( attributes varchar(32)[], ); |
用varchar或clob类型表示，格式为json数组。例如: create table tb_test ( attributes varchar(512), CHECK (attribute IS JSON) ); |
|
|
json |
有原生类型json和jsonb。例如：
create table tb_test ( json_column jsonb, ); |
用varchar或clob表示。例如
create table tb_test ( json_column clob, CHECK (attribute IS JSON) ); |
|
|
自增序列类型 |
create sequence seq_tb_test_id minvalue 1 maxvalue 2147483647;
create table tb_test ( id int default nextval('seq_tb_test_id') ); (推荐)
或者 create table tb_test ( id serial ); （不推荐） |
create sequence seq_tb_test_id minvalue 1 maxvalue 2147483647;
create table tb_test ( id int default seq_tb_test_id.nextval ); |
postgresql声明一个字段类型是serial，实质是创建一个序列（sequence），并将其绑定到这个字段上。 |
|
bool类型 |
boolean |
不支持bool类型，可以用 number(5)类型代替。1表示true，0表示false |
|
|
几何类型 |
geometry |
st_geometry |
|
|
ip地址类型 |
inet |
不支持inet，用字符串替代 |
|
3.2 常用 sql 语法
下面是数据库脚本中一些需要改造的语法：
|
|
PostgreSQL 11 |
Oracle |
备注 |
|
创建扩展插件 |
create extension pgcrypto; |
不支持 |
将postgresql的 sql脚本改造为oracle版本时，删除这些语句 |
|
创建/删除数据库对象之前先判断 |
if exists/ if not exists |
不支持if exists/ if not exists。 |
将sql脚本改造为基于oracle版本时，删除if exists/ if not exists。 |
|
批量插入 |
insert into tb01 (id) values(1),(2),(3); |
insert into tb01 (id) values(1); insert into tb01 (id) values(2); insert into tb01 (id) values(3); |
Oracle中，需要逐行插入数据 |
|
设置字段默认值 |
alter table tb_region alter column region_id set default gen_random_uuid(); |
alter table tb_region modify region_id default sys_guid(); |
|
|
NULL和'' |
NULL和''不同 |
ORACLE认为''等同于NULL |
NULL和'' |
|
字段定义时 not null 和default 的顺序 |
create table test ( ……, status default 0 not null ); （推荐） 或 create table test ( ……, status not null default 0 ); |
create table test ( ……, status default 0 not null ); |
|
3.3 常用 sql 函数
下面是PostgreSQL和Oracle中，对应的常用的数据库函数和运算符：
|
|
PostgreSQL 11 |
Oracle 19c |
备注 |
|
类型转换 |
value :: type 或 CAST(value AS type) |
CAST(value AS type) |
|
|
序列取值 |
nextval('sequence_name'） |
sequence_name.nextval |
注意：在pg中，nextval('sequence_name') 在同一行中每调用一次，数值会增加1； 在oracle中，sequence_name.nextval 在同一行中多次调用，值是相同的。
例如： -- postgresql # select nextval('sequence_name'),nextval('sequence_name'); result: 1,2
# select sequence_name.nextval,sequence_name.nextval; result : 1,1 |
|
获取uuid |
gen_random_uuid() 或者uuid_generate_v1mc() |
sys_guid() |
|
|
获取当前时间 |
获取事务开始时间戳： select now(); select current_timestamp;
获取当前命令执行的时间戳： select clock_timestamp(); |
获取事务开始时间戳： 无
获取当前命令执行的时间戳： select current_timestamp from dual; |
注意，两个数据库中 current_timestamp 的含义的差别 |
3.4 索引
下面是数据库由PostgreSQL 迁移到Oracle时，索引的改造点：
|
|
PostgreSQL 11 |
Oracle 19c |
备注 |
|
创建btree索引 |
create index idx_tb_event_event_id on tb_event [using btree] (event_id); |
create index idx_tb_event_event_id on tb_event (event_id); |
btree 是默认的索引类型。不建议明确指出索引类型是 btree。 |
|
PostgreSQL的 pg_trgm |
PostgreSQL的扩展，用于模糊匹配 |
不支持，改造时直接删除这个索引 |
|
|
PostgreSQL的 gin索引 |
PostgreSQL的索引类型 |
不支持，可根据索引具体用途，判断是删除这个索引还是改造它。 |
|
|
PostgreSQL的 gin_trgm_ops |
PostgreSQL的索引访问方法，用于模糊匹配
create index indx_tb_card_card_no_like on tb_card using gin (card_no gin_trgm_ops); |
不支持，改造时直接删除这个索引。 |
|
|
PostgreSQL的 text_pattern_ops |
PostgreSQL的索引访问方法，用于后模糊匹配 'abc%'
create index idx_tb_region_region_path on tb_region (region_path text_pattern_ops); |
Oracle 不需要此方法，默认的btree即支持后模糊匹配 'abc%'：
create index idx_tb_region_region_path on tb_region (region_path); |
|
|
PostgreSQL的 brin索引 |
PostgreSQL的索引类型 |
不支持 |
在Oracle中用 默认btree索引 替代 |
|
PostgreSQL的 bloom 索引 |
PostgreSQL的索引类型 |
不支持 |
在Oracle中用 默认btree索引替代 |
|
为几何类型 geometry的字段创建索引 |
create index idx_tb_map_point_the_geom on tb_map_point using gist (the_geom); |
CREATE INDEX idx_tb_map_point_the_geom ON tb_map_point (the_geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX; |
|
|
为JSON 字段（存放JSON对象）的某些键创建索引 |
create table tb_test ( id int, json_column jsonb, );
create index idx_tb_test_json_column on tb_test ((json_column ->> 'parentId')); |
create table tb_test ( id int, json_column clob, CHECK (json_column IS JSON) );
create index idx_tb_test_json_column on tb_test (json_value(json_column, '$.parentId' RETURNING varchar(64))); |
|
|
为数组字段创建索引 |
1. 字段类型为array create table tb_test ( id int, codes integer [], );
create index idx_tb_test_codes on tb_test using gin (codes);
2. 字段类型为jsonb create table tb_test ( id int, codes jsonb );
create index idx_tb_test_codes on tb_test using gin (codes); |
不支持创建包含数组中的所有元素的索引 |
|
3.5 序列
下面是SQL脚本中与的序列相关的改造点：
|
|
PostgreSQL 11 |
Oracle 19c |
备注 |
|
获取序列当前值 |
currval('seqence_name'); |
seqence_name.currval |
|
|
获取序列下一个值 |
nextval('seqence_name'); |
seqence_name.nextval |
注意：在pg中，nextval('sequence_name') 在同一行中每调用一次，数值会增加1； 在oracle中，sequence_name.nextval 在同一行中多次调用，值是相同的。
例如： -- postgresql # select nextval('sequence_name'),nextval('sequence_name'); result: 1,2
# select sequence_name.nextval,sequence_name.nextval; result : 1,1 |
|
修改序列的值 |
setval('seqence_name') |
不支持 |
|
|
设置序列不循环 |
create sequence seqence_name NO CYCLE; |
create sequence seqence_name NOCYCLE; |
|
|
创建序列的语法差异 |
CREATE SEQUENCE seq_1 INCREMENT [ BY ] 1 MINVALUE 0 MAXVALUE 10000 START [ WITH ] 1 CACHE [ 1 | 2 | 3 | ...] |
CREATE SEQUENCE seq_1 INCREMENT BY 1 MINVALUE 0 MAXVALUE 10000 START WITH 1 CACHE [ 2 | 3 | ...] |
postgresql创建序列语句中，“BY”，“with” 是可选的。 PostgreSQL 序列缓存值可以等于1，而 Oracle 的序列缓存值必须大于1。 |
3.6 自定义函数、存储过程和触发器
PostgreSQL 的函数内部支持DDL（create, drop, alter）和DML（insert.delete,update），而Oracle 的函数只支持DML，存储过程则支持DDL和DML。因此，对于我们在PostgreSQL 中定义的函数，那些内部没有DDL 语句，且运行时会不进行DDL操作的，应该被改造为Oracle的函数；而那些内部有DDL 语句（create, drop, alter），或者运行时会进行DDL操作的，应该被改造为Oracle存储过程；
PostgreSQL 允许函数/存储过程重载，即有多个同名函数；而Oracle不允许。
下面是这两种数据库中，自定义函数，存储过程和触发器相关的语法差异：
|
|
PostgreSQL 11 |
Oracle 19c |
备注 |
|
|
|
函数 |
函数 |
存储过程 |
|
|
是否支持在内部执行 DDL 语句 |
是 |
否 |
是 |
|
|
内部执行 DDL 语句的方法 |
1. 直接在函数内部执行DDL语句 begin create table test(id int); end;
2. 执行sql字符串; begin execute 'create table test(id int)'; end; |
不支持 |
执行sql 字符串 begin execute immediate 'create table test(id number(10))'; end; |
|
|
存储过程中，执行sql字符串 |
begin execute ' insert into test(id int) values (1);'; end; |
begin execute immediate ' insert into test(id int) values (1)'; end; |
begin execute immediate ' insert into test(id int) values (1)'; end; |
注意：在postgresql中，execute 执行的sql字符串内可以分号结尾；而在oracle中，execute immediate 执行的sql 字符串不允许以分号结尾 |
|
函数中赋值符号 |
:= 或者 = |
:= |
:= |
在变量赋值时,需要注意 |
|
字符串作为函数参数 |
varchar或 varchar(n)
例如： func(a varchar(32), b varchar) |
varchar，不带长度
例如： func(a varchar, b varchar) |
varchar，不能长度
例如： proc(a varchar, b varchar) |
|
|
创建并调用有参函数/存储过程 |
create or replace function func(a int, a2 varchar(32)) returns int as $$ declare b int; [declare] c varchar(32); begin PL/SQL statements... return 0; end; $$ language plpgsql; （推荐）
select func(1); |
create or replace function func(a number, a2 varchar) return int {as | is} b int; c varchar(32); begin PL/SQL statements... return 0; end;
select func(1) from dual; |
create or replace procedure proc(a number, a2 varchar) {as | is} b int; c varchar(32); begin PL/SQL statements... end [proc];
call proc(1); |
|
|
创建和调用无参函数/存储过程 |
create or replace function func() returns int as $$ declare b int; [declare] c varchar(32); begin PL/SQL statements... return 0; end; $$ language plpgsql;
select func(); |
create or replace function func return int {as | is} b int; c varchar(32); begin PL/SQL statements... return 0; end;
select func() from dual; |
create or replace procedure proc {as | is} b int; c varchar(32); begin PL/SQL statements... end [proc];
call proc(); |
Oracle数据库的无参函数在定义时函数名后面没有括号，在调用时，函数名后面有括号。 |
|
创建和调用返回集合的函数（示例） |
create type tp_id_name as (id int,name varchar(128))
create or replace function func_get_id_name() returns setof tp_id_name as $$ begin return query execute 'select id, name from man'; end; $$ language plpgsql;
select * from func_get_id_name(); |
create type tp_id_name as object(id int,name varchar(128)); create type table_tp_id_name as table of tp_id_name;
CREATE OR replace function funcunc_get_id_name RETURN table_tp_id_name as v_table_tp_id_name table_tp_id_name; sql_text VARCHAR(1000); BEGIN sql_text:='select tp_id_name(id,name) from man'; execute immediate sql_text bulk collect into v_table_tp_id_name; return v_table_tp_id_name; end func_get_id_name;
select * from table(func_get_id_name()); |
|
需要定义一个类型，表示集合中的元组 |
|
删除函数/存储过程 |
drop function f(a int); |
drop function f; |
drop procedure f; |
|
|
触发器 |
create or replace function trigger_function() returns trigger as $$ declare ...; begin PL/SQL statements... end; $$ language plpgsql;
create [or replace] tigger trigger_name {before| after | instead of } event on table_name [for each row] execute trigger_function() ; |
create [or replace] tigger trigger_name {before| after | instead of } event on table_name [for each row] begin PL/SQL statements... end; |
在postgresql中，触发器执行的语句需要被定义为一个函数。而在oracle中，触发器执行的语句位于触发器的定义内部。 |
|
3.7 分区表
3.7.1 分区字段是以毫秒为单位的utc时间
这里以事件表 tb_event 为例，我们是这样设计它的的分区方案的：
1. 这个表是根据 event_time_utc （事件发生UTC时间，以毫秒为单位），以月为间隔来分区的。从主表创建之日起，每个月为这张表创建一张分区，用来存放对应月份的数据。
2. 分区表有默认的分区，存放的是有独立月分区的数据以外的数据。在PostgreSQL 11中，我们创建一个默认分区tb_event_default，在Oracle中，创建默认分区 tb_event_default_future，存放有独立分区的最新的那个月以后的数据。
3. 在程序启动和每个月即将结束时，创建存放这个月和下一（几）个月的数据的分区表（如果他们不存在），并将默认分区中属于这些月份的数据迁移至对应的分区表中，最后修改默认分区的范围。
我们单独讲解与分区表相关的一些sql语句的改造：
- 创建分区表
下面是创建分区表的语法差异。可以看出，在Oracle中创建分区表时，必须至少创建一个分区。而在PostgreSQL中创建分区表时，则不能创建分区。
|
PostgreSQL 11 |
Oracle 19c |
|
create table tb_event ( event_id varchar(36) not null, event_type varchar(32), event_time timestamp, event_time_difference varchar(32) not null, event_time_utc bigint not null ) partition by range (event_time_utc); |
create table tb_event ( event_id varchar(36) not null, event_type varchar(32), event_time timestamp, event_time_difference varchar(32) not null, event_time_utc number(20) not null, constraint pk_tb_event primary key (event_id) ) partition by range (event_time_utc) ( partition tb_event_default_future values less than (maxvalue) ); |
- 创建主键，唯一键.
PostgreSQL要求全局主键、唯一键必须包含分区字段。如果你执行这样的sql语句：
create table tb_event
(
event_id varchar(36) not null,
event_type varchar(32),
event_time timestamp,
event_time_difference varchar(32) not null,
event_time_utc bigint not null,
constraint pk_tb_event primary key (event_id)
) partition by range (event_time_utc);
则会报错：
ERROR: insufficient columns in PRIMARY KEY constraint definition
DETAIL: PRIMARY KEY constraint on table "tb_event" lacks column "event_time_utc" which is part of the partition key.
对于PostgreSQL，你可以为每个分区创建局部主键或局部唯一键，局部主键列可以不包含分区字段。例如：
alter table tb_event_201909 add constraint pk_tb_event_201909 primary key (event_id) ;
而Oracle则支持不包含分区字段的全局主键：
create table tb_event
(
event_id varchar(36) not null,
event_type varchar(32),
event_time timestamp,
event_time_difference varchar(32) not null,
event_time_utc number(20) not null,
constraint pk_tb_event primary key (event_id)
) partition by range (event_time_utc)
(
partition tb_event_default_future values less than (maxvalue)
);
- 为下一个月的数据创建分区，并调整默认分区的范围
这里以2019年9月为例，下面是在PostgreSQL和Oracle中的方法。
|
PostgreSQL 11 |
Oracle 19c |
|
alter table tb_event detach PARTITION tb_event_default;
create table tb_event_201909 partition of tb_event for values from (1567267200000) to (1569859200000);
insert into tb_event select * from tb_event_default where event_time_utc >= 1567267200000 and event_time_utc <1569859200000;
delete from tb_event_default where event_time_utc >= 1567267200000 and event_time_utc < 1569859200000;
alter table tb_event attach partition tb_event_default default; |
方法是拆分分区后重建索引
ALTER TABLE tb_event SPLIT PARTITION tb_event_default_future AT (1569859200000) INTO (PARTITION tb_event_201909, PARTITION tb_event_default_future);
begin for c1 in (select t.index_name, t.partitioned from user_indexes t where table_name = upper('tb_event')) loop if c1.partitioned='NO' then -- rebuild global index directly execute immediate 'alter index ' || c1.index_name || ' rebuild'; else -- rebuild every unusable partition for partitioned index for c2 in (select partition_name from user_ind_partitions where index_name=c1.index_name and status = 'UNUSABLE') loop execute immediate 'alter index ' || c1.index_name || ' rebuild partition ' || c2.partition_name; end loop; end if; end loop; end; |
- 删除2019年8月的分区，并调整默认分区的范围
|
PostgreSQL 11 |
Oracle 19c |
|
drop table drop table tb_event_201908;
--默认分区范围会自动调整 |
删除分区后重建全局索引
alter table tb_event drop partition tb_event_201908;
begin for c1 in (select t.index_name, t.partitioned from user_indexes t where table_name = upper('tb_event')) loop if c1.partitioned='NO' then -- rebuild global index directly execute immediate 'alter index ' || c1.index_name || ' rebuild'; else -- rebuild every unusable partition for partitioned index for c2 in (select partition_name from user_ind_partitions where index_name=c1.index_name and status = 'UNUSABLE') loop execute immediate 'alter index ' || c1.index_name || ' rebuild partition ' || c2.partition_name; end loop; end if; end loop; end;
|
- 分区表上创建索引
|
PostgreSQL 11 |
Oracle 19c |
|
create index idx_tb_event_event_type on tb_event(event_type); |
第一种： create index idx_tb_event_event_type on tb_event (event_type) local; （局部索引，容易维护，推荐） 第二种： create index idx_tb_event_event_type on tb_event (event_type) global; (全局索引，不移维护，不推荐) |
- 创建和删除分区表的存储过程编写。
针对tb_event，我们编写创建分区的存储过程如下：
存储过程的三个参数的含义如下：
p_partition_unit：分区的单位。取值范围是month，day和quarter
p_partiton_cnt: 希望创建的分区的数量
p_start_utc_time: 第一个新建分区的对应时间范围内的任意时刻，以毫秒为单位。
|
/* -- procedure: Create partitions for table tb_event
-- parameters: -- p_partition_unit: Partition unit. Values: month, day, quarter -- p_partiton_cnt: Count of partitions to be created when this procedure is called. The default value is 6. -- p_start_utc_time: One UTC timestamp value that is in the range of the first partition's partition-key, in milliseconds. The default value is now.
*/
create or replace procedure proc_create_partition_for_tb_event(p_partition_unit varchar default 'month', p_partiton_cnt number default 6, p_start_utc_time number default (CAST(SYS_EXTRACT_UTC(current_timestamp) AS date) - to_date('1970-01-01', 'YYYY-MM-DD'))*86400*1000) as v_start_time timestamp; -- 要新建的第一个分区的开始时间 v_partitioned_tbname varchar(64); v_partition_name varchar(64); v_partition_to_split varchar(64); v_time_format varchar(64); v_start_time_of_this timestamp; v_end_time_of_this timestamp; v_high_value_of_this number(20); --当前新建分区的分区字段的上限值（不含） v_high_value_text varchar(64); begin v_partitioned_tbname := 'tb_event';
v_time_format := (case p_partition_unit when 'month' then 'YYYYMM' when 'day' then 'YYYYMMDD' when 'quarter' then 'YYYY"q"Q' end);
-- 伦敦时间的字面值再加上 时区偏移量 SELECT trunc(to_timestamp('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + (p_start_utc_time/1000/86400) + TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))/24, 'month') INTO v_start_time FROM dual;
if p_partition_unit = 'month' then v_start_time_of_this := v_start_time; v_end_time_of_this := v_start_time_of_this + interval '1' month; elsif p_partition_unit = 'day' then v_start_time_of_this := v_start_time; v_end_time_of_this := v_start_time_of_this + interval '1' day; elsif p_partition_unit = 'quarter' then v_start_time_of_this := v_start_time; v_end_time_of_this := v_start_time_of_this + interval '3' month; end if;
--当前新建分区的分区字段的上限值（不含） v_high_value_of_this := (CAST(SYS_EXTRACT_UTC(v_end_time_of_this) as date) - to_date('1970-01-01', 'YYYY-MM-DD'))*86400*1000; v_partition_name := v_partitioned_tbname || '_' || to_char(v_start_time_of_this, v_time_format);
if p_partition_unit in ('month', 'day', 'quarter') then for i in 0..(p_partiton_cnt - 1) loop for c in (select table_name, HIGH_VALUE, partition_name from USER_TAB_PARTITIONS where table_name = upper(v_partitioned_tbname) order by partition_position) loop v_high_value_text := c.HIGH_VALUE; -- HIGH_VALUE is of type 'Long', and can only be converted into varchar in this way
-- if this partition to be create already exists if v_high_value_text = cast(v_high_value_of_this as varchar) then exit; -- if this partition does not exist and its partition-key's range is included by another partition, we need to split that parition elsif (v_high_value_text = 'MAXVALUE' or cast(v_high_value_text as number) > v_high_value_of_this) then v_partition_to_split := c.partition_name; execute immediate 'alter table ' || v_partitioned_tbname || ' split PARTITION ' || v_partition_to_split || ' AT (' || v_high_value_of_this || ') INTO (PARTITION ' || v_partition_name || ', PARTITION ' || v_partition_to_split || ')'; exit; end if; end loop;
if p_partition_unit = 'month' then v_start_time_of_this := v_start_time_of_this + interval '1' month; v_end_time_of_this := v_end_time_of_this + interval '1' month; elsif p_partition_unit = 'day' then v_start_time_of_this := v_start_time_of_this + interval '1' day; v_end_time_of_this := v_end_time_of_this + interval '1' day; elsif p_partition_unit = 'quarter' then v_start_time_of_this := v_start_time_of_this + interval '3' month; v_end_time_of_this := v_end_time_of_this + interval '3' month; end if;
v_high_value_of_this := (CAST(SYS_EXTRACT_UTC(v_end_time_of_this) as date) - to_date('1970-01-01', 'YYYY-MM-DD'))*86400*1000; v_partition_name := v_partitioned_tbname || '_' || to_char(v_start_time_of_this, v_time_format); end loop; end if;
for c1 in (select t.index_name, t.partitioned from user_indexes t where table_name = upper(v_partitioned_tbname)) loop if c1.partitioned = 'NO' then -- rebuild global index directly execute immediate 'alter index ' || c1.index_name || ' rebuild'; else -- rebuild every unusable partition for partitioned index for c2 in (select partition_name from user_ind_partitions where index_name=c1.index_name and status = 'UNUSABLE') loop execute immediate 'alter index ' || c1.index_name || ' rebuild partition ' || c2.partition_name; end loop; end if; end loop; end; |
调用时，执行命令：
call proc_create_partition_for_tb_crosshistory('month'，6);
即可为从本月开始的6个月创建分区。
为tb_event删除分区的存储过程如下：
存储过程的两个函数含义如下：
p_partition_unit：分区的单位。取值范围是month，day和quarter。
p_keep_days: 数据保留的天数。超过这么多天以前的数据会被删除；
|
create or replace procedure proc_clean_partition_for_tb_event(p_partition_unit varchar default 'month', p_keep_days number default 90) as v_partitioned_tbname varchar(64); v_sql varchar(64); v_clean_time timestamp; v_clean_utctime number; v_partiton_colname varchar(64); v_time_format varchar(64); v_high_value_text varchar(64); begin
v_sql := 'select current_timestamp - interval ''' || p_keep_days || ''' day from dual'; execute IMMEDIATE v_sql into v_clean_time; v_clean_utctime := (CAST(SYS_EXTRACT_UTC(v_clean_time) AS date) - to_date('1970-01-01', 'YYYY-MM-DD'))*86400*1000;
v_partitioned_tbname := 'tb_event'; v_partiton_colname := 'event_time_utc';
v_time_format := (case p_partition_unit when 'month' then 'YYYYMM' when 'day' then 'YYYYMMDD' when 'quarter' then 'YYYY"q"Q' end);
if p_partition_unit in ('month', 'day', 'quarter') then for c in (select table_name, HIGH_VALUE, partition_name from USER_TAB_PARTITIONS where table_name = upper(v_partitioned_tbname) order by partition_position) loop v_high_value_text := c.HIGH_VALUE; -- HIGH_VALUE is of type 'Long', and can only be converted into varchar in this way if v_high_value_text <> 'MAXVALUE' AND CAST(v_high_value_text AS number) <= v_clean_utctime then execute immediate 'alter table ' || v_partitioned_tbname || ' drop partition ' || c.partition_name; end if; end loop; end if; execute immediate 'delete from ' || v_partitioned_tbname || ' where ' || v_partiton_colname || ' < ' || v_clean_utctime;
for c1 in (select t.index_name, t.partitioned from user_indexes t where table_name = upper(v_partitioned_tbname)) loop if c1.partitioned = 'NO' then -- rebuild global index directly execute immediate 'alter index ' || c1.index_name || ' rebuild'; else -- rebuild every unusable partition for partitioned index for c2 in (select partition_name from user_ind_partitions where index_name=c1.index_name and status = 'UNUSABLE') loop execute immediate 'alter index ' || c1.index_name || ' rebuild partition ' || c2.partition_name; end loop; end if; end loop; end; |
调用时，执行命令：
call proc_clean_partition_for_tb_event ('month'，90);
即可删除90天以前的数据。
3.7.2 分区字段是 timestamp 类型的
我们以tb_record为例。
1. 这个表是根据 event_time，以月为间隔来分区的。从主表创建之日起，每个月为这张表创建一张分区，用来存放对应月份的数据。
2. 分区表有默认的分区，存放的是有独立月分区的数据以外的数据。在PostgreSQL 11中，我们创建一个默认分区tb_record_default，在Oracle中，创建默认分区 tb_record_default_future，存放有独立分区的最新的那个月以后的数据。
3. 在程序启动和每个月即将结束时，创建存放这个月和下一（几）个月的数据的分区表（如果他们不存在），并将默认分区中属于这些月份的数据迁移至对应的分区表中，最后修改默认分区的范围。
针对tb_record，我们编写创建分区的存储过程如下：
- 创建分区的存储过程
存储过程的三个参数的含义如下：
p_partition_unit：分区的单位。 取值范围是month，day和quarter。
p_partiton_cnt: 希望创建的分区的数量
p_start_time: 第一个新建分区的对应时间范围内的任意时刻。
|
/* -- procedure: Create partitions for table tb_record
-- parameters: -- p_partition_method: Partition unit. Values: month, day, quarter -- p_partiton_cnt: Count of partitions to be created when this funciton is called. The default value is 7. -- p_start_time: One timestamp value that is in the range of the first partition's partition-key. The default value is now.
*/
create or replace procedure proc_create_partition_for_tb_record(p_partition_unit varchar default 'month', p_partiton_cnt number default 6, p_start_time timestamp with time zone default current_timestamp) as v_start_time timestamp; -- 要新建的第一个分区的开始时间 v_partitioned_tbname varchar(64); v_partition_name varchar(64); v_partition_to_split varchar(64); v_time_format varchar(64); v_start_time_of_this timestamp; v_end_time_of_this timestamp; v_high_value_of_this timestamp; --当前新建分区的分区字段的上限值（不含） v_high_value_text varchar(64); begin v_partitioned_tbname := 'tb_record';
v_time_format := (case p_partition_unit when 'month' then 'YYYYMM' when 'day' then 'YYYYMMDD' when 'quarter' then 'YYYY"q"Q' end);
SELECT trunc(p_start_time, 'month') INTO v_start_time FROM dual;
if p_partition_unit = 'month' then v_start_time_of_this := v_start_time; v_end_time_of_this := v_start_time_of_this + interval '1' month; elsif p_partition_unit = 'day' then v_start_time_of_this := v_start_time; v_end_time_of_this := v_start_time_of_this + interval '1' day; elsif p_partition_unit = 'quarter' then v_start_time_of_this := v_start_time; v_end_time_of_this := v_start_time_of_this + interval '3' month; end if;
--当前新建分区的分区字段的上限值（不含） v_high_value_of_this := v_end_time_of_this; v_partition_name := v_partitioned_tbname || '_' || to_char(v_start_time_of_this, v_time_format);
if p_partition_unit in ('month', 'day', 'quarter') then for i in 0..(p_partiton_cnt - 1) loop for c in (select table_name, HIGH_VALUE, partition_name from USER_TAB_PARTITIONS where table_name = upper(v_partitioned_tbname) order by partition_position) loop v_high_value_text := REPLACE(replace(c.HIGH_VALUE, 'TIMESTAMP'' ', ''), ''''); -- HIGH_VALUE is of type 'Long', and can only be converted into varchar in this way
-- if this partition to be create already exists if v_high_value_text = to_char(v_high_value_of_this, 'YYYY-MM-DD HH24:MI:SS') THEN exit; -- if this partition does not exist and its partition-key's range is included by another partition, we need to split that parition elsif (v_high_value_text = 'MAXVALUE' or v_high_value_text > to_char(v_high_value_of_this, 'YYYY-MM-DD HH24:MI:SS')) then v_partition_to_split := c.partition_name; execute immediate 'alter table ' || v_partitioned_tbname || ' split PARTITION ' || v_partition_to_split || ' AT ( to_timestamp(''' || to_char(v_high_value_of_this, 'YYYY-MM-DD HH24:MI:SS') || ''', ''YYYY-MM-DD HH24:MI:SS'')) INTO (PARTITION ' || v_partition_name || ', PARTITION ' || v_partition_to_split || ')'; exit; end if; end loop;
if p_partition_unit = 'month' then v_start_time_of_this := v_start_time_of_this + interval '1' month; v_end_time_of_this := v_end_time_of_this + interval '1' month; elsif p_partition_unit = 'day' then v_start_time_of_this := v_start_time_of_this + interval '1' day; v_end_time_of_this := v_end_time_of_this + interval '1' day; elsif p_partition_unit = 'quarter' then v_start_time_of_this := v_start_time_of_this + interval '3' month; v_end_time_of_this := v_end_time_of_this + interval '3' month; end if; v_high_value_of_this := v_end_time_of_this; v_partition_name := v_partitioned_tbname || '_' || to_char(v_start_time_of_this, v_time_format); end loop; end if;
for c1 in (select t.index_name, t.partitioned from user_indexes t where table_name = upper(v_partitioned_tbname)) loop if c1.partitioned = 'NO' then -- rebuild global index directly execute immediate 'alter index ' || c1.index_name || ' rebuild'; else -- rebuild every unusable partition for partitioned index for c2 in (select partition_name from user_ind_partitions where index_name=c1.index_name and status = 'UNUSABLE') loop execute immediate 'alter index ' || c1.index_name || ' rebuild partition ' || c2.partition_name; end loop; end if; end loop; end;
|
调用时，执行命令：
call proc_create_partition_for_tb_record('month');
即可为从本月开始的6个月创建分区。
- 删除分区的存储过程
为tb_record删除分区的存储过程如下：
存储过程的两个函数含义如下：
p_partition_unit：分区的单位。取值范围是month，day和quarter
p_keep_days: 数据保留的天数。超过这么多天以前的数据会被删除；
|
/* -- procedure: Clean old data for tb_record
-- parameters: -- p_partition_unit: Partition unit. Values: month, day, quarter -- p_keep_days: Duration of data retention, in days. The default value is 90.
*/
create or replace procedure proc_clean_partition_for_tb_record(p_partition_unit varchar default 'month', p_keep_days number default 90) as v_partitioned_tbname varchar(64); v_sql varchar(64); v_clean_time timestamp; v_partiton_colname varchar(64); v_time_format varchar(64); v_high_value_text varchar(64); begin
v_sql := 'select current_timestamp - interval ''' || p_keep_days || ''' day from dual'; execute immediate v_sql into v_clean_time;
v_partitioned_tbname := 'tb_record'; v_partiton_colname := 'event_time';
v_time_format := (case p_partition_unit when 'month' then 'YYYYMM' when 'day' then 'YYYYMMDD' when 'quarter' then 'YYYY"q"Q' end);
if p_partition_unit in ('month', 'day', 'quarter') then for c in (select table_name, HIGH_VALUE, partition_name from USER_TAB_PARTITIONS where table_name = upper(v_partitioned_tbname) order by partition_position) loop v_high_value_text := replace(replace(c.HIGH_VALUE, 'TIMESTAMP'' ', ''), ''''); -- HIGH_VALUE is of type 'Long', and can only be converted into varchar in this way if v_high_value_text <> 'MAXVALUE' AND v_high_value_text <= to_char(v_clean_time, 'YYYY-MM-DD HH24:MI:SS') then execute immediate 'alter table ' || v_partitioned_tbname || ' drop partition ' || c.partition_name; end if; end loop; end if; execute immediate 'delete from ' || v_partitioned_tbname || ' where ' || v_partiton_colname || ' < to_timestamp(''' || to_char(v_clean_time, 'YYYY-MM-DD HH24:MI:SS') || ''', ''YYYY-MM-DD HH24:MI:SS'')';
for c1 in (select t.index_name, t.partitioned from user_indexes t where table_name = upper(v_partitioned_tbname)) loop if c1.partitioned = 'NO' then -- rebuild global index directly execute immediate 'alter index ' || c1.index_name || ' rebuild'; else -- rebuild every unusable partition for partitioned index for c2 in (select partition_name from user_ind_partitions where index_name=c1.index_name and status = 'UNUSABLE') loop execute immediate 'alter index ' || c1.index_name || ' rebuild partition ' || c2.partition_name; end loop; end if; end loop; end; |
调用时，执行命令：
call proc_clean_partition_for_tb_record('month'，90);
即可删除90天以前的数据。