华为GaussDB T 管理分区表

墨天轮 2019-10-12

1337

管理分区表

操作场景

GaussDB 100分区表支持范围分区（Range Partition）、列表分区（List Partition）、哈希分区（Hash Partition）与间隔分区（INTERVAL Partition）。表分区功能可以改善数据库的查询性能，增强可用性，方便维护，以及均衡I/O等。

操作步骤

按照以下方式对范围分区表进行操作。

删除已存在的重名表，以一个已存在的表“staffs_p”为例。
```
DROP TABLE IF EXISTS staffs_p;
```


创建分区表staffs_p。CREATE TABLE staffs_p
(
  staff_ID    NUMBER(6) not null,
  FIRST_NAME     VARCHAR2(20),
  LAST_NAME      VARCHAR2(25),
  EMAIL          VARCHAR2(25),
  PHONE_NUMBER   VARCHAR2(20),
  HIRE_DATE      DATE,
  employment_ID         VARCHAR2(10),
  SALARY         NUMBER(8,2),
  COMMISSION_PCT NUMBER(2,2),
  MANAGER_ID     NUMBER(6),
  section_ID  NUMBER(4)
)
PARTITION BY RANGE (staff_ID)
(
  partition P_050_BEFORE values less than (50),
  partition P_100 values less than (100) ,
  partition P_150 values less than (150) ,
  partition P_200 values less than (200)
);

删除分区P_200_AFTER。ALTER TABLE staffs_p DROP PARTITION P_200_AFTER;

增加分区P_250，范围为 200 <= P_250 <=250。ALTER TABLE staffs_p ADD PARTITION P_250 VALUES LESS THAN (250);

查询分区P_150。SELECT * FROM staffs_p PARTITION (P_150);

分裂分区对一个分区进行分裂，将其分裂为两个新分区。当前只支持对range分区进行分裂。--分裂名为P_050_BEFORE 的分区，将其分裂为p_501和p_502，其中p_501的边界是25，p_502的边界是50。
ALTER TABLE staffs_p SPLIT PARTITION P_050_BEFORE  AT(25) INTO (PARTITION p_501, PARTITION p_502);


创建分区表索引创建分区表索引HR_staffS_p1_index1，不指定索引分区的名字。CREATE INDEX HR_staffS_p1_index1 ON staffs_p (staff_ID) LOCAL;

创建分区表索引HR_staffS_p1_index2，并指定索引分区的名字。CREATE INDEX HR_staffS_p1_index2 ON staffs_p (section_ID) LOCAL
(
    PARTITION section_ID1_index,
    PARTITION section_ID2_index TABLESPACE USERS,
    PARTITION section_ID3_index TABLESPACE USERS,
    PARTITION section_ID4_index,
    PARTITION staff_ID5_index
) TABLESPACE USERS;


按照以下方式对哈希分区表进行操作。删除已存在的重名表，以一个已存在的表“staffs_p2017和staffs_p2018”为例。DROP TABLE IF EXISTS staffs_p2017;
DROP TABLE IF EXISTS staffs_p2018;


创建分区表创建指定分区名，但不指定分区个数的哈希分区表。CREATE TABLE staffs_p2017
(
  staff_ID    NUMBER(6) not null,
  FIRST_NAME     VARCHAR2(20),
  LAST_NAME      VARCHAR2(25),
  EMAIL          VARCHAR2(25),
  PHONE_NUMBER   VARCHAR2(20),
  HIRE_DATE      DATE,
  employment_ID         VARCHAR2(10),
  SALARY         NUMBER(8,2),
  COMMISSION_PCT NUMBER(2,2),
  MANAGER_ID     NUMBER(6),
  section_ID  NUMBER(4)
)
PARTITION BY HASH(staff_ID)
(
  PARTITION P_01 tablespace USERS,
  PARTITION P_02 tablespace USERS,
  PARTITION P_03 tablespace USERS,
  PARTITION P_04 tablespace USERS,
  PARTITION P_05 tablespace USERS
);


创建指定分区个数，但不指定分区名的哈希分区表。CREATE TABLE staffs_p2018
(
  staff_ID    NUMBER(6) not null,
  FIRST_NAME     VARCHAR2(20),
  LAST_NAME      VARCHAR2(25),
  EMAIL          VARCHAR2(25),
  PHONE_NUMBER   VARCHAR2(20),
  HIRE_DATE      DATE,
  employment_ID         VARCHAR2(10),
  SALARY         NUMBER(8,2),
  COMMISSION_PCT NUMBER(2,2),
  MANAGER_ID     NUMBER(6),
  section_ID  NUMBER(4)
)
PARTITION BY HASH (staff_ID) PARTITIONS 5 STORE IN(USERS,USERS);


