oracle 11g 新特性应用--虚拟列&ora_hash() 在性能提升的作用（merge） / 开普饭

情景说明:数据仓库运行以每天或一固定频率同步刷新增量数据后，需要对增量数据插入全量数据的过程，当数据量达到超大数量级时，

数据的更新操作的性能会严重不足，产生这种情况的原因多是因为程序中直接使用。

主要处理过程之一，每4个小时把新增的快递单数据
merge到历史数据中
---如果存在同样的订号，就把历史表的信息更新为新增的信息；
---如果不存在同样的订号，就把新增的信息插入历史表；

历史表：
! 按时间分区
! 保留15天数据
! 最大单分区记录通常为几亿到十几亿
! 最大单分区20-50G
· 新增数据
! 几百万到几千万

常见merge语句

MERGE INTO tabe_history A
USING (select * from table_incred
where biz_date < to_date(:edate,'YYYYMMDD HH24')
and biz_date > to_date(:bdate,'YYYYMMDD HH24')
) B
ON (A.ENTERPRISE_CODE = B.ENTERPRISE_CODE AND A.MAIL_NO = B.MAIL_NO)
WHEN MATCHED THEN
UPDATE
SET A.MAIL_TYPE = NVL(A.MAIL_TYPE, B.MAIL_TYPE),
A.WEIGHT = NVL(A.WEIGHT, B.WEIGHT),
...<几十个 columns>...)
WHEN NOT MATCHED THEN
INSERT (A.ENTERPRISE_CODE, A.MAIL_NO, A.MAIL_TYPE, ...<几十个 columns>...)
VALUES(B.ENTERPRISE_CODE, B.MAIL_NO, B.MAIL_TYPE,...<几十个 columns>...);

该过程中的hash join 方法

Hash Join的过程：
1. 读取驱动表(新增数据表）.
2. 对相应列（mail_no )建立hash bucket.
3. PGA内存足够时，把所有hash bucket放在内存中, 匹配被驱动表（历史表）数据.
4. PGA内存不足时，需要把部分hash bucket放在内存中，多次匹配被驱动表（历史表）数据.
5. 结合驱动表和被驱动表数据，进行merge操作.

最终造成性能问题的原因

内存不足导致PGA不足，Hash匹配需要多次进行，最终历史表需要多次被扫描

解决方法：
! 取Hash 值后分区
! 类似于Oracle Hash分区，但不等同于Hash分区
! 自定义，可控的Hash分区 ----KEY POINT如何建立可控的HASH分区，该篇文章关键点。

应用解决：使用11g 虚拟列，列值通过ora_hash()获取
! 通过其他列运算得到
! 不占用空间
! 不需要手动更新值
! 分布绝大多数情况下非常均匀

概念说明

虚拟列定义

Oracle 11G 在表中引入了虚拟列，虚拟列是一个表达式，在运行时计算，不存储在数据库中，不能更新虚拟列的值。

定义一个虚拟列的语法： refer to https://www.2cto.com/database/201306/216917.html
column_name [datatype] [GENERATED ALWAYS] AS [] [VIRTUAL]

1.虚拟列可以用在select，update，delete语句的where条件中，但是不能用于DML语句
2.可以基于虚拟列来做分区
3. 可以在虚拟列上建索引，oracle的函数索引就类似。
4. 可以在虚拟列上建约束

建表语句也可以自定义

virtual_column是yes，表示是虚拟列，我们也可以在建表语句加上VIRTUAL，显示声明为虚拟列

total_sal NUMBER(10,2) AS (get_empl_total_sal(monthly_sal, bonus)) VIRTUAL ---虚拟列的值不可以手动添加只能通过表达式计算

ORA_HASH函数

就是讲第一个参数只进行hash计算，然后根据第二和第三个参数将计算后的hash值平均分到几个部分

oracle中的hash分区就是利用的ora_hash函数

partition by hash(object_id) 等价于 ora_hash(object_id,4294967295)

ora_hash(列,hash桶) hash桶默认是4294967295 可以设置0到4294967295

ora_hash(object_id,4) 会把object_id的值进行hash运算，然后放到 0,1,2,3,4 这些桶里面，

也就是说 ora_hash(object_id, 4，0) 只会产生 0 1 2 3 4

ora_hash('strings', N, 0 )

strings: 输入值
N：最大hash bucket的值
0：起始hash bucket值，缺省是1

；

解决结果

1. 将历史表和新增数据表改造为基于虚拟列的分区表
Create table table_history
(
Mail_no varchar2(32),
....
Hash_parted_key generated always as (ora_hash(Mail_no,63 )

)
)
Partition by list(Hash_parted_key)
(
Partition p0 values(0),
Partition p1 values(1),
...
);

省略table_incre

2. 改写存储过程

For loop_index in 0..63 loop
MERGE INTO (select * from table_history where hash_parted_key = loop_index )
A
USING (select * from table_incre
where biz_date < to_date(:edate,'YYYYMMDD HH24')
and biz_date > to_date(:bdate,'YYYYMMDD HH24')
and hash_parted_key = loop_index
) B
ON (A.ENTERPRISE_CODE = B.ENTERPRISE_CODE AND A.MAIL_NO = B.MAIL_NO)
WHEN MATCHED THEN
UPDATE
SET A.MAIL_TYPE = NVL(A.MAIL_TYPE, B.MAIL_TYPE),
A.WEIGHT = NVL(A.WEIGHT, B.WEIGHT),
...<几十个 columns>...)
WHEN NOT MATCHED THEN
INSERT (A.ENTERPRISE_CODE, A.MAIL_NO, A.MAIL_TYPE, ...<几十个 columns>...)
VALUES(B.ENTERPRISE_CODE, B.MAIL_NO, B.MAIL_TYPE,...<几十个 columns>...);
End loop;
End;

说明: 因为两个表的hash键都是使用同样的数据字段，所以表划分数据结果集ora——hash的数据结果也是一致的。

所以当数据匹配的时候，不会造成误差

oracle 11g 新特性应用--虚拟列&ora_hash() 在性能提升的作用（merge）

虚拟列定义

ORA_HASH函数

相关推荐