oracle分区表之hash分区表的使用及扩展

原作者: [db:作者] 来自: [db:来源] 收藏邀请

Hash分区是通过对分区键运用Hash算法从而决定数据的分区归属。使用Hash分区有什么优点呢？

常用的分区表所具有的优点：如提高数据可用行，减少管理负担，改善语句性能等优点，hash分区同样拥有。此外，由于Hash分区表是按分区键的hash计算结果来决定其分区的，而特定的分区键其hash值是固定的，也就是说Hash分区表的数据是按分区键值来聚集的，同样的分区键肯定在同一分区。
比如，在证券行业，我们经常查询某一只股票的K线，
假设表的结构如下：

复制代码代码如下:

create table equity
(
id number,
trade_date date,
……);

Equity表可能会很大，对equity表的查询通常都是指定id,查询某一交易日期或者某段时期内的其他信息。这种情况下我们需要如何为equity表选择分区呢？
单从表本身结构来看，似乎trade_date列很适合被选择用来作范围分区。但如果我们这样分区的话，前面需求中的查询：指定某一id,查询其某一范围内的交易信息，比如看1年内的K线，则这种查询常常需要跨分区。我们知道，对分区表作跨分区查询，很多时候其性能并不会太好，特别是这种查询很可能还要跨很多分区。
你也可能会说，我们再在id, trade_date列上建个索引不就行了，仔细想想是不是这样呢？这时候的equity表中的数据是按trade_date值来聚集的，同样trade_date值的数据常常在一个数据块中，这样前面需求中所描述的查询即使通过索引访问，最终读表时也常常是去读离散的数据块，即每一条记录需要对应读一个表数据块。
如果建成Hash分区表，则数据按hash分区键聚集，就更适合需求中描述的查询，因为同样id的记录必定在同一分区，同时，同样 id值的记录落在同一数据块的几率也增大了，从而“一定程度上”减少了IO。
上面对hash分区减少IO的描述加了引号，因为仅依靠Hash分区表试图实现大范围减少IO操作是不现实的，特别是当equity表中记录的股票数非常多时，同一股票发生在不同交易日的记录在物理上也很难聚集到相同数据块中。实际上，如果我们在Hash分区的基础上再对equity表采用IOT表的组织方式，则前面描述的查询性能就可大为提高。IOT表不在该文讨论的范围之内，这里就不作进一步讨论了。
当我们决定使用Hash表之前，我们还需要确定我们的所选择的分区键值是连续分布的，或者接近连续分区，此外，分区的个数需要是2的整数幂，比如2,4,8… 这些要求是由Hash函数的特点决定的，这样我们分区表的各个分区所包含的数据量才会比较平均。

Hash分区表的扩展：

Hash分区表是通过add partition命令来增加分区的。Oracle推荐分区的个数是2的幂，比如,2,4,8..等等，这样可以确保数据在各个分区中分布比较均匀。当然，如前所述，还需要分区键值是连续分布的，或接近连续分布。
增加新分区时，需要将一些原有的数据从旧的分区划分到新的分区中，那么这种数据划分时来源分区选择遵循什么原则呢？
要点如下：如果要增加的分区是第N个分区，大于等于N的最小2的整数幂为M，则当增加第N个分区时，这个分区的数据来源于分区N-M/2。
比如，现在有个Hash分区表共有100个分区，我们想为其增加一个分区，则它是101个分区，即上面公式中的N为101，而大于101的最小2的整数幂为128，则M为128，于是，这个101分区的数据来源就应该是101-128/2=37分区。
换个角度来说，当我们在增加第101分区的时候，是需要锁定37分区的，因为我们需要将该分区中的部分数据插入到新的101分区中。
下面，我们用一个实例来验证上面的说法，同时看看在实际操作中有什么需要注意的事项：
Commodity表是我们系统中的一个大表，几年前在为该表创建Hash分区表时，当时的DBA在选择分区数时指定了100个分区：

复制代码代码如下:

select TABLE_NAME,PARTITION_POSITION,PARTITION_NAME,NUM_ROWS from user_tab_partitions where table_name=\'COMMODITY\' order by PARTITION_POSITION;
TABLE_NAME PARTITION_POSITION PARTITION_NAME NUM_ROWS
-------------- ------------------ ---------------------- ----------
COMMODITY 1 COT_IND01_P1 4405650
COMMODITY 2 COT_IND01_P2 5046650
COMMODITY 3 COT_IND01_P3 5107550
……
COMMODITY 36 COT_IND01_P36 5718800
COMMODITY 37 COT_IND01_P37 9905200
COMMODITY 38 COT_IND01_P38 10118400
COMMODITY 39 COT_IND01_P39 10404950
COMMODITY 40 COT_IND01_P40 9730850
COMMODITY 41 COT_IND01_P41 9457300
COMMODITY 42 COT_IND01_P42 9717950
COMMODITY 43 COT_IND01_P43 9643900
COMMODITY 44 COT_IND01_P44 11138000
COMMODITY 45 COT_IND01_P45 9381300
COMMODITY 46 COT_IND01_P46 10101150
COMMODITY 47 COT_IND01_P47 8809950
COMMODITY 48 COT_IND01_P48 10611050
COMMODITY 49 COT_IND01_P49 10010600
COMMODITY 50 COT_IND01_P50 8252600
COMMODITY 51 COT_IND01_P51 9709900
COMMODITY 52 COT_IND01_P52 8983200
COMMODITY 53 COT_IND01_P53 9012750
COMMODITY 54 COT_IND01_P54 9310650
COMMODITY 55 COT_IND01_P55 8966450
COMMODITY 56 COT_IND01_P56 8832650
COMMODITY 57 COT_IND01_P57 9470600
COMMODITY 58 COT_IND01_P58 8932450
COMMODITY 59 COT_IND01_P59 9994850
COMMODITY 60 COT_IND01_P60 9617450
COMMODITY 61 COT_IND01_P61 10278850
COMMODITY 62 COT_IND01_P62 9277600
COMMODITY 63 COT_IND01_P63 8136300
COMMODITY 64 COT_IND01_P64 10064600
COMMODITY 65 COT_IND01_P65 3710900
……
COMMODITY 99 COT_IND01_P99 5273800
COMMODITY 100 COT_IND01_P100 5293350
100 rows selected.

