聊聊Oracle分区的使用心得

Oracle数据库经常被认为是客户最想要抛弃的数据库之一。我总结了下，最主要的原因如下：收费、维护复杂、不适合网络和云端。能抛弃的早已抛弃，不能抛弃的，像一些银行、政府单位仍然在使用。相信大家在用的过程中都会碰到如下问题：

• 随着单表数据量增多，插入速度越来越慢

• 随着单表数据量增多，查询速度越来越慢

如上两种问题，解决简单方案就是删除或者备份数据，复杂解决方案就是分表、分库，如果业务代码比较老，分表成本无论对数据还是业务改动太大，有没有其它的解决方案？另外Oracle单张表到底能够容纳多少数据？具体答案，下文揭晓。

插入问题

多数人认为Oracle是关系型数据库，不适合做单表大数据量的插入。你去Oracle官方看下，它会告诉你，它不存在插入瓶颈，很多组织把它当成数据仓库使用。如果说插入速度不够快，一定是下面几个原因：索引太多、没使用批量或并行插入、机器性能问题。仔细想想也是啊，如果单表中存在索引，肯定需要按照索引进行排序，数据插入特定位置。不能直接顺序写入，速度自然上不去啊。

你最容易想到的方法，自然是删除单表索引，插入完成后，添加索引。但是这种方式其实挺沙雕的，我是没有想到适合什么应用场景，首先我添加插入数据就是为了查询，由于没有索引，大数据量查询速度慢或无法查询。在说了当你把所有数据插入到数据库之后，在进行索引构建，还是需要消耗一定时间。

分区，简单来说，是一种分而治之的方法，可以提高Oracle的维护和SQL的性能。分区方式一般有范围分区、hash分区、列表分区，这几种分区类型之间还能够相互组合，从而形成了子分区，比如：范围范围或者范围-hash。怎么使用呢？我举个例子你就理解了，比如我们手机上都有一个银行客户端，当我需要搜索我的银行转账信息时，它一般会要求你输入一个查询时间段，最近1个月、半年、一年等等，这种其实特别适合使用范围分区。

你可能会有疑问，分来分去，还在一张表中，能够提升插入速度吗？具体请看下图

分区在逻辑上还是一张表，但是物理已经划分到不同的空间，想象一下，你把一张表中的数据分散到多个物理空间中，当查询/插入定位某一块物理存储空间上时，速度肯定会有提升。

分区创建

创建代码如下是通过把不同时间段内的数据分散到不同的分区，你可能会有疑问我的使用场景没有时间，那你可以考虑hash或者列表，正常情况下，总有一种方式适合你，如果都没有，即使使用分表也不会太简单。这里也可以思考下，分区和分表的区别和优缺点分别是什么？

create table
pos_data (
   start_date        DATE,
   store_id          NUMBER,
   inventory_id      NUMBER(6),
   qty_sold          NUMBER(3)
)
PARTITION BY RANGE (start_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( 
   PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
   PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))
); 

看了这种方式后，你会有疑问，这特么一个月一个月的创建分区，谁来创建啊？其实Oracle11g之后提供了一种interval的方式，通过这种方式，你只要声明创建分区间隔，比如小时、天、月，他就能够自动依次创建分区，具体创建方式，我这里不在赘述。具体请参考官方文档：https://docs.oracle.com/cd/B28359_01/server.111/b32024/partition.htm#CACHFHHF

注意：一旦创建分区完成，如果想修改，基本上就是重新导入一次数据。所以创建分区之前一定要仔细规划和思考使用场景。

创建分区是挺简单的，但是我一个分区应该存放多少数据呢？这个其实真的没有特别好的办法能够自动计算出来，有时我就在想数据库系统能不能根据负载或者数据量大小，自动分裂分区表，但就目前而言，没有实现。仍需要开发人员结合自己的实际使用情况综合权衡。我这里可以给出一个我自己在7200转磁盘上测试出来的数据，单个分区总数据量最好不要超过1.5g，超过后插入速度会逐渐变慢，每次批量插入的数据可以控制在2000条(每条1k，每秒可以插入5000条)左右，具体可以根据机器性能测试调整。

另外关于interval自动分区，分区名称会递增自动命名，这就带来一个问题，我们无法预测分区的名称是什么？有些程序员习惯性认为必须对着分区名称查询或者插入才能提高速度，其实不然，只要我们where条件恰好卡在分区区间中，就可以保证定位到分区中查询。如果你想修改分区名称，只能自己通过触发器修改。

只要有大数据量都会有表空间满的问题，自动扩展存在上限，目前都是手动多创建几组，网上有人写的自动化创建触发器，我个人感觉不太优雅，不知道大家有特别好的办法没，如果有还请留言告知。手动修改方式可以参考：https://www.cnblogs.com/timlong/p/11075825.html

默认情况下，oracle的分区表对于分区字段是不允许进行update操作的，如果有对分区字段行进update，就会报错——ORA-14402: 更新分区关键字列将导致分区的更改。但是可以通过打开表的row movement属性来允许对分区字段的update操作。

redo log

数据插入或者查询过程中会把数据存放在redolog中，如果你导出awr报告（

sqlplus / as sysdba
@?/rdbms/admin/awrrpt.sql

），发现在切换redolog上花费很长时间，你可以考虑改大redolog，尽可能减少日志切换，具体多大，可以看下官方建议：下面我给出一个修改示例：

