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

最新推荐文章于 2024-10-08 11:27:53 发布

richieruan

最新推荐文章于 2024-10-08 11:27:53 发布

阅读量2.5k

点赞数 2

分类专栏：数据库文章标签： oracle数据库函数 11gR2

本文链接：https://blog.csdn.net/richieruan/article/details/79113875

版权

本文探讨了在Oracle 11g中，如何利用虚拟列和ora_hash()函数解决大规模数据merge性能问题。通过对历史表和新增数据表增加基于ora_hash()的虚拟列进行分区，优化merge语句，避免了内存不足导致的性能瓶颈。通过实例展示了如何改写存储过程，显著提升了数据更新操作的效率。

摘要由CSDN通过智能技术生成

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

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

主要处理过程之一，每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放在内存中，多次匹配被