聊聊怎样使用Oracle分区表

[[377098]]

本文转载自微信公众号「 jinjunzhu」，作者 jinjunzhu 。转载本文请联系 jinjunzhu公众号。

单表的数据量如果太大，会影响到读写性能。我们可以使用分库分表来解决单表的性能问题。Oracle的分区表是将一张大表在物理上分成几张较小的表，从逻辑上来看仍然是一张完整的表。这样每次DML操作可以只考虑其中一张分区表。oracle建议单表大小超过2GB时就使用分区表。

今天我们就来聊一聊分区表的使用。

分区类型

范围分区

范围分区是最常用的分区方法，它使用分区键来作为划分分区的依据，分区键可以使用时间、id等易于比较的字段。

1.使用id进行分区

我们创建一张操作记录表，有3个字段，分别是id，type和op_time，这里我们使用id对这张表做3个分区，分别为t_operate_log_1(id<100)，t_operate_log_2(100<=id<200)，t_operate_log_3(id>=200),建表sql如下：

create table t_operate_log 
( 
  id   number(7), 
  type varchar(1), 
  op_time date 
) 
PARTITION BY RANGE(id) 
( 
 partition t_operate_log_1 values less than (100), 
 partition t_operate_log_2 values less than (200), 
 partition t_operate_log_3 values less than (300) 
) 

创建表成功后，我们插入3条数据，id分别是1，100，200，sql如下：

insert into t_operate_log values(1, '1', sysdate); 
insert into t_operate_log values(100, '1', sysdate); 
insert into t_operate_log values(200, '1', sysdate); 

下面这3条sql分别可以查到id是1，100，200的这3条记录：

select * from t_operate_log partition(t_operate_log_1); 
select * from t_operate_log partition(t_operate_log_2); 
select * from t_operate_log partition(t_operate_log_3); 

下面这个sql可以查到t_operate_log中所有记录：

select * from t_operate_log 

注意：我们可以用下面命令再添加一个分区：

ALTER TABLE t_operate_log ADD PARTITION t_operate_log_4 VALUES LESS THAN(400); 

但是要注意一点，如果一个分区的范围是maxvalue(比如把300替换成maxvalue)，添加分区会失败。

我们也可以用下面命令删除一个分区：

ALTER TABLE t_operate_log DROP PARTITION t_operate_log_4; 

2.使用时间进行分区

我们还是使用上面的表进行试验，这次我们使用op_time字段做3个分区，分别为：

t_operate_log_1(op_time时间小于2019-01-17) 
 
t_operate_log_2(2019-01-17<=id<2020-01-17)，t_operate_log_3(id>=2020-01-17) 

建表sql如下：

create table t_operate_log 
( 
  id   number(7), 
  type varchar(1), 
  op_time date 
) 
PARTITION BY RANGE(op_time) 
( 
 partition t_operate_log_1 values less than (to_date('2019-01-17','yyyy-MM-dd')), 
 partition t_operate_log_2 values less than (to_date('2020-01-17','yyyy-MM-dd')), 
 partition t_operate_log_3 values less than (maxvalue) 
) 

创建表成功后，我们插入3条数据，id分别是1，2，3，时间分别是2019-01-16、2019-01-17、2020-01-17：

insert into t_operate_log values(1, '1', to_date('2019-01-16','yyyy-MM-dd')); 
insert into t_operate_log values(2, '1', to_date('2019-01-17','yyyy-MM-dd')); 
insert into t_operate_log values(3, '1', to_date('2020-01-17','yyyy-MM-dd')); 

下面这3条sql分别可以查到id是1，2，3的这3条记录：

select * from t_operate_log partition(t_operate_log_1); 
select * from t_operate_log partition(t_operate_log_2); 
select * from t_operate_log partition(t_operate_log_3); 

下面这个sql可以查到t_operate_log中所有记录：

select * from t_operate_log 

列表分区

列表分区的使用场景是表中的某一列只有固定几个值，比如上面的操作日志表，假如我们的type有4个类型：add、edit、delete、query，我们建立分区表如下：