删除分区执行删除操作时，最后一个分区的数据插入到前边的某个分区里，然后将最后一个分区删掉。如果只剩下一个分区，删除时会报错。
ALTER TABLE staffs_p2017 COALESCE PARTITION;
ALTER TABLE staffs_p2018 COALESCE PARTITION;

增加分区part_06。ALTER TABLE staffs_p2017 ADD PARTITION part_06;

查询分区查询指定分区名的哈希分区表的分区。查询分区P_04。SELECT * FROM staffs_p2017 PARTITION(P_04);


查询指定分区个数的哈希分区表的分区。--通过联合查询系统表SYS.SYS_TABLE_PARTS和SYS.SYS_TABLES查询出分区名。
SELECT * FROM SYS.SYS_TABLE_PARTS WHERE TABLE#= (SELECT ID FROM SYS.SYS_TABLES WHERE NAME='STAFFS_P2018');

USER#TABLE#PART#NAME                                                             HIBOUNDLEN   HIBOUNDVAL                                                       SPACE#ORG_SCN              ENTRY                INITRANS     PCTFREE      FLAGS        BHIBOUNDVAL                                                      ROWCNT       BLKCNT       EMPCNT       AVGRLN       SAMPLESIZE   ANALYZETIME
------------ ------------ ------------ ---------------------------------------------------------------- ------------ ---------------------------------------------------------------- ------------ -------------------- -------------------- ------------ ------------ ------------ ---------------------------------------------------------------- ------------ ------------ ------------ ------------ ------------ ----------------------
2            71           10           SYS_P1023                                                        0                                                                             3            2189599603179522     4393751543808        2            8            0                                                                                                                                             
2            71           20           SYS_P1024                                                        0                                                                             3            2189599603179523     4393751543808        2            8            0                                                                                                                                             
2            71           30           SYS_P1025                                                        0                                                                             3            2189599603179524     4393751543808        2            8            0                                                                                                                                             
2            71           40           SYS_P1026                                                        0                                                                             3            2189599603179525     4393751543808        2            8            0

4 rows fetched.

--查询名为SYS_P1024的分区的数据。
SELECT * FROM STAFFS_P2018 PARTITION(SYS_P1024);


创建分区表索引创建分区表索引HR_staffS_p1_index_2017，不指定索引分区的名字。CREATE INDEX HR_staffS_p1_index_2017 ON staffs_p2017 (staff_ID) LOCAL;

创建分区表索引HR_staffS_p1_index_2017_1，并指定索引分区的名字。CREATE INDEX HR_staffS_p1_index_2017_1 ON staffs_p2017 (employment_ID) LOCAL
(
    PARTITION employment_ID1_index,
    PARTITION employment_ID2_index TABLESPACE USERS,
    PARTITION employment_ID3_index TABLESPACE USERS,
    PARTITION employment_ID4_index,
    PARTITION employment_ID5_index
) TABLESPACE USERS;




按照以下方式对间隔分区表进行操作。删除已存在的重名表，以一个已存在的表“staffs_p2016”为例。DROP TABLE IF EXISTS staffs_p2016;


创建分区表staffs_p2016。CREATE TABLE staffs_p2016
(
  staff_ID    NUMBER(6) not null,
  FIRST_NAME     VARCHAR2(20),
  LAST_NAME      VARCHAR2(25),
  EMAIL          VARCHAR2(25),
  PHONE_NUMBER   VARCHAR2(20),
  HIRE_DATE      DATE,
  employment_ID         VARCHAR2(10),
  SALARY         NUMBER(8,2),
  COMMISSION_PCT NUMBER(2,2),
  MANAGER_ID     NUMBER(6),
  section_ID  NUMBER(4)
)
PARTITION BY RANGE (staff_ID)
INTERVAL (2)
(
  partition P_50 values less than (50),
  partition P_100 values less than (100),
  partition P_150 values less than (150)
);

删除分区--通过联合查询系统表SYS.SYS_TABLE_PARTS和SYS.SYS_TABLES查询出分区名。
SELECT * FROM SYS.SYS_TABLE_PARTS WHERE TABLE# = (SELECT ID FROM SYS.SYS_TABLES WHERE NAME='STAFFS_P2016');

