聊聊怎样使用Oracle分区表

单表的数据量如果太大，会影响到读写性能。我们可以使用分库分表来解决单表的性能问题。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如下：

createtablet_operate_log
(
idnumber(7),
typevarchar(1),
op_timedate
)
PARTITIONBYRANGE(id)
(
partitiont_operate_log_1valueslessthan(100),
partitiont_operate_log_2valueslessthan(200),
partitiont_operate_log_3valueslessthan(300)
)

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

insertintot_operate_logvalues(1,'1',sysdate);
insertintot_operate_logvalues(100,'1',sysdate);
insertintot_operate_logvalues(200,'1',sysdate);

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

select*fromt_operate_logpartition(t_operate_log_1);
select*fromt_operate_logpartition(t_operate_log_2);
select*fromt_operate_logpartition(t_operate_log_3);

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

select*fromt_operate_log

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

ALTERTABLEt_operate_logADDPARTITIONt_operate_log_4VALUESLESSTHAN(400);

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

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

ALTERTABLEt_operate_logDROPPARTITIONt_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如下：

createtablet_operate_log
(
idnumber(7),
typevarchar(1),
op_timedate
)
PARTITIONBYRANGE(op_time)
(
partitiont_operate_log_1valueslessthan(to_date('2019-01-17','yyyy-MM-dd')),
partitiont_operate_log_2valueslessthan(to_date('2020-01-17','yyyy-MM-dd')),
partitiont_operate_log_3valueslessthan(maxvalue)
)

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

insertintot_operate_logvalues(1,'1',to_date('2019-01-16','yyyy-MM-dd'));
insertintot_operate_logvalues(2,'1',to_date('2019-01-17','yyyy-MM-dd'));
insertintot_operate_logvalues(3,'1',to_date('2020-01-17','yyyy-MM-dd'));

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

select*fromt_operate_logpartition(t_operate_log_1);
select*fromt_operate_logpartition(t_operate_log_2);
select*fromt_operate_logpartition(t_operate_log_3);

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

select*fromt_operate_log

列表分区

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

createtablet_operate_log
(
idnumber(7),
typevarchar2(10),
op_timedate
)
PARTITIONBYlist(type)
(
partitiont_operate_log_addvalues('add'),
partitiont_operate_log_deletevalues('delete'),
partitiont_operate_log_editvalues('edit'),
partitiont_operate_log_queryvalues('query')
)

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

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

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

select*fromt_operate_logpartition(t_operate_log_add);
select*fromt_operate_logpartition(t_operate_log_delete);
select*fromt_operate_logpartition(t_operate_log_edit);
select*fromt_operate_logpartition(t_operate_log_query);

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

ALTERTABLEt_operate_logMODIFYPARTITIONt_operate_log_queryADDVALUES('select');

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

ALTERTABLEt_operate_logMODIFYPARTITIONt_operate_log_queryDROPVALUES('select');

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

createtablet_operate_log
(
idnumber(7),
typevarchar2(10),
op_timedate
)
PARTITIONBYlist(type)
(
partitiont_operate_log_add_delvalues('add','delete'),
partitiont_operate_log_edit_queryvalues('edit','query')
)

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

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

HASH分区

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

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

createtablet_operate_log
(
idnumber(7),
typevarchar2(10),
op_timedate
)
PARTITIONBYhash(id)
(
partitiont_operate_log_1,
partitiont_operate_log_2,
partitiont_operate_log_3,
partitiont_operate_log_4
)

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

使用HASH分区有2个建议：

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

注意：

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

ALTERTABLEt_operate_logADDPARTITIONt_operate_log_5;

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

范围列表组合分区

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

createtablet_operate_log
(
idnumber(7),
typevarchar2(10),
op_timedate
)
PARTITIONBYRANGE(op_time)SUBPARTITIONBYLIST(type)
(
PARTITIONt_operate_log_time_1VALUESLESSTHAN(to_date('2021-01-16','yyyy-MM-dd'))
(
SUBPARTITIONt_operate_log_add_1values('add'),
SUBPARTITIONt_operate_log_delete_1values('delete'),
SUBPARTITIONt_operate_log_edit_1values('edit'),
SUBPARTITIONt_operate_log_query_1values('query')
),
PARTITIONt_operate_log_time_2VALUESLESSTHAN(to_date('2021-01-17','yyyy-MM-dd'))
(
SUBPARTITIONt_operate_log_add_2values('add'),
SUBPARTITIONt_operate_log_delete_2values('delete'),
SUBPARTITIONt_operate_log_edit_2values('edit'),
SUBPARTITIONt_operate_log_query_2values('query')
)
)

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

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

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

select*fromt_operate_logSUBPARTITION(t_operate_log_add_1);
select*fromt_operate_logSUBPARTITION(t_operate_log_delete_1);
select*fromt_operate_logSUBPARTITION(t_operate_log_edit_1);
select*fromt_operate_logSUBPARTITION(t_operate_log_query_1);
select*fromt_operate_logSUBPARTITION(t_operate_log_add_2);
select*fromt_operate_logSUBPARTITION(t_operate_log_delete_2);
select*fromt_operate_logSUBPARTITION(t_operate_log_edit_2);
select*fromt_operate_logSUBPARTITION(t_operate_log_query_2);

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

ALTERTABLEt_operate_logMODIFYPARTITIONt_operate_log_time_1ADDSUBPARTITIONt_operate_log_select_1values('select');

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

ALTERTABLEt_operate_logDROPSUBPARTITIONt_operate_log_select_1;

范围和HASH组合分区

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

