PPAS(enterprisedb)(PG Oracle相容版) 10以及以前版本分區表的使用，分區表索引的建立

标簽

PostgreSQL , enterprisedb , ppas , oracle

https://github.com/digoal/blog/blob/master/201901/20190123_02.md#%E8%83%8C%E6%99%AF 背景

PPAS 10以及以前的版本，對于Oracle分區表的使用，以及如何建立分區表的索引。

10 以及以前的版本，僅支援range, list分區。11開始支援HASH分區。

https://github.com/digoal/blog/blob/master/201901/20190123_02.md#oracle%E5%88%86%E5%8C%BA%E8%A1%A8%E8%AF%AD%E6%B3%95 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'));

https://github.com/digoal/blog/blob/master/201901/20190123_02.md#ppas-%E5%88%86%E5%8C%BA%E8%A1%A8%E7%94%A8%E6%B3%95 PPAS 分區表用法

注意兩個相關參數

set default_with_oids = on;  -- with oids（多一列），設定為OFF時不允許使用Oracle的建立分區表的文法。  
  
set default_with_rowids = on;  -- oid上增加一列UK索引。 如果業務上不需要使用rowid虛拟列，強烈建議設定為OFF。

文法與Oracle相似，前面兩個Oracle中的建立分區表的SQL可以直接運作。

https://github.com/digoal/blog/blob/master/201901/20190123_02.md#%E5%88%9B%E5%BB%BA%E5%88%86%E5%8C%BA%E8%A1%A8%E7%B4%A2%E5%BC%95 建立分區表索引

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)

https://github.com/digoal/blog/blob/master/201901/20190123_02.md#%E5%B0%86%E5%88%86%E5%8C%BA%E8%A1%A8%E5%88%9B%E5%BB%BA%E7%B4%A2%E5%BC%95%E7%9A%84%E5%8A%9F%E8%83%BD%E5%B0%81%E8%A3%85%E6%88%90%E5%87%BD%E6%95%B0 将分區表建立索引的功能封裝成函數

輸入:  
  
主表所在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）都支援了直接對分區表建立索引，不需要以上繁瑣的操作。

https://github.com/digoal/blog/blob/master/201901/20190123_02.md#%E5%85%B6%E4%BB%96 其他

1、不支援非預設ops的情況，如果有非預設OPS的話，改一下以上函數(使用非預設ops)。

2、如果需要支援并行建立，改一下以上函數(使用dblink異步任務，同時使用CONCURRENTLY關鍵字建立索引)。

3、如果需要開啟異步任務，同時對多個分區建立，改一下以上函數(使用dblink異步任務)。

https://github.com/digoal/blog/blob/master/201901/20190123_02.md#%E5%8F%82%E8%80%83 參考

《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 - 分區表增強彙總》

https://github.com/digoal/blog/blob/master/201901/20190123_02.md#%E5%85%8D%E8%B4%B9%E9%A2%86%E5%8F%96%E9%98%BF%E9%87%8C%E4%BA%91rds-postgresql%E5%AE%9E%E4%BE%8Becs%E8%99%9A%E6%8B%9F%E6%9C%BA 免費領取阿裡雲RDS PostgreSQL執行個體、ECS虛拟機

PPAS(enterprisedb)(PG Oracle相容版) 10以及以前版本分區表的使用，分區表索引的建立

PPAS(enterprisedb)(PG Oracle相容版) 10以及以前版本分區表的使用，分區表索引的建立

标簽

https://github.com/digoal/blog/blob/master/201901/20190123_02.md#%E8%83%8C%E6%99%AF 背景

https://github.com/digoal/blog/blob/master/201901/20190123_02.md#oracle%E5%88%86%E5%8C%BA%E8%A1%A8%E8%AF%AD%E6%B3%95 Oracle分區表文法

https://github.com/digoal/blog/blob/master/201901/20190123_02.md#ppas-%E5%88%86%E5%8C%BA%E8%A1%A8%E7%94%A8%E6%B3%95 PPAS 分區表用法

https://github.com/digoal/blog/blob/master/201901/20190123_02.md#%E5%88%9B%E5%BB%BA%E5%88%86%E5%8C%BA%E8%A1%A8%E7%B4%A2%E5%BC%95 建立分區表索引

https://github.com/digoal/blog/blob/master/201901/20190123_02.md#%E5%B0%86%E5%88%86%E5%8C%BA%E8%A1%A8%E5%88%9B%E5%BB%BA%E7%B4%A2%E5%BC%95%E7%9A%84%E5%8A%9F%E8%83%BD%E5%B0%81%E8%A3%85%E6%88%90%E5%87%BD%E6%95%B0 将分區表建立索引的功能封裝成函數

https://github.com/digoal/blog/blob/master/201901/20190123_02.md#%E5%85%B6%E4%BB%96 其他

https://github.com/digoal/blog/blob/master/201901/20190123_02.md#%E5%8F%82%E8%80%83 參考

https://github.com/digoal/blog/blob/master/201901/20190123_02.md#%E5%85%8D%E8%B4%B9%E9%A2%86%E5%8F%96%E9%98%BF%E9%87%8C%E4%BA%91rds-postgresql%E5%AE%9E%E4%BE%8Becs%E8%99%9A%E6%8B%9F%E6%9C%BA 免費領取阿裡雲RDS PostgreSQL執行個體、ECS虛拟機

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List