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

开发者社区> 数据库> 正文

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

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

标签

PostgreSQL , enterprisedb , ppas , oracle


背景

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 - 分区表 增强 汇总》

 

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

版权声明:如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件至:developerteam@list.alibaba-inc.com 进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章