USER#TABLE#PART#NAME                                                             HIBOUNDLEN   HIBOUNDVAL                                                       SPACE#ORG_SCN              ENTRY                INITRANS     PCTFREE      FLAGS        BHIBOUNDVAL                                                      ROWCNT       BLKCNT       EMPCNT       AVGRLN       SAMPLESIZE   ANALYZETIME
------------ ------------ ------------ ---------------------------------------------------------------- ------------ ---------------------------------------------------------------- ------------ -------------------- -------------------- ------------ ------------ ------------ ---------------------------------------------------------------- ------------ ------------ ------------ ------------ ------------ ----------------------
2            70           10           P_50                                                             2            50                                                               3            47529132593153       4393751543808        2            8            0                                    
2            70           20           P_100                                                            3            100                                                              3            47529132687361       4393751543808        2            8            0                                    
2            70           30           P_150                                                            3            150                                                              3            47529132744705       4393751543808        2            8            0                                    

3 rows fetched.

--删除名为P_50的分区。
ALTER TABLE staffs_p2016 DROP PARTITION P_50;

增加分区间隔分区表会随着用户插入的数据动态扩展，无需手动增加。自动生成的分区名可以从SYS.SYS_TABLE_PARTS表查询出来。
查询分区--通过联合查询系统表SYS.SYS_TABLE_PARTS和SYS.SYS_TABLES查询出分区名。
SELECT * FROM SYS.SYS_TABLE_PARTS WHERE TABLE# = (SELECT ID FROM SYS.SYS_TABLES WHERE NAME='STAFFS_P2016');

USER#TABLE#PART#NAME                                                             HIBOUNDLEN   HIBOUNDVAL                                                       SPACE#ORG_SCN              ENTRY                INITRANS     PCTFREE      FLAGS        BHIBOUNDVAL                                                      ROWCNT       BLKCNT       EMPCNT       AVGRLN       SAMPLESIZE   ANALYZETIME
------------ ------------ ------------ ---------------------------------------------------------------- ------------ ---------------------------------------------------------------- ------------ -------------------- -------------------- ------------ ------------ ------------ ---------------------------------------------------------------- ------------ ------------ ------------ ------------ ------------ ----------------------
0            242          20           P_100                                                            3            100                                                              0            6386343436210177     4393751543808        2            8            0                                          
0            242          30           P_150                                                            3            150                                                              0            6386343436247041     4393751543808        2            8            0                                          

2 rows fetched.

--查询名为P_150的分区的数据。
SELECT * FROM STAFFS_P2016 PARTITION(P_150);

创建分区表索引创建分区表索引HR_staffS_p1_index_2016，不指定索引分区的名字。CREATE INDEX HR_staffS_p1_index_2016 ON staffs_p2016 (staff_ID) LOCAL;


创建分区表索引HR_staffS_p1_index_20162，并指定索引分区的名字。CREATE INDEX HR_staffS_p1_index_20162 ON staffs_p2016 (section_ID) LOCAL
(
    PARTITION section_ID2_index,
    PARTITION section_ID3_index,
   PARTITION section_ID4_index
) TABLESPACE USERS;




按照以下方式对列表分区表进行操作。删除已存在的重名表，以一个已存在的表“education”为例。DROP TABLE IF EXISTS education;


创建分区表education。CREATE TABLE education(staff_id INT NOT NULL, higest_degree CHAR(20), graduate_school VARCHAR(64), graduate_date DATETIME, education_note VARCHAR(70))
PARTITION BY LIST(higest_degree)
(
PARTITION doctor VALUES ('doctor') TABLESPACE USERS,
PARTITION master VALUES ('master') TABLESPACE USERS,
PARTITION undergraduate VALUES ('undergraduate') TABLESPACE USERS
);


删除分区undergraduate。ALTER TABLE education DROP PARTITION undergraduate;

增加分区postdoctor。ALTER TABLE education ADD PARTITION postdoctor VALUES('postdoctor');

查询分区postdoctor。SELECT * FROM education PARTITION (postdoctor);

创建分区表索引创建分区表索引idx_education1，不指定索引分区的名字。CREATE INDEX idx_education1 ON education(staff_id) LOCAL;

创建分区表索引idx_education2，并指定索引分区的名字。CREATE INDEX idx_education2 ON education(higest_degree) LOCAL
(
    PARTITION higest_degree1_index,
    PARTITION higest_degree4_index,
    PARTITION higest_degree5_index
) TABLESPACE USERS;

  gaussdb t
  「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
 关注作者
 
  
 【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
   评论

 领墨值

 
  有奖问卷
  意见反馈 
 客服小墨

华为GaussDB T 管理分区表

管理分区表

操作场景

相关概念

操作步骤

评论