• 修改当前在线日志从默认50M增加至512M。

1.查看当前日志组的状态

SQL> select group#,members,bytes/1024/1024,status from v$log;

    GROUP#    MEMBERS BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------------
         1          1              50 INACTIVE
         2          1              50 CURRENT
         3          1              50 INACTIVE

2.查看在线日志组的位置

SQL> select MEMBER from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oradata/ARPDB/redo01.log
/u01/app/oradata/ARPDB/redo02.log

/u01/app/oradata/ARPDB/redo03.log

3.新增group 4-6日志组，大小为512M

alter database add logfile group 4 ('/u01/app/oradata/ARPDB/redo04.log') size 512M;
alter database add logfile group 5 ('/u01/app/oradata/ARPDB/redo05.log') size 512M;
alter database add logfile group 6 ('/u01/app/oradata/ARPDB/redo06.log') size 512M;

4.切换当前日志到新的日志组

alter system switch logfile;   
alter system switch logfile;

让在线日志组，变成inactive的方式，执行如下命令，有时可能切换速度比较慢，多等待和刷新执行几次即可解决：

alter system checkpoint;

5.删除旧的日志组

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

6.查看修改后的日志组的状态信息：

SQL> select group#,members,bytes/1024/1024,status from v$log;

    GROUP#    MEMBERS BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------------
         4          1             512 INACTIVE
         5          1             512 CURRENT
         6          1             512 UNUSED

查询问题

查询速度慢，正常情况下，首先想到的都是索引问题，因为使用了分区，我们应该使用本地索引，还是应该使用全局索引呢？就我目前的经验而言，只要你把Oracle当成数据仓库来使用，本地索引就是你的绝佳选择，一方面本地索引可用性更高，另一方面全局索引指向多个分区，一旦出现故障，所有分区都不可访问。

对于索引，可以通过SQL执行计划调整索引情况，不过这里我给出我的两点使用经验

如果你要获取表中的很少一部分数据（总数据的2%-3%）使用Btree索引，如果说你要处理表中大量的数据，但是这些数据可以直接通过索引获得，也就是我们说的覆盖索引，也要用Btree索引。（Oracle数据行本身是块状存储的，有可能查询的数量比块本身还要多，从而造成走索引还不如全表扫描速度快）

用过Oracle的同学都知道，Oracle中有位图索引的概念，但什么时候应该使用位图索引呢？用专业的术语来说就是相异基数低的数据适合用位图索引。举个例子你有10000个人员信息，里面包含男女这两个类型，2/10000=0.0002，这就是一个很小的值，适合使用位图索引，一般情况下，这个值小于1%的都可以根据实际情况使用位图索引。

有时添加了索引，而没有按照索引执行，可能是因为Oracle内部进行了调整和优化，可以通过添加Oracle hint纠正Oracle优化器执行计划。

最后一点，所谓任何不结合实际技术的需求实现，都是在耍流氓。简单来说，你已经使用了Oracle分区，那么你在做查询业务的时候，尽可能引导用户定位到其中一个分区中，可以通过分区时间或者类型等分区键定位。否则的话不能带来实际上的性能提升，比如，你非要进行全表扫面，查询下总数，非要跨分区查询部分数据，效率肯定不高。所以你可以考虑从用户使用者的角度规避这些动作或者后台粗略统计。你看看谷歌和百度等搜索引擎，也不会准确告诉你总共有多少页相关数据。

IO 问题

既然说到大数据写入，就很难避免高IO占用问题，这该如何解决呢？为了说清楚这个问题，我引用了一位大佬的漫画，如下所示：

所有的数据都写入到一个分区，对于其中的一个分区产生了热点，难以应对，好痛苦，分区不得不分割成两个分区。

两个分区分别处理不同的数据写入，各司其职，其乐融融，好轻松。

当我们开始保存像时间戳这样单调递增的值，或者按照字母顺序插入字典单词时，问题就出现了，新产生数据分布不均匀，产生了倾斜，所有数据都跑到了一个分区，又回到了难以应对，好痛苦。

作为一个开发者，你怎么解决这种高IO占用问题呢？

• 尽量避免使用索引，除非需要对值进行查询。无索引就等价于无热点值。

• 降低您的写速率，或者找出如何更好地随机值。一个纯粹的随机分布是最好的，但即使一个不是随机的分布也比一个可预测的、单调递增的值要好

• 为您的值添加一个分片标识符前缀。如果您计划执行查询，这是有问题的，因为您需要为值添加前缀和取消前缀业务逻辑，这会增加写入和查询的复杂度，但是这会降低磁盘整体压力，提高写入速度和成功率。

• 硬件层面做Raid或者换成闪存，提高硬盘IO读写性能。

• 读写分离。

• Oracle分区本身支持数据压缩方案，可以在建表时声明，会消耗一定cpu，但能够提高查询和存储速度。

以上都是冠冕堂皇的废话，对你的磁盘IO降低不会带来本质上的帮助，具体做的时候，你要结合实际情况去考虑和制定可行性方案。

总结

本文主要从读写、索引、性能优化等几个方面讨论了使用Oracle分区的方法及误区，有说的不对的地方，还请指正！另外还请关注公众号，加我微信，我拉你进群讨论！

推荐

---

详解Kubernetes存储体系

Kubernetes入门培训（内含PPT）

原创不易，随手关注或者”在看“，诚挚感谢！