createtablet_operate_log
(
idnumber(7),
typevarchar2(10),
op_timedate
)
PARTITIONBYRANGE(op_time)SUBPARTITIONBYHASH(id)
(
PARTITIONt_operate_log_time_1VALUESLESSTHAN(to_date('2021-01-16','yyyy-MM-dd'))
(
SUBPARTITIONt_operate_log_1,
SUBPARTITIONt_operate_log_2,
SUBPARTITIONt_operate_log_3,
SUBPARTITIONt_operate_log_4
),
PARTITIONt_operate_log_time_2VALUESLESSTHAN(to_date('2021-01-17','yyyy-MM-dd'))
(
SUBPARTITIONt_operate_log_5,
SUBPARTITIONt_operate_log_6,
SUBPARTITIONt_operate_log_7,
SUBPARTITIONt_operate_log_8
)
)

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

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

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

列表和HASH组合分区

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

createtablet_operate_log
(
idnumber(7),
typevarchar2(10),
op_timedate
)
PARTITIONBYlist(type)SUBPARTITIONBYHASH(id)
(
PARTITIONt_operate_log_type_addVALUES('add')
(
SUBPARTITIONt_operate_log_add_1,
SUBPARTITIONt_operate_log_add_2
),
PARTITIONt_operate_log_type_deleteVALUES('delete')
(
SUBPARTITIONt_operate_log_delete_1,
SUBPARTITIONt_operate_log_delete_2
),
PARTITIONt_operate_log_type_editVALUES('edit')
(
SUBPARTITIONt_operate_log_edit_1,
SUBPARTITIONt_operate_log_edit_2
),
PARTITIONt_operate_log_type_queryVALUES('query')
(
SUBPARTITIONt_operate_log_query_1,
SUBPARTITIONt_operate_log_query_2
)
)

注意事项

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

createtablet_operate_log
(
idnumber(7),
typevarchar2(10),
op_timedate
)
PARTITIONBYlist(type)
(
partitiont_operate_log_add_delvalues('add','delete')TABLESPACELOG,
partitiont_operate_log_edit_queryvalues('edit','query')TABLESPACELOG
)

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

SELECT*FROMUSER_TAB_PARTITIONSWHERETABLE_NAME='T_OPERATE_LOG';
SELECT*FROMUSER_TAB_SUBPARTITIONSWHERETABLE_NAME='T_OPERATE_LOG';

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

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

分区索引创建

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

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

Local-Allindexentriesinasinglepartitionwillcorrespondtoasingletablepartition(equipartitioned).TheyarecreatedwiththeLOCALkeywordandsupportpartitionindependance.Equipartioningallowsoracletobemoreefficientwhilstdevisingqueryplans.
#本地索引-单个分区中的所有索引项只对应于单个表分区。使用LOCAL关键字创建，并且支持分区独立性。本地索引使oracle在设计查询计划时更加高效。
Global-Indexinasinglepartitionmaycorrespondtomultipletablepartitions.TheyarecreatedwiththeGLOBALkeywordanddonotsupportpartitionindependance.Globalindexescanonlyberangepartitionedandmaybepartitionedinsuchafashionthattheylookequipartitioned,butOraclewillnottakeadvantageofthisstructure.
#全局索引-单个分区中的索引对应于多个表分区。使用GLOBAL关键字创建，不支持分区独立性。全局索引只支持范围分区，或者分区方式看起来是均衡的，但Oracle不会利用这种结构。

Prefixed-Thepartitionkeyistheleftmostcolumn(s)oftheindex.Probingthistypeofindexislesscostly.Ifaqueryspecifiesthepartitionkeyinthewhereclausepartitionpruningispossible,thatis,notallpartitionswillbesearched.
#前缀索引-分区关键字在索引字段的左边。检测这种索引比较容易，如果查询的where条件中包含了分区键，就会消除掉不必要的分区，不会扫描所有分区表了。
Non-Prefixed-Doesnotsupportpartitionpruning,butiseffectiveinaccessingdatathatspansmultiplepartitions.Oftenusedforindexingacolumnthatisnotthetablespartitionkey,whenyouwouldliketheindextobepartitionedonthesamekeyastheunderlyingtable.
#分前缀索引-不支持分区消除，但在跨分区查找数据时很有效。通常用于创建的索引不是分区键，而这个索引想用在所有子表的情况。

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

createtablet_operate_log
(
idnumber(7),
typevarchar(1),
op_timedate
)
PARTITIONBYRANGE(id)
(
partitiont_operate_log_1valueslessthan(100),
partitiont_operate_log_2valueslessthan(200),
partitiont_operate_log_3valueslessthan(300)
)
#创建本地索引
createindexindex_t_operate_logont_operate_log(type)local
(
partitiont_operate_log_1,
partitiont_operate_log_2,
partitiont_operate_log_3
)

这里有几点说明：

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

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

CREATEINDEXindex_t_operate_logONt_operate_log(type)GLOBAL

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

对已经存在的表分区

1.创建表

createtablet_operate_log
(
idnumber(7),
typevarchar(1),
op_timedate
)

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

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

createtablet_operate_log_p
(
idnumber(7),
typevarchar(1),
op_timedate
)
PARTITIONBYRANGE(id)
(
partitiont_operate_log_1valueslessthan(101)
)

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

ALTERTABLEt_operate_log_p
EXCHANGEPARTITIONt_operate_log_1
WITHTABLEt_operate_log
WITHOUTVALIDATION;

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

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

DROPTABLEt_operate_log;
RENAMEt_operate_log_pTOt_operate_log;

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

ALTERTABLEt_operate_logSPLITPARTITIONt_operate_log_1AT(id)
INTO(PARTITIONt_operate_log_2,
PARTITIONt_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