create table t_operate_log 
( 
  id   number(7), 
  type varchar2(10), 
  op_time date 
) 
PARTITION BY list(type) 
( 
 partition t_operate_log_add values('add'), 
 partition t_operate_log_delete values('delete'), 
 partition t_operate_log_edit values('edit'), 
 partition t_operate_log_query values('query') 
) 

创建表成功后，我们插入4条数据，type分别为add，delete，edit，query

insert into t_operate_log values(1, 'add', to_date('2019-01-16','yyyy-MM-dd')); 
insert into t_operate_log values(2, 'delete', to_date('2019-01-16','yyyy-MM-dd')); 
insert into t_operate_log values(3, 'edit', to_date('2020-01-16','yyyy-MM-dd')); 
insert into t_operate_log values(4, 'query', to_date('2020-01-16','yyyy-MM-dd')); 

下面的4条sql分别可以查出每一种type类型对应的数据：

select * from t_operate_log partition(t_operate_log_add); 
select * from t_operate_log partition(t_operate_log_delete); 
select * from t_operate_log partition(t_operate_log_edit); 
select * from t_operate_log partition(t_operate_log_query); 

注意：我们可以给列表分区增加元素，比如我们在t_operate_log_query这个分区表中增加一个元素"select",sql如下：

ALTER TABLE t_operate_log MODIFY PARTITION t_operate_log_query ADD VALUES('select'); 

也可以给列表分区删除元素，比如我们在t_operate_log_query这个分区表中删除元素"select",sql如下：

ALTER TABLE t_operate_log MODIFY PARTITION t_operate_log_query DROP VALUES('select'); 

如果每个分区的数据量不大，没有必须做太多分区，我们创建分区时可以减少数量，如下创建分区的sql我们把t_operate_log创建成了2个分区：

create table t_operate_log 
( 
  id   number(7), 
  type varchar2(10), 
  op_time date 
) 
PARTITION BY list(type) 
( 
 partition t_operate_log_add_del values('add','delete'), 
 partition t_operate_log_edit_query values('edit','query') 
) 

创建成功后我们还是使用上面的insert语句插入4条数据，成功后我们用下面的sql查询，分别可以查询出2条数据：

#查询出type是add和delete的数据 
select * from t_operate_log partition(t_operate_log_add_del); 
#查询出type是edit和query的数据 
select * from t_operate_log partition(t_operate_log_edit_query); 

HASH分区

范围分区和列表分区都使用了某一个字段来做分区键，使用这个字段的值作为分区的依据，使用简单。但是有一个问题就是分区键的区分度要大，不然容易存在分区数据量严重不均匀的情况。

如果没有一个合适的分区键，使用HASH分区就是一个很好的选择，HASH分区的好处是可以让分区表数据分布均匀。我们还是以上面的表为例，我们使用HASH分区来创建4个分区表，sql如下：

create table t_operate_log 
( 
  id   number(7), 
  type varchar2(10), 
  op_time date 
) 
PARTITION BY hash(id) 
( 
 partition t_operate_log_1, 
 partition t_operate_log_2, 
 partition t_operate_log_3, 
 partition t_operate_log_4 
) 

创建成功后我们插入100条数据，id是从1~100,我们分别查询4个分区表的数据，数据条数分别是20、28、25、27。

使用HASH分区有2个建议：

• 分区键的值最好是连续的
• 分区数量最好是2的n次方，这样可以对hash运算更加友好(想想java中HashMap构造函数的initialCapacity参数)

注意：

• HASH分区支持添加操作，比如我们添加一个分区，sql如下：

ALTER TABLE t_operate_log ADD PARTITION t_operate_log_5; 

• 创建后我们查询t_operate_log_5这张表，发现也有数据，这是因为添加或删除分区时，所有数据会重新计算HASH值，重新分配到不同的分区表中。
• HASH分区是不能删除的，删除会报“ORA-14255:不能对范围、列表，组合范围或组合列表方式对表进行分区”

