《数据架构师经验总结》由会员分享，可在线阅读，更多相关《数据架构师经验总结（28页珍藏版）》请在金锄头文库上搜索。
1、Copyright 2012 EN-CORE Co., Ltd. All rights reserved | Confidential 1 1 为什么需要构建数据架构 数据架构的构成 如何成为数据架构师 问题讨论 Copyright 2012 EN-CORE Co., Ltd. All rights reserved | Confidential 4 4 数数据标准不一致据标准不一致 列名相同，列的数据类型不同列名相同，列的数据类型不同 列名相同，列的数据类型相同，长度不同列名相同，列的数据类型相同，长度不同 列名定义不统一，类型不一致，长度不相列名定义不统一，类型不一致，长度不相 同同 列名
2、定义没有统一标准，识别困难列名定义没有统一标准，识别困难 模型中缺失中文注释，难以验证。 中文名称相同，英文缩写不同，英文缩写中文名称相同，英文缩写不同，英文缩写 相同，中文名称不同相同，中文名称不同 Copyright 2012 EN-CORE Co., Ltd. All rights reserved | Confidential 5 5 数数据模型管理混乱据模型管理混乱 Copyright 2012 EN-CORE Co., Ltd. All rights reserved | Confidential 6 6 深入的性能的问题无法解决深入的性能的问题无法解决 Hin t INDEX S
3、QL 优化比重未达优化比重未达10% 优化比重超过优化比重超过30% 优化比重超过优化比重超过50% 需需要通过要通过SQL 语句及数据语句及数据 模型分析性模型分析性 能问题能问题 缩减缩减H/W Resource 帮助开发人员及帮助开发人员及 运维人员提高数据运维人员提高数据 库技术水平库技术水平 优化比重超过优化比重超过80% Copyright 2012 EN-CORE Co., Ltd. All rights reserved | Confidential 7 7 SQLSQL语句编写水平不高导致出现严重性能问题语句编写水平不高导致出现严重性能问题 select a.* from L
4、CCont a, lcpol b, lccontstate c where 1 = 1 and a.contno = b.contno and a.manageCom like 86% and a.appflag = 1 and b.appflag = 1 and c.contno = b.contno and b.polno = b.mainpolno and c.polno = b.mainpolno and c.StateType = Available and c.State = 0 and c.EndDate is null and b.AutoPayFlag = 1 and not
5、 exists (select X from lcconthangupstate where contno = a.contno and posflag = 1) and exists (select 1 from LMRiskApp where RiskCode = b.RiskCode and AutoPayFlag = 0) and exists (select 1 from LJSPayPerson d where d.ContNo = b.ContNo and not exists (select 1 from LJTempFee where otherno = a.contno a
6、nd TempFeeType = 2 and confdate is null and (enteraccdate is null or enteraccdate 1 and d.LastPayToDate to_date(2015-01-27, YYYY-MM-DD) and d.PayType = ZC) Copyright 2012 EN-CORE Co., Ltd. All rights reserved | Confidential 8 8 开开发人员对执行计划收悉发人员对执行计划收悉 - | Id | Operation | Name | Starts | E| Id | Oper
7、ation | Name | Starts | E- -Rows | ARows | A- -Rows | ARows | A- -Time | Buffers | ReadsTime | Buffers | Reads | | - | 0 | SELECT STATEMENT | | 1 | | 2356 | 0 | SELECT STATEMENT | | 1 | | 2356 |00:23:58.36 00:23:58.36 | 4589K| 287K| 4589K| 287K| | 1 | NESTED LOOPS | | 1 | 1 | 2356 |00:23:58.36 | 458
8、9K| | 1 | NESTED LOOPS | | 1 | 1 | 2356 |00:23:58.36 | 4589K| 28287K|7K| | 2 | NESTED LOOPS ANTI | | 1 | 1 | 3530 |00:23:41.89 | 4578K| | 2 | NESTED LOOPS ANTI | | 1 | 1 | 3530 |00:23:41.89 | 4578K| 28283K|3K| | 3 | NESTED LOOPS SEMI | | 1 | 1 | 3553 |00:23:41.54 | 4574K| | 3 | NESTED LOOPS SEMI | |
9、 1 | 1 | 3553 |00:23:41.54 | 4574K| 28283K|3K| | 4 | NESTED LOOPS | | 1 | 1 | 3556 |00:23:41.51 | 4570K| | 4 | NESTED LOOPS | | 1 | 1 | 3556 |00:23:41.51 | 4570K| 28283K|3K| |* 5 | TABLE ACCESS BY INDEX ROWID | LCPOL | 1 | 1 | 162K|00:08:32.81 | 1776K| 4|* 5 | TABLE ACCESS BY INDEX ROWID | LCPOL | 1
10、 | 1 | 162K|00:08:32.81 | 1776K| 40180189 |9 | |* 6 | INDEX RANGE SCAN | IDX_ENCN_LCPOL_C04 | 1 | 1 | 1905K|00:00:00.01 | 28971 | |* 6 | INDEX RANGE SCAN | IDX_ENCN_LCPOL_C04 | 1 | 1 | 1905K|00:00:00.01 | 28971 | 0 |0 | |* 7 | TABLE ACCESS BY INDEX ROWID | LCCONT | 162K| 1 | 3556 |00:15:08.56 | 2794K| |* 7 | TABLE ACCESS BY INDEX ROWID | LCCONT | 162K| 1 | 3556 |00:15:08.56 | 2794K| 24242K|2K| |* 8 | INDEX UNIQUE SCAN | PK_LCCONT | 162K| 1 | 3556 |00:14:28.01 | 2791K| |* 8 | INDEX UNIQUE SCAN | PK_LCCONT | 162K|
