Oracle

Oracle分区表

分区类型

范围分区(RANGE)

范围分区是将数据按范围映射到每一个分区里去，分区范围是在创建分区时指定的，例如经常使用的时间分区，把数据表按月分区。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

create table partition_tab
(
    PID NUMBER NOT NULL PRIMARY KEY,
    DATE_TIME DATE
)
PARTITION BY RANGE (DATE_TIME)
(
    PARTITION P2001 VALUES LESS THAN (TO_DATE('2020-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) TABLESPACE USERS,
    PARTITION P2002 VALUES LESS THAN (TO_DATE('2020-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) TABLESPACE USERS,
    PARTITION PMAX VALUES LESS THAN (MAXVALUE) TABLESPACE USERS
)

在范围分区中，每个分区都必须通过VALUES LESS THAN来限制分区的上限值，低于上限值的数据就会存入该分区中，下限值则是前一个分区的上限值。其中MAX分区是用来存储未满足其它分区条件的数据，包括空值。如果不存在MAX分区我们插入不满足其它分区条件时就会发生如下错误

1
2

insert into  partition_tab values(1,to_date('2020-04-05 15:00:00','YYYY-MM-DD HH24:MI:SS'));
ORA-14400: inserted partition key does not map to any partition ORA-06512: at "SYS.DBMS_SQL", line 1721

列表分区(LIST)

列表分区的分区字段通常值的可选项只有几个，例如按照类型或者国家分区

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

create table list_part
(
    PID number not null primary key,
    dbname varchar2(10)
)
partition by list(dbname)
(
    partition pmysql values('5.7') TABLESPACE USERS, 
    partition predis values('4.0') TABLESPACE USERS,
    partition poracle values('11g') TABLESPACE USERS
)

散列分区(HASH)

散列分区通过对字段值进行HASH运算，再确定进入哪个分区，只需要指定分区的数量，通常建议2的N次方。当列的值没有合适的条件时，可以考虑使用HASH分区

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE TABLE HASH_PART  
(  
    ID NUMBER NOT NULL PRIMARY KEY,
    empno NUMBER (4),  
    ename VARCHAR2 (30)
)  
PARTITION BY  HASH (empno) 
(
  PARTITION PNO1 TABLESPACE USERS,
  PARTITION PNO2 TABLESPACE USERS,
  PARTITION PNO3 TABLESPACE USERS,
  PARTITION PNO4 TABLESPACE USERS
)

也可以简写为下列格式的语句

1
2
3
4
5
6
7
8

CREATE TABLE HASH_PART  
(  
    ID NUMBER NOT NULL PRIMARY KEY,
    empno NUMBER (4),  
    ename VARCHAR2 (30)
)  
PARTITION BY  HASH (empno) PARTITIONS 8
STORE IN (users,test)

范围列表分区

范围列表分区是把范围分区和列表分区结合起来，表首先按范围分区，然后再进行列表分区，分区中的分区称之为子分区

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

CREATE TABLE range_list_tab
(
    ID NUMBER NOT NULL PRIMARY KEY,
    NAME VARCHAR2(20),
    COST NUMBER(10),
    CREATE_TIME DATE,
    TYPE VARCHAR2(20)
)
PARTITION BY RANGE(CREATE_TIME) SUBPARTITION BY LIST (TYPE)
(
   PARTITION P1 VALUES LESS THAN(TO_DATE('2020-02-01','YYYY-MM-DD')) TABLESPACE USERS
    (
        SUBPARTITION P1_SUB1 VALUES ('1') TABLESPACE USERS,
        SUBPARTITION P1_SUB2 VALUES ('2') TABLESPACE USERS
    ),
   PARTITION P2 VALUES LESS THAN (TO_DATE('2020-03-01','YYYY-MM-DD')) TABLESPACE USERS
    (
        SUBPARTITION P2_SUB1 VALUES ('1') TABLESPACE USERS,
        SUBPARTITION P2_SUB2 VALUES ('2') TABLESPACE USERS
    )
)

范围散列分区

范围散列分区是把范围分区和列表分区结合起来，表先安范围分区，再进行散列分区

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE TABLE range_hash_tab
(
    ID NUMBER NOT NULL PRIMARY KEY,
    NAME VARCHAR2(20),
    COST NUMBER(10),
    CREATE_TIME DATE,
    TYPE VARCHAR2(20)
)
PARTITION BY RANGE(CREATE_TIME) SUBPARTITION BY hash (NAME) subpartitions 2 store in (USERS)
(
   PARTITION P1 VALUES LESS THAN(TO_DATE('2020-02-01','YYYY-MM-DD')),
   PARTITION P2 VALUES LESS THAN (TO_DATE('2020-03-01','YYYY-MM-DD'))
)

分区表的分区可以指定独立的分区表空间，可以与表不一样，甚至分区之间也可以不一样。将分区数据文件分散在多个磁盘上，来提高IO性能。

分区表维护

添加分区

当我们分区不够时，我们可以添加分区，例如按照时间的范围分区，数据库目前只有2月份的分区，现在需要添加3月份的分区进去

1

ALTER TABLE partition_tab ADD PARTITION P2003 VALUES LESS THAN (TO_DATE('2020-04-01 00:00:00','YYYY-MM-DD HH24:MI:SS'));

添加的分区要大于之前所有的分区，例如设置了MAXVALUE的话添加分区将不被允许，会出现如下错误

1

ORA-14074: partition bound must collate higher than that of the last partition

LIST分区表添加分区

1

alter table list_part add partition pmongo values('mongodb');

hash分区表添加分区(所有数据都将重新计算hash值，重新分配到分区中，完成后需要重建索引)

1

ALTER TABLE HASH_PART ADD PARTITION PNO5 update indexes;

删除分区

当我们想删除某个分区或者子分区的话，我可以直接针对分区进行DROP操作

1
2

ALTER TABLE list_part DROP PARTITION poracle;  
ALTER TABLE range_list_tab DROP SUBPARTITION P2_SUB2;

此功能HASH分区不支持，删除分区会导致GLOBAL索引失效，可以指定update indexes选项。如果正在删除的分区是最后一个分区了，那分区将无法删除，需要的话可以直接删除表。

截断分区

截断分区能够删除分区或子分区的数据而不删除分区

1
2

ALTER TABLE list_part TRUNCATE PARTITION predis update indexes;
ALTER TABLE range_list_tab TRUNCATE SUBPARTITION P2_SUB2 update indexes;

合并分区

合并分区能够将相邻的几个分区或子分区合并成一个分区，合并分区只能向上限值更高的分区合并，而无法向下

1

ALTER TABLE partition_tab MERGE PARTITIONS P2001,P2002 INTO PARTITION P2002 UPDATE INDEXES;

合并执行后P2001分区将被删除，数据全部移入新的合并分区中，产生大量IO。如果不指定UPDATE INDEXES选项，LOCAL INDEX将会失效，可以通过下面的语句重建索引

1

ALTER TABLE partition_tab MODIFY PARTITION P2002 REBUILD UNUSABLE LOCAL INDEXES;

拆分分区

拆分分区与合并分区相反，能够将一个分区按照条件拆分成多个分区，在我们设置了MAXVALUE或者DEFAULT分区时，我们再想新建分区就只能用拆分的方式了

1

ALTER TABLE partition_tab SPLIT PARTITION P2002 AT(to_date('2020-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) INTO  (partition P2001, partition P2002) UPDATE INDEXES

HASH分区不支持该操作，另外RANGE分区关键词为AT，LIST分区关键词为VALUES

接合分区(coalesce)

分区接合是针对散列分区或者散列子分区，能够减少分区数，当散列分区接合后，数据将分散到其它分区中，被接合的分区是由数据库决定的，完成后该分区会被删除。

1
2

ALTER TABLE HASH_PART COALESCE PARTITION;  
ALTER TABLE range_hash_tab MODIFY PARTITION P1 COALESCE SUBPARTITION;

操作会导致local index和global index失效，可以添加update indexes选项或者重建索引

交换分区

交换分区将分区或者子分区与非分区表进行数据交换，采用修改数据字典的逻辑存储方式修改，交换比较快。同时，组合分区也可以与非组合分区进行交换。

分区表与非分区表的交换

1

ALTER TABLE partition_tab EXCHANGE PARTITION P2001 WITH TABLE TAB_2001;
  • 交换的表结构必须一致
  • 操作会导致global index失效，可以添加update indexes选项或者重建索引
  • INCLUDEING INDEXES选项能够同步将本地索引也交换

移动分区

移动分区能够做到将分区移动到其它表空间上

1

Alter table partition_tab move partition P2001 tablespace USERS2 update indexes;

修改LIST分区

我们可以对LIST分区表添加值或减少值

1
2

alter table list_part modify partition pmysql add values('8.0');
alter table list_part modify partition pmysql drop values('5.6');

重命名表分区

1

ALTER TABLE <TABLE_NAME> RENAME PARTITION <PARTITION_NAME> TO <NEW_PARTITION_NAME>;

日常查询

查询表分区信息

1
2

select * from dba_tab_partitions;
select * from DBA_TAB_SUBPARTITIONS

查询索引信息

1

Select index_name,status From dba_indexes

查询所有分区表信息

1

select * from DBA_PART_TABLES

查看分区列

1
2

select * from DBA_PART_KEY_COLUMNS 
select * from DBA_SUBPART_KEY_COLUMNS

分区表索引

本地分区索引使用LOCAL属性创建，索引分区的所有键均指向分区中存储的行。本地分区索引也是分区管理，具有和分区表相同的分区，子分区，分区表会自动维护本地分区索引相应的分区。

本地索引的优点：

  • 在分区表执行除SPLIT PARTITION或ADD PARTITION 外的维护命令时，仅影响当前分区
  • 本地分区索引支持分区的独立性
  • 本地分区索引支持单一分区的数据装载和卸载
  • 本地分区索引与分区表的均衡性带来性能提升
  • 分区表中的位图索引必须是本地索引，非分区表不能建立分区位图索引

①本地前缀索引是指以索引列的左前缀来分区的，如果存在子分区则要求其子分区的分区键包含在索引键中，本地前缀索引可以是唯一索引，也可以不是。 ②本地非前缀索引指没有以索引列的前缀来分区，但子分区的分区键不在索引键中，本地非前缀索引不可以是唯一索引，除非分区键是索引键的子集

1

create index LOCAL_IDX_PART on partition_tab(date_time) LOCAL;

全局分区索引是指特定索引分区中的键可能存在指向存储在分区表中的多个分区或子分区的行，使用GLOBAL属性创建，全局索引只支持按范围或散列分区，全局索引为B-TREE索引。全局分区索引必须是前缀的。

1
2
3
4
5
6
7

create index GLOBAL_IDX_PART on partition_tab(date_time)  
GLOBAL partition by  range(date_time)  
(  
   PARTITION P2001 VALUES LESS THAN (TO_DATE('2020-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS')),  
   PARTITION P2002 VALUES LESS THAN (TO_DATE('2020-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS')),
   PARTITION PMAX VALUES LESS THAN (MAXVALUE)
);

在跨分区查询比较多的情况全局索引性能更好，在分区内部查询比较多的情况下LOCAL本地索引性能更好。

对于分区索引的删除，LOCAL索引不能指定分区名单独删除分区索引，只能随着分区的删除而删除。而GLOBAL索引可以指定分区名，删除某一个分区，但是如果分区不为空，则会导致更高一级的索引分区失效。

1

ALTER INDEX GLOBAL_IDX_PART drop partition P2001 ;
comments powered by Disqus