PPAS(enterprisedb)(PG Oracle兼容版) 10以及以前版本分区表的使用，分区表索引的创建-阿里云开发者社区

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

2020-02-25 274

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云原生数据库 PolarDB MySQL 版，通用型 2核4GB 50GB

云原生数据库 PolarDB PostgreSQL 版，标准版 2核4GB 50GB

简介： 标签 PostgreSQL , enterprisedb , ppas , oracle 背景 PPAS 10以及以前的版本，对于Oracle分区表的使用，以及如何创建分区表的索引。 10 以及以前的版本，仅支持range, list分区。11开始支持HASH分区。 Oracle分区表语法 https://docs.oracle.com/cd/E18283_01/server.11

背景

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找到所有继承表。

postgres=# \d pg_inherits  
            Table "pg_catalog.pg_inherits"  
  Column   |  Type   | Collation | Nullable | Default   
-----------+---------+-----------+----------+---------  
 inhrelid  | oid     |           | not null |   
 inhparent | oid     |           | not null |   
 inhseqno  | integer |           | not null |   
Indexes:  
    "pg_inherits_relid_seqno_index" UNIQUE, btree (inhrelid, inhseqno)  
    "pg_inherits_parent_index" btree (inhparent)

例如要对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;  
$$;

如下:

postgres=# \d+ sales  
                                                 Table "public.sales"  
    Column     |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description   
---------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------  
 prod_id       | numeric(6,0)                |           |          |         | main     |              |   
 cust_id       | numeric                     |           |          |         | main     |              |   
 time_id       | timestamp without time zone |           |          |         | plain    |              |   
 channel_id    | character(1)                |           |          |         | extended |              |   
 promo_id      | numeric(6,0)                |           |          |         | main     |              |   
 quantity_sold | numeric(3,0)                |           |          |         | main     |              |   
 amount_sold   | numeric(10,2)               |           |          |         | main     |              |   
Partition key: RANGE (time_id) NULLS LAST  
Partitions: sales_sales_q1_2006 FOR VALUES FROM (MINVALUE) TO ('01-APR-06 00:00:00'),  
            sales_sales_q2_2006 FOR VALUES FROM ('01-APR-06 00:00:00') TO ('01-JUL-06 00:00:00'),  
            sales_sales_q3_2006 FOR VALUES FROM ('01-JUL-06 00:00:00') TO ('01-OCT-06 00:00:00'),  
            sales_sales_q4_2006 FOR VALUES FROM ('01-OCT-06 00:00:00') TO ('01-JAN-07 00:00:00')  
Has OIDs: yes  
  
postgres=# \d sales_sales_q1_2006  
                      Table "public.sales_sales_q1_2006"  
    Column     |            Type             | Collation | Nullable | Default   
---------------+-----------------------------+-----------+----------+---------  
 prod_id       | numeric(6,0)                |           |          |   
 cust_id       | numeric                     |           |          |   
 time_id       | timestamp without time zone |           |          |   
 channel_id    | character(1)                |           |          |   
 promo_id      | numeric(6,0)                |           |          |   
 quantity_sold | numeric(3,0)                |           |          |   
 amount_sold   | numeric(10,2)               |           |          |   
Partition of: sales FOR VALUES FROM (MINVALUE) TO ('01-APR-06 00:00:00')  
Indexes:  
    "pg_oid_120027427_index" UNIQUE, btree (oid)  
    "md5193df902f78920ac4d636ebcab5d50b1" btree (prod_id, quantity_sold)  
  
postgres=# \d sales_sales_q2_2006  
                      Table "public.sales_sales_q2_2006"  
    Column     |            Type             | Collation | Nullable | Default   
---------------+-----------------------------+-----------+----------+---------  
 prod_id       | numeric(6,0)                |           |          |   
 cust_id       | numeric                     |           |          |   
 time_id       | timestamp without time zone |           |          |   
 channel_id    | character(1)                |           |          |   
 promo_id      | numeric(6,0)                |           |          |   
 quantity_sold | numeric(3,0)                |           |          |   
 amount_sold   | numeric(10,2)               |           |          |   
Partition of: sales FOR VALUES FROM ('01-APR-06 00:00:00') TO ('01-JUL-06 00:00:00')  
Indexes:  
    "pg_oid_120027434_index" UNIQUE, btree (oid)  
    "md52c8ff555d00e2fd5245fafb3027a6d6d" btree (prod_id, quantity_sold)

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

输入:  
  
主表所在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）都支持了直接对分区表创建索引，不需要以上繁琐的操作。

其他

1、不支持非默认ops的情况，如果有非默认OPS的话，改一下以上函数(使用非默认ops)。

2、如果需要支持并行创建，改一下以上函数(使用dblink异步任务，同时使用CONCURRENTLY关键字创建索引)。

3、如果需要开启异步任务，同时对多个分区创建，改一下以上函数(使用dblink异步任务)。

参考

《PostgreSQL 快速给指定表每个字段创建索引 - 2 (近乎完美)》

《PostgreSQL dblink异步调用实践,跑并行多任务 - 例如开N个并行后台任务创建索引, 开N个后台任务跑若干SQL》

《在PostgreSQL中跑后台长任务的方法 - 使用dblink异步接口》

社区版本分区表使用:

《PostgreSQL 9.x, 10, 11 hash分区表用法举例》

《PostgreSQL 分区表如何支持多列唯一约束 - 枚举、hash哈希分区, 多列唯一, insert into on conflict, update, upsert, merge insert》

《PostgreSQL native partition 分区表性能优化之 - 动态SQL+服务端绑定变量》

《PostgreSQL 分区表、继承表记录去重方法》

《PostgreSQL pgbench tpcb 海量数据库测试 - 分区表测试优化》

《PostgreSQL 11 preview - 分区表增强汇总》

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

标签

背景

Oracle分区表语法

PPAS 分区表用法

创建分区表索引

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

其他

参考

免费领取阿里云RDS PostgreSQL实例、ECS虚拟机

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

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

标签

背景

Oracle分区表语法

PPAS 分区表用法

创建分区表索引

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

其他

参考

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像