范围列表组合分区

在一些复杂的场景下，我们可以使用范围和列表组合分区来进行分区，比如在前面讲范围分区和列表分区的例子，我们做一个范围列表组合分区的改进，sql如下：

create table t_operate_log 
( 
  id   number(7), 
  type varchar2(10), 
  op_time date 
) 
PARTITION BY RANGE(op_time) SUBPARTITION BY LIST (type)   
(   
   PARTITION t_operate_log_time_1 VALUES LESS THAN(to_date('2021-01-16','yyyy-MM-dd')) 
  (        
     SUBPARTITION t_operate_log_add_1 values('add'), 
     SUBPARTITION t_operate_log_delete_1 values('delete'), 
     SUBPARTITION t_operate_log_edit_1 values('edit'), 
     SUBPARTITION t_operate_log_query_1 values('query') 
  ),    
   PARTITION t_operate_log_time_2 VALUES LESS THAN (to_date('2021-01-17','yyyy-MM-dd')) 
  (    
       SUBPARTITION t_operate_log_add_2 values('add'), 
     SUBPARTITION t_operate_log_delete_2 values('delete'), 
     SUBPARTITION t_operate_log_edit_2 values('edit'), 
     SUBPARTITION t_operate_log_query_2 values('query') 
  )    
) 

上面我按照op_time做了分区，然后按照type做了子分区，这是我插入8条数据，每张子分区表一条，sql如下：

insert into t_operate_log values(1, 'add', to_date('2021-01-15','yyyy-MM-dd')); 
insert into t_operate_log values(2, 'delete', to_date('2021-01-15','yyyy-MM-dd')); 
insert into t_operate_log values(3, 'edit', to_date('2021-01-15','yyyy-MM-dd')); 
insert into t_operate_log values(4, 'query', to_date('2021-01-15','yyyy-MM-dd')); 
insert into t_operate_log values(5, 'add', to_date('2021-01-16','yyyy-MM-dd')); 
insert into t_operate_log values(6, 'delete', to_date('2021-01-16','yyyy-MM-dd')); 
insert into t_operate_log values(7, 'edit', to_date('2021-01-16','yyyy-MM-dd')); 
insert into t_operate_log values(8, 'query', to_date('2021-01-16','yyyy-MM-dd')); 

然后我用下面的sql可以查出每张子分区表有1条数据：

select * from t_operate_log SUBPARTITION(t_operate_log_add_1); 
select * from t_operate_log SUBPARTITION(t_operate_log_delete_1); 
select * from t_operate_log SUBPARTITION(t_operate_log_edit_1); 
select * from t_operate_log SUBPARTITION(t_operate_log_query_1); 
select * from t_operate_log SUBPARTITION(t_operate_log_add_2); 
select * from t_operate_log SUBPARTITION(t_operate_log_delete_2); 
select * from t_operate_log SUBPARTITION(t_operate_log_edit_2); 
select * from t_operate_log SUBPARTITION(t_operate_log_query_2); 

注意：我们可以添加子分区，比如我们给t_operate_log_time_1这个分区添加一个子分区，列表分区的type字段值是'select',sql如下：

ALTER TABLE t_operate_log MODIFY PARTITION t_operate_log_time_1 ADD SUBPARTITION t_operate_log_select_1 values('select');   

我们也可以删除子分区，比如删除type是'select'的这个分区，sql如下：

ALTER TABLE t_operate_log DROP SUBPARTITION t_operate_log_select_1; 

范围和HASH组合分区

如果范围列表组合分区导致分区表数据不太均衡时，我们可以考虑范围分区和HASH分区来组合使用，看如下的建表sql:

