PPAS(enterprisedb)(PG Oracle兼容版) 10以及以前版本分区表的使用，分区表索引的创建

digoal 2019-01-23

742

作者

digoal

日期

2019-01-23

背景

PPAS 10以及以前的版本，对于Oracle分区表的使用，以及如何创建分区表的索引。

10 以及以前的版本，仅支持range, list分区。11开始支持HASH分区。

Oracle分区表语法

https://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin001.htm#i1006455

例子

CREATE TABLE sales ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY RANGE (time_id) ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy')) , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy')) , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy')) , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy')) );

CREATE TABLE q1_sales_by_region (deptno number, deptname varchar2(20), quarterly_sales number(10, 2), state varchar2(2)) PARTITION BY LIST (state) (PARTITION q1_northwest VALUES ('OR', 'WA'), PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'), PARTITION q1_northeast VALUES ('NY', 'VM', 'NJ'), PARTITION q1_southeast VALUES ('FL', 'GA'), PARTITION q1_northcentral VALUES ('SD', 'WI'), PARTITION q1_southcentral VALUES ('OK', 'TX'));

PPAS 分区表用法

注意两个相关参数

```
set default_with_oids = on; -- with oids（多一列），设置为OFF时不允许使用Oracle的创建分区表的语法。

set default_with_rowids = on; -- oid上增加一列UK索引。如果业务上不需要使用rowid虚拟列，强烈建议设置为OFF。
```

语法与Oracle相似，前面两个Oracle中的创建分区表的SQL可以直接运行。

创建分区表索引

1、10以前的版本，不允许直接在表上创建

postgres=# \set VERBOSITY verbose postgres=# create index idx_sales_1 on sales (prod_id); ERROR: 42809: cannot create index on partitioned table "sales" LOCATION: DefineIndex, indexcmds.c:396

只能在分区上创建索引。

如果分区很多，可以写成DO或者函数，简化整个过程。

需要用到inherit找到所有继承表。

例如要对sales的所有分区

do language plpgsql $$ declare s name; t name; tbl oid := 'public.sales'::regclass; col text := format('%I,%I', 'prod_id', 'quantity_sold'); o oid; begin for o in select inhrelid from pg_inherits where inhparent=tbl loop select nspname, relname into s,t from pg_class t1 join pg_namespace t2 on (t1.relnamespace=t2.oid) where t1.oid=o; execute format('create index %s on %I.%I (%s)', 'md5'||md5(random()::text), s, t, col); end loop; end; $$;

如下:

将分区表创建索引的功能封装成函数

```
输入:

主表所在schema
主表名
索引字段
索引方法
表空间
是否需要不堵塞DML
```

函数如下

create or replace function create_index_on_partition_table ( ptblnsp name, -- 主表所在schema, 大小写敏感，推荐全部使用小写。 ptbl name, -- 主表名, 大小写敏感，推荐全部使用小写。 cols name[], -- 索引字段, 严格按顺序来创建，大小写敏感，推荐全部使用小写。 am name default 'btree', -- 索引方法 tbs name default 'pg_default' -- 表空间 ) returns void as $$ declare s name; t name; tbl oid := format('%I.%I', ptblnsp, ptbl)::regclass; col text; o oid; begin select string_agg(format('%I',x),', ') into col from unnest(cols) x; for o in select inhrelid from pg_inherits where inhparent=tbl loop perform 1 from (select pg_get_indexdef(indexrelid) as def from pg_index where indrelid=o) t where substring(def, '$(.*)$')=col limit 1; if not found then -- 避免重复创建，例如新增了分区后，需要对新建分区添加索引，老分区已经添加就不需要再加了 select nspname, relname into s,t from pg_class t1 join pg_namespace t2 on (t1.relnamespace=t2.oid) where t1.oid=o; execute format('create index %s on %I.%I (%s)', 'md5'||md5(random()::text), s, t, col); end if; end loop; end; $$ language plpgsql strict;

使用举例

CREATE TABLE salesabc ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , "QWWWuantity_sold" NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY RANGE (time_id) ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy')) , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy')) , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy')) , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy')) );

创建分区索引

select create_index_on_partition_table('public','salesabc','{prod_id, QWWWuantity_sold,amount_sold}');

查看索引已正确创建

postgres=# select indexrelid::regclass,indrelid::Regclass,pg_get_indexdef(indexrelid) from pg_index where indrelid in (select inhrelid from pg_inherits where inhparent='public.salesabc'::regclass); indexrelid | indrelid | pg_get_indexdef -------------------------------------+------------------------+------------------------------------------------------------------------------------------------------------------------------------------ pg_oid_120027673_index | salesabc_sales_q1_2006 | CREATE UNIQUE INDEX pg_oid_120027673_index ON public.salesabc_sales_q1_2006 USING btree (oid) pg_oid_120027680_index | salesabc_sales_q2_2006 | CREATE UNIQUE INDEX pg_oid_120027680_index ON public.salesabc_sales_q2_2006 USING btree (oid) pg_oid_120027687_index | salesabc_sales_q3_2006 | CREATE UNIQUE INDEX pg_oid_120027687_index ON public.salesabc_sales_q3_2006 USING btree (oid) pg_oid_120027694_index | salesabc_sales_q4_2006 | CREATE UNIQUE INDEX pg_oid_120027694_index ON public.salesabc_sales_q4_2006 USING btree (oid) md56a2cbe5776d443387f068bbe539533e5 | salesabc_sales_q1_2006 | CREATE INDEX md56a2cbe5776d443387f068bbe539533e5 ON public.salesabc_sales_q1_2006 USING btree (prod_id, "QWWWuantity_sold", amount_sold) md5e1c5c1645d5c9cd6500040d98b1ff39d | salesabc_sales_q2_2006 | CREATE INDEX md5e1c5c1645d5c9cd6500040d98b1ff39d ON public.salesabc_sales_q2_2006 USING btree (prod_id, "QWWWuantity_sold", amount_sold) md519a145aefd180dd7f4a43e57f3254d61 | salesabc_sales_q3_2006 | CREATE INDEX md519a145aefd180dd7f4a43e57f3254d61 ON public.salesabc_sales_q3_2006 USING btree (prod_id, "QWWWuantity_sold", amount_sold) md5402f9b0fb2919c8b4545033ac450a140 | salesabc_sales_q4_2006 | CREATE INDEX md5402f9b0fb2919c8b4545033ac450a140 ON public.salesabc_sales_q4_2006 USING btree (prod_id, "QWWWuantity_sold", amount_sold) (8 rows)

Enterprisedb 11（POLARDDB PG， PPAS 11）都支持了直接对分区表创建索引，不需要以上繁琐的操作。