查询各个分区的数据分布，我们可以看到，从分区37 ~ 64的28个分区的记录数大概是其他分区的两倍。由于100不是2的整数幂，所以Oracle的hash函数是无法保证数据是平均分布的。我们为该表添加一个新的分区COT_IND01_P101:

复制代码代码如下:

alter table nts_commodity_ts add partition COT_IND01_P101;
Table altered.
Elapsed: 00:06:58.52

收集统计信息后查询新的分区记录数：

复制代码代码如下:

select TABLE_NAME,PARTITION_POSITION,PARTITION_NAME,NUM_ROWS from user_tab_partitions where table_name=\'COMMODITY\' and partition_name in (\'COT_IOT_IND01_P37\',\'COT_IOT_IND01_P101\');

TABLE_NAME PARTITION_POSITION PARTITION_NAME NUM_ROWS
------------------ ------------------ --------------------- ----------
COMMODITY 37 COT__IND01_P37 4905200
COMMODITY 101 COT_IND01_P101 5107550

这时，我们可以看到，分区37中的数据被接近于平分到了分区37和101中。
监控增加分区过程中session锁的情况，我们发现期间有两个对象被以exclusive模式锁定了：

复制代码代码如下:

SQL> select * from v$lock where sid=1239 and type=\'TM\' and LMODE=6 order by sid,lmode;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
FFFFFFFF7D764828 FFFFFFFF7D764888 1239 TM 4004126 0 6 0 72 2
FFFFFFFF7D764828 FFFFFFFF7D764888 1239 TM 4004063 0 6 0 72 2
它们分别是什么对象呢？
select OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID from user_objects where object_id in (4004126,4004063)
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID
--------------------- ------------------------------ ----------
COMMODITY COT_IND01_P100 4004126
COMMODITY COT_IND01_P37 4004063

可以看到，分区37和100都被锁定了。锁定37分区是意料中的事，因为要从该表转移数据。那为什么要锁定第100个分区，也就是最后一个分区呢？
我的理解是：新增加分区的位置101是由原分区表的分区数100确定的，如果在增加分区的过程中允许对原表最后一个分区100作DDL操作，如coalesce操作，则新加的101分区就不一定是从原来的分区37分配数据了，101分区本身应该是新的第100分区，这样就引起混乱了。到这里，你可能会说，按这理解，是不是其他的分区也应该锁定呢？其实不用，因为hash分区表是不支持drop partition操作的，而只支持coalesce操作来实现类似的操作，但coalesce只能从最后一个分区开始收缩。
了解了增加hash表分区过程中锁信息的实际指导意义是什么呢？
继续上例中的讨论，由于分区37和最后一个分区100会被排他锁定，因此在添加分区过程中这两个分区是不能作DML操作的，因为DML操作需要在分区上申请共享锁(mode为3)。也就是操作这两个分区的应用会受到影响。
Hash表增加分区不会像其他类型分区表，如range分区那样能够迅速完成，因为这里添加分区的过程中是要有IO操作的，要转移数据到新的分区。其实这还不是最主要的，由于Hash表是根据分区键Hash函数值来决定分区的，添加分区的主要时间其实是花在了计算hash值上。在上面的测试中，添加新分区操作的消耗时间是6分58秒，从下面的10046统计信息可以看到，其中6分钟都是花在了CPU操作上，相信主要是Hash运算引起的。

[code]
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse      328      0.17       0.27          0          0        148           0
Execute   1520    360.14     396.30     456820   11416202      26357    11565252
Fetch     1767      5.42      21.18      21421      26540          0        2862
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total     3615    365.73     417.76     478241   11442742      26505    11568114

该测试案例中分区COT_IND01_P37中共有接近1千万条数据，耗时接近7分钟，假设分区数据达到了1亿条，则耗时应该在1个小时以上。如果我们的Hash分区数按Oracle的建议为2的整数幂，则我们在增加分区时是要增加原有分区一倍的新分区，比如原分区为128个，扩展的时候需要增加128个分区，乘以每次添加分区需要的时间，则为Hash表增加分区将是一个很恐怖的操作。
总之，Hash分区有其优势，但也有严重的缺陷，比如这里描述的分区扩展问题。因此在项目设计之初，我们就需要慎重选择分区数。但是随着数据量的增加，我们又很难避免为分区表增加分区的操作，这种操作是很耗资源的操作，操作过程中由于锁的问题会影响对原有某些分区的操作。但如果我们因为畏惧前面存在的问题拖着不作分区扩展，则越是往后，随着数据量的增加，这种增加分区的操作越难以实施。