create table t_operate_log 
( 
  id   number(7), 
  type varchar2(10), 
  op_time date 
) 
PARTITION BY RANGE(op_time) SUBPARTITION BY HASH (id)   
(   
   PARTITION t_operate_log_time_1 VALUES LESS THAN(to_date('2021-01-16','yyyy-MM-dd')) 
  (        
     SUBPARTITION t_operate_log_1, 
     SUBPARTITION t_operate_log_2, 
     SUBPARTITION t_operate_log_3, 
     SUBPARTITION t_operate_log_4 
  ),    
   PARTITION t_operate_log_time_2 VALUES LESS THAN (to_date('2021-01-17','yyyy-MM-dd')) 
  (    
     SUBPARTITION t_operate_log_5, 
     SUBPARTITION t_operate_log_6, 
     SUBPARTITION t_operate_log_7, 
     SUBPARTITION t_operate_log_8 
  )    
) 

我们用下面的sql插入8条记录：

insert into t_operate_log values(1, 'add', to_date('2021-01-15','yyyy-MM-dd')); 
insert into t_operate_log values(2, 'delete', to_date('2021-01-15','yyyy-MM-dd')); 
insert into t_operate_log values(3, 'edit', to_date('2021-01-15','yyyy-MM-dd')); 
insert into t_operate_log values(4, 'query', to_date('2021-01-15','yyyy-MM-dd')); 
insert into t_operate_log values(5, 'add', to_date('2021-01-16','yyyy-MM-dd')); 
insert into t_operate_log values(6, 'delete', to_date('2021-01-16','yyyy-MM-dd')); 
insert into t_operate_log values(7, 'edit', to_date('2021-01-16','yyyy-MM-dd')); 
insert into t_operate_log values(8, 'query', to_date('2021-01-16','yyyy-MM-dd')); 

我们分别查询这8张表的记录时，发现并不是每张表中1条数据，这是使用了HASH分区的原因。

列表和HASH组合分区

我们也可以使用列表和HASH做组合进行分区，建表sql如下：

create table t_operate_log 
( 
  id   number(7), 
  type varchar2(10), 
  op_time date 
) 
PARTITION BY list(type) SUBPARTITION BY HASH (id)   
(   
   PARTITION t_operate_log_type_add VALUES('add') 
  (        
     SUBPARTITION t_operate_log_add_1, 
     SUBPARTITION t_operate_log_add_2 
  ),    
   PARTITION t_operate_log_type_delete VALUES('delete') 
  (    
     SUBPARTITION t_operate_log_delete_1, 
     SUBPARTITION t_operate_log_delete_2 
  ) ,   
   PARTITION t_operate_log_type_edit VALUES('edit') 
  (        
     SUBPARTITION t_operate_log_edit_1, 
     SUBPARTITION t_operate_log_edit_2 
  ),    
   PARTITION t_operate_log_type_query VALUES('query') 
  (    
     SUBPARTITION t_operate_log_query_1, 
     SUBPARTITION t_operate_log_query_2 
  ) 
) 

注意事项

1.创建分区表时可以选择TABLESPACE，比如下面的sql：

create table t_operate_log 
( 
  id   number(7), 
  type varchar2(10), 
  op_time date 
) 
PARTITION BY list(type) 
( 
 partition t_operate_log_add_del values('add','delete') TABLESPACE LOG, 
 partition t_operate_log_edit_query values('edit','query') TABLESPACE LOG 
) 

2.使用下面sql可以查看分区列表和子分区列表，TABLE_NAME要大写：

SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='T_OPERATE_LOG'; 
SELECT * FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME='T_OPERATE_LOG'; 

3.如果表中只有一个分区，那是不能删除分区的，删除的时候报ORA-14083:无法删除分区表的唯一分区

4.除了添加和删除，分区还支持合并、拆分、重命名等多个操作，感兴趣的可以自己研究。

分区索引创建

Oracle分区索引有2种，local索引和global索引。

首先我要解释几个概念，从官网翻译：

