数据仓库应用开发经验之谈.pdf
5墨值下载
数据仓库应用开发经验之谈
最近连续为两家银行的数据仓库系统推广性能优化服务，通过远程调研和现场实地分析，
我发现两家银行的数据仓库运行现状和应用开发特点都非常相似，以至于我刚完成针对A银行的
《数据仓库系统性能优化技术交流.pptx》，我就对另一位销售说：我把这个PPT中的A银行换成
你的B银行，再把运行数据和SQL语句换一下，马上就可以和B银行客户交流了。
A银行和B银行的数据仓库系统运行现状如何？原来两套系统的夜间ETL作业都是运行时间
长、资源消耗高。谁导致的？原来两套系统的ETL应用都是充斥了太多不必要的全表扫描。与两
个开发团队沟通，他们都有数据仓库应用就应该尽量采用全表扫描，并尽量少用索引的开发指导
思想。实际情况呢？我在A银行进行的现场POC测试结果显示：ETL一条重要语句被我采用索引技
术后，从3分多钟优化到5秒钟，I/O从200GB下降到1.5GB。
于是，我决意赶紧把这些最新的案例和经验之谈付诸文字，供更多业内朋友参考，也尽快消
除广大开发人员在数据仓库技术运用中的某些误区，更是把困扰很多客户的数据仓库ETL作业和
其它跑批应用动辄需要运行数小时、每天耗费TB级资源的烦恼，尽快都驱散掉。当然，本文仅仅
是抛砖引玉的经验之谈，欲真正实现上述目标，还需要采购原厂专业服务，哈哈。更需要客户的
设计、开发、运维人员与专业服务团队展开全方位合作，共同开展数据仓库应用的全面整改。
1. 联机交易和数据仓库系统的不同技术运用
就在本周一与负责B银行的销售同事分享上周我在A银行的经验时，他问我：数据仓库应用
尽量采用全表扫描、尽量少用索引的开发指导思想，是Oracle官方最佳实践经验吗？我笑言道：
的确是Oracle官方最佳实践经验，也是本罗老师到处散布的“流毒”，哈哈。
以下就是我在多本书、多篇文章、多个PPT、多个解决方案文档和与客户多次交流中经常使
用的一张片子，即联机交易系统（OLTP）和联机分析类系统（OLAP）在业务特征和技术运用差异
性对比分析表格，这个表格既基于Oracle公司在性能优化领域的方法论和最佳实践经验，也凝聚
了业务广大同行和我自己多年总结的实战经验。
即联机交易（OLTP）和联机分析（OLAP）在业务和技术运用方面都各有特点甚至风格迥异。
典型的OLTP系统包括银行核心业务系统、网银系统、ATM系统、手机银行系统等，还有电信运行
商的CRM系统、计费系统、账务系统等，也包括保险行业的承保系统、赔付系统等，这些系统一
大业务特点就是面向广大客户，具有并发量高，但单笔事务小的特点，例如都是查询某个客户账
号、手机号、保单号的信息。因此在技术运用方面，OLTP系统一个基本策略就是大海捞针，于是
索引、分区索引、嵌套循环（Nested_loop）连接技术等就成为了主要的技术策略，还有尽量使
用绑定变量，降低高并发语句的硬解析次数，以及不采用并行处理等技术策略。
而典型的OLAP系统和应用包括报表系统、统计分析系统、数据集市和数据仓库系统，以及
OLTP系统中的夜间跑批、季度结息、年终决算等批处理应用。此类系统的业务方面具有并发量不
高，但单笔事务大的特点，例如按时间、地区等维度进行全行的存款额统计汇总等。因此在技术
运用方面，OLAP系统一个基本策略就是大气磅礴的大吞吐量、大批量处理，于是全表扫描、全分
区扫描、哈希连接（HASH JOIN）、并行处理等技术成为了主要的技术策略，而各种索引技术运
用并不一定是首选技术，即便使用索引，也是采用Bitmap、Bitmap Join等更适合统计分析的索
引技术。另外OLAP应用通常不建议使用绑定变量，目的就是让Oracle明明白白地基于常量来确保
复杂统计分析语句的执行计划最优化。
2. 季度结息应用中OLAP技术运用的成功案例
2021年夏天我去某银行现场参与季度结息业务的值守服务，再次切身感受了银行业务和技
术人士以及我们服务团队熬夜的辛苦。那次，我深入分析了该行季度结息应用特点，当晚就做出
了季度结息应用可从通宵8小时优化到2小时，免去大家辛苦熬夜值守的判断。该案例在《某银行
比较项目 联机交易（OLTP） 联机分析（OLAP）
业务特征
操作特点
日常业务操作
，尤其是包含大量前台操作
后台操作，例如统计报表、大批量数据加载
响应速度
优先级最高
，要求反应速度非常高
要求速度高
、吞吐量大
吞吐量
小
大
并发访问量
非常高
不高
单笔事务的资源消耗
小
大
SQL
语句类型
主要是插入和修改操作
（DML）
主要是大量查询操作或批量
DML操作
技术运用
索引类型
B*
索引
Bitmap
、Bitmap Join索引
索引量
适量
多
访问方式
按索引访问
全表扫
描/全分区扫描
连接方式
Nested_loop
Hash
Join
BIND
变量
使用或强制使用
不使用
并行技术
使用不多
大量使用
分区技术
使用
，但目标不同
使用
，但目标不同
物化视图使用
少量使用
大量使用
的季度结息（上）》和《某银行的季度结息（下）》两文中已经有详细描述，不再赘述相关细节。
本文仅从OLAP应用开发最佳实践经验角度去重温该季度结息应用的优化过程。
这就是前年我针对该行结息应用开展的4轮优化测试结果：
案例
时间
提升比
案例1：普通表按索引查询
00:24:45
N/A
案例2：分区表按索引查询
00:10:27
2.36
案例3：分区表按分区扫描查询
00:07:13
3.42
案例4：分区表按分区扫描+parallel查询
00:06:26
3.84
这就是该行结息应用中其实非常简单的单表操作语句：
select B.KEY_1,
B.TOD_CDEP_TOT_AMT,
... ...
B.LAST_MAINT_STAT
from INVE B
where B.KEY_1 between :b1 and :b2
order by B.KEY_1
以下就是目前现状的“案例1：普通表按索引查询”的
示意图：
即结息应用目前是按KEY_1字段分段在应用层进
行并行处理，每个号段都是通过IDX_KEY_1索引进行访
问。因此，为完成所有账号的季度结息处理，实际上最
终访问了IDX_KEY_1整个索引以及INVE整个表。运行时
间是00:24:45。
以下就是“案例2：分区表按索引查询”
的示意图，即在将INVE表按号段分区并将
IDX_KEY_1 索引创建为Local prefixed-
partition分区索引之后，若按分区索引访问
分区表，相比案例1，速度提高到了00:10:27，
即提高了2.36倍。这就是分区技术“分而治
之”的优化效果。
既然是访问INVE表所有数据，那么完全可以不使用索引，并且基于分区裁剪技术进行所有分
区的扫描，即如下图所示的“案例3：分区表按
分区扫描查询”：
案例3的实际运行时间是00:07:13，相比
最初的案例1提升比达到3.42。
INVE
IDX_KEY_1
号段1 号段2
… …
号段n
INV
E_P
_PK
号段1 号段2
… …
号段n
INV
E_P
_PK
INV
E_P
_PK
INV
E_P
_PK
号段1 号段2
…
号段n
号段1 号段2
…
号段n
of 12
5墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
最新上传
下载排行榜
1
2
9-数据库人的进阶之路：从PG分区、SQL优化到拥抱AI未来（罗敏）.pptx
3
1-PG版本兼容性案例（彭冲）.pptx
4
2-TDSQL PG在复杂查询场景中的挑战与实践-opensource.pdf
5
6-PostgreSQL 哈希索引原理浅析（文一）.pdf
6
3-AI时代的变革者-面向机器的接口语言(MOQL)_吕海波.pptx
7
8-基于PG向量和RAG技术的开源知识库问答系统MaxKB.pptx
8
4-IvorySQL V4：双解析器架构下的兼容性创新实践.pptx
9
7-拉起PG好伙伴DifySupaOdoo.pdf
10
《云原生安全攻防启示录》李帅臻.pdf
相关文档
评论