
怎样使用oracle分区表
单表的数据量如果太大,会影响到读写性能。我们可以使用分库分表来解决单表的性能问题。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如下:
创建表成功后,我们插入3条数据,id分别是1,100,200,sql如下:
下面这3条sql分别可以查到id是1,100,200的这3条记录:
下面这个sql可以查到t_operate_log中所有记录:
注意:我们可以用下面命令再添加一个分区:
但是要注意一点,如果一个分区的范围是maxvalue(比如把300替换成maxvalue),添加分区会失败。
我们也可以用下面命令删除一个分区:
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如下:
创建表成功后,我们插入3条数据,id分别是1,2,3,时间分别是2019-01-16、2019-01-17、2020-01-17:
下面这3条sql分别可以查到id是1,2,3的这3条记录:
下面这个sql可以查到t_operate_log中所有记录:
列表分区
列表分区的使用场景是表中的某一列只有固定几个值,比如上面的操作日志表,假如我们的type有4个类型:add、edit、delete、query,我们建立分区表如下:
创建表成功后,我们插入4条数据,type分别为add,delete,edit,query
下面的4条sql分别可以查出每一种type类型对应的数据:
注意:我们可以给列表分区增加元素,比如我们在t_operate_log_query这个分区表中增加一个元素"select",sql如下:
也可以给列表分区删除元素,比如我们在t_operate_log_query这个分区表中删除元素"select",sql如下:
如果每个分区的数据量不大,没有必须做太多分区,我们创建分区时可以减少数量,如下创建分区的sql我们把t_operate_log创建成了2个分区:
创建成功后我们还是使用上面的insert语句插入4条数据,成功后我们用下面的sql查询,分别可以查询出2条数据:
HASH分区
范围分区和列表分区都使用了某一个字段来做分区键,使用这个字段的值作为分区的依据,使用简单。但是有一个问题就是分区键的区分度要大,不然容易存在分区数据量严重不均匀的情况。
如果没有一个合适的分区键,使用HASH分区就是一个很好的选择,HASH分区的好处是可以让分区表数据分布均匀。我们还是以上面的表为例,我们使用HASH分区来创建4个分区表,sql如下:
创建成功后我们插入100条数据,id是从1~100,我们分别查询4个分区表的数据,数据条数分别是20、28、25、27。
使用HASH分区有2个建议:
- 分区键的值最好是连续的.
- 分区数量最好是2的n次方,这样可以对hash运算更加友好(想想java中HashMap构造函数的initialCapacity参数).
注意:
HASH分区支持添加操作,比如我们添加一个分区,sql如下:
创建后我们查询t_operate_log_5这张表,发现也有数据,这是因为添加或删除分区时,所有数据会重新计算HASH值,重新分配到不同的分区表中。
HASH分区是不能删除的,删除会报“ORA-14255:不能对范围、列表,组合范围或组合列表方式对表进行分区”
范围列表组合分区
在一些复杂的场景下,我们可以使用范围和列表组合分区来进行分区,比如在前面讲范围分区和列表分区的例子,我们做一个范围列表组合分区的改进,sql如下:
上面我按照op_time做了分区,然后按照type做了子分区,这是我插入8条数据,每张子分区表一条,sql如下:
然后我用下面的sql可以查出每张子分区表有1条数据:
注意:我们可以添加子分区,比如我们给t_operate_log_time_1这个分区添加一个子分区,列表分区的type字段值是'select',sql如下:
我们也可以删除子分区,比如删除type是'select'的这个分区,sql如下:
范围和HASH组合分区
如果范围列表组合分区导致分区表数据不太均衡时,我们可以考虑范围分区和HASH分区来组合使用,看如下的建表sql:
我们用下面的sql插入8条记录:
我们分别查询这8张表的记录时,发现并不是每张表中1条数据,这是使用了HASH分区的原因。
列表和HASH组合分区
我们也可以使用列表和HASH做组合进行分区,建表sql如下:
注意事项
1.创建分区表时可以选择TABLESPACE,比如下面的sql:
2.使用下面sql可以查看分区列表和子分区列表,TABLE_NAME要大写:
3.如果表中只有一个分区,那是不能删除分区的,删除的时候报ORA-14083:无法删除分区表的唯一分区
4.除了添加和删除,分区还支持合并、拆分、重命名等多个操作,感兴趣的可以自己研究。
分区索引创建
Oracle分区索引有2种,local索引和global索引。
首先我要解释几个概念,从官网翻译:
下面我们先来创建local索引,下面的sql我先创建一个范围分区,然后创建了一个本地索引:
这里有几点说明:
- local索引是针对单个分区表的索引,无论是普通索引还是唯一索引,这个索引只对单个分区表有效。
- 创建local索引,可以不加括号后面的语句,但是如果加了必须选择所有分区表,否则会报“ORA-14024:LOCAL索引的分区数必须等于基础表的分区数”。
下面我们创建一个global索引,sql如下:
注意:oracle不支持全局非前缀索引。
对已经存在的表分区
1.创建表
创建完成后插入100条数据,id为1~100。
2.创建一个分区表,只有1个分区
3.把原表数据抽取到分区表
这时我们查看t_operate_log_p这个表有100条数据。
4.删除原有表,把新的表改名为原来的表
5.把新表拆分成多个分区表
6.验证
使用下面sql我们可以看到有2个分区:
使用下面sql我们可以看到,第一个分区有49条,第二个分区有51条,可见我们分区用的id=50的记录分到了第二个分区表:
参考链接:
https://oracle-base.com/articles/8i/partitioned-tables-and-indexes.
本文转载自微信公众号「君哥聊技术」
原文链接:https://mp.weixin.qq.com/s/Pf6g-oAiumyMlBTZ6zW__Q.