Local - All index entries in a single partition will correspond to a single table partition (equipartitioned). They are created with the LOCAL keyword and support partition independance. Equipartioning allows oracle to be more efficient whilst devising query plans. 
#本地索引 - 单个分区中的所有索引项只对应于单个表分区。使用LOCAL关键字创建，并且支持分区独立性。本地索引使oracle在设计查询计划时更加高效。 
Global - Index in a single partition may correspond to multiple table partitions. They are created with the GLOBAL keyword and do not support partition independance. Global indexes can only be range partitioned and may be partitioned in such a fashion that they look equipartitioned, but Oracle will not take advantage of this structure. 
#全局索引 - 单个分区中的索引对应于多个表分区。使用GLOBAL关键字创建，不支持分区独立性。全局索引只支持范围分区，或者分区方式看起来是均衡的，但Oracle不会利用这种结构。 

Prefixed - The partition key is the leftmost column(s) of the index. Probing this type of index is less costly. If a query specifies the partition key in the where clause partition pruning is possible, that is, not all partitions will be searched. 
#前缀索引 - 分区关键字在索引字段的左边。检测这种索引比较容易，如果查询的where条件中包含了分区键，就会消除掉不必要的分区，不会扫描所有分区表了。 
Non-Prefixed - Does not support partition pruning, but is effective in accessing data that spans multiple partitions. Often used for indexing a column that is not the tables partition key, when you would like the index to be partitioned on the same key as the underlying table. 
#分前缀索引 - 不支持分区消除，但在跨分区查找数据时很有效。通常用于创建的索引不是分区键，而这个索引想用在所有子表的情况。 

下面我们先来创建local索引，下面的sql我先创建一个范围分区，然后创建了一个本地索引：

create table t_operate_log 
( 
  id   number(7), 
  type varchar(1), 
  op_time date 
) 
PARTITION BY RANGE(id) 
( 
 partition t_operate_log_1 values less than (100), 
 partition t_operate_log_2 values less than (200), 
 partition t_operate_log_3 values less than (300) 
) 
#创建本地索引 
create index index_t_operate_log on t_operate_log(type) local 
( 
 partition t_operate_log_1, 
 partition t_operate_log_2, 
 partition t_operate_log_3 
) 

这里有几点说明：

• local索引是针对单个分区表的索引，无论是普通索引还是唯一索引，这个索引只对单个分区表有效。
• 创建local索引，可以不加括号后面的语句，但是如果加了必须选择所有分区表，否则会报“ORA-14024:LOCAL索引的分区数必须等于基础表的分区数”。

下面我们创建一个global索引，sql如下：

CREATE INDEX index_t_operate_log ON t_operate_log (type) GLOBAL  

注意:oracle不支持全局非前缀索引。

对已经存在的表分区

1.创建表

create table t_operate_log 
( 
  id   number(7), 
  type varchar(1), 
  op_time date 
) 

创建完成后插入100条数据，id为1~100。

2.创建一个分区表，只有1个分区

create table t_operate_log_p 
 
( 
 
id number(7), 
 
type varchar(1), 
 
op_time date 
 
) 
 
PARTITION BY RANGE(id) 
 
( 
 
partition t_operate_log_1 values less than (101) 
 
) 

3.把原表数据抽取到分区表

ALTER TABLE t_operate_log_p 
  EXCHANGE PARTITION t_operate_log_1 
  WITH TABLE t_operate_log 
  WITHOUT VALIDATION; 

这时我们查看t_operate_log_p这个表有100条数据。

4.删除原有表，把新的表改名为原来的表

DROP TABLE t_operate_log; 
RENAME t_operate_log_p TO t_operate_log; 

5.把新表拆分成多个分区表

ALTER TABLE t_operate_log SPLIT PARTITION t_operate_log_1 AT (id) 
INTO (PARTITION t_operate_log_2, 
      PARTITION t_operate_log_3); 

6.验证

使用下面sql我们可以看到有2个分区:

SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='T_OPERATE_LOG';

使用下面sql我们可以看到，第一个分区有49条，第二个分区有51条，可见我们分区用的id=50的记录分到了第二个分区表：

select * from t_operate_log PARTITION(t_operate_log_2);

select * from t_operate_log PARTITION(t_operate_log_3);

参考链接：

https://oracle-base.com/articles/8i/partitioned-tables-and-indexes