数据架构师经验总结

Copyrightⓒ2012EN-CORECo.,Ltd.Allrightsreserved|Confidential1为什么需要构建数据架构数据架构的构成如何成为数据架构师问题讨论Copyrightⓒ2012EN-CORECo.,Ltd.Allrightsreserved|Confidential4数据标准不一致列名相同，列的数据类型不同列名相同，列的数据类型相同，长度不同列名定义不统一，类型不一致，长度不相同列名定义没有统一标准，识别困难模型中缺失中文注释，难以验证。中文名称相同，英文缩写不同，英文缩写相同，中文名称不同Copyrightⓒ2012EN-CORECo.,Ltd.Allrightsreserved|Confidential5数据模型管理混乱Copyrightⓒ2012EN-CORECo.,Ltd.Allrightsreserved|Confidential6深入的性能的问题无法解决HintINDEXSQL优化比重未达10%优化比重超过30%优化比重超过50%需要通过SQL语句及数据模型分析性能问题缩减H/WResource帮助开发人员及运维人员提高数据库技术水平优化比重超过80%Copyrightⓒ2012EN-CORECo.,Ltd.Allrightsreserved|Confidential7SQL语句编写水平不高导致出现严重性能问题selecta.*fromLCConta,lcpolb,lccontstatecwhere1=1anda.contno=b.contnoanda.manageComlike'86%'anda.appflag='1'andb.appflag='1'andc.contno=b.contnoandb.polno=b.mainpolnoandc.polno=b.mainpolnoandc.StateType='Available'andc.State='0'andc.EndDateisnullandb.AutoPayFlag='1'andnotexists(select'X'fromlcconthangupstatewherecontno=a.contnoandposflag='1')andexists(select1fromLMRiskAppwhereRiskCode=b.RiskCodeandAutoPayFlag='0')andexists(select1fromLJSPayPersondwhered.ContNo=b.ContNoandnotexists(select1fromLJTempFeewhereotherno=a.contnoandTempFeeType='2'andconfdateisnulland(enteraccdateisnullorenteraccdatedate'3000-1-1'))andnvl((selectBankOnTheWayFlagfromljspayswheres.getnoticeno=d.getnoticeno),'0')'1'andd.LastPayToDateto_date('2015-01-27','YYYY-MM-DD')andd.PayType='ZC')Copyrightⓒ2012EN-CORECo.,Ltd.Allrightsreserved|Confidential8开发人员对执行计划收悉-----------------------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|Reads|-----------------------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||2356|00:23:58.36|4589K|287K||1|NESTEDLOOPS||1|1|2356|00:23:58.36|4589K|287K||2|NESTEDLOOPSANTI||1|1|3530|00:23:41.89|4578K|283K||3|NESTEDLOOPSSEMI||1|1|3553|00:23:41.54|4574K|283K||4|NESTEDLOOPS||1|1|3556|00:23:41.51|4570K|283K||*5|TABLEACCESSBYINDEXROWID|LCPOL|1|1|162K|00:08:32.81|1776K|40189||*6|INDEXRANGESCAN|IDX_ENCN_LCPOL_C04|1|1|1905K|00:00:00.01|28971|0||*7|TABLEACCESSBYINDEXROWID|LCCONT|162K|1|3556|00:15:08.56|2794K|242K||*8|INDEXUNIQUESCAN|PK_LCCONT|162K|1|3556|00:14:28.01|2791K|237K||*9|FILTER||162K||3556|00:14:20.10|2465K|235K||*10|FILTER||162K||5847|00:14:04.26|2450K|231K||*11|TABLEACCESSBYINDEXROWID|LJSPAYPERSON|162K|1|5847|00:01:10.04|505K|16934||*12|INDEXRANGESCAN|IDX_LJSPAYPERSON_1|162K|2|30421|00:00:04.05|488K|863||*13|TABLEACCESSBYINDEXROWID|LJTEMPFEE|162K|1|101|00:12:53.91|1944K|214K||*14|INDEXRANGESCAN|IDX_LJTEMPFEE_1|162K|2|1453K|00:00:55.19|493K|17104||15|TABLEACCESSBYINDEXROWID|LJSPAY|4993|1|4993|00:00:15.74|15075|3941||*16|INDEXUNIQUESCAN|PK_LJSPAY|4993|1|4993|00:00:02.49|10082|727||*17|TABLEACCESSBYINDEXROWID|LMRISKAPP|3556|116|3553|00:00:00.02|3652|0||*18|INDEXUNIQUESCAN|PK_LMRISKAPP|3556|1|3556|00:00:00.01|96|0||*19|TABLEACCESSBYINDEXROWID|LCCONTHANGUPSTATE|3553|5|23|00:00:00.36|3675|41||*20|INDEXRANGESCAN|PK_LCCONTHANGUPSTATE|3553|1|23|00:00:00.35|3651|41||*21|INDEXRANGESCAN|IDX_ENCN_LCCONTSTATE_C01|3530|1|2356|00:00:16.46|10799|4485|-----------------------------------------------------------------------------------------------------------------------------------ELASPEDTIME:23.58.36Copyrightⓒ2012EN-CORECo.,Ltd.Allrightsreserved|Confidential9-----------------------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|Reads|-----------------------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||2356|00:02:56.73|581K|31409||1|NESTEDLOOPSANTI||1|1|2356|00:02:56.73|581K|31409||2|NESTEDLOOPS||1|1|2436|00:00:01.41|555K|0||3|NESTEDLOOPSANTI||1|1|3610|00:00:01.38|544K|0||4|NESTEDLOOPS||1|1|3634|00:00:01.35|540K|0||5|NESTEDLOOPSSEMI||1|1|3634|00:00:01.29|529K|0||6|TABLEACCESSBYINDEXROWID|LCPOL|1|1|3637|00:00:01.29|529K|0||*7|INDEXRANGESCAN|IDX_ENC_TEST_B05|1|1|3637|00:00:01.27|526K|0||*8|FILTER||162K||3637|00:00:01.02|521K|0||*9|TABLEACCESSBYINDEXROWID|LJSPAYPERSON|162K|1|5928|00:00:00.88|506K|0||*10|INDEXRANGESCAN|IDX_LJSPAYPERSON_1|162K|2|30587|00:00:00.70|488K|0||11|TABLEACCESSBYINDEXROWID|LJSPAY|5074|1|5074|00:00:00.05|15318|0||*12|INDEXUNIQUESCAN|PK_LJSPAY|5074|1|5074|00:00:00.02|10244|0||*13|TABLEACCESSBYINDEXROWID|LMRISKAPP|238|116|235|00:00:00.01|298|0||*14|INDEXUNIQUESCAN|PK_LMRISKAPP|238|1|238|00:00:00.01|60|0||*15|TABLEACCESSBYINDEXROWID|LCCONT|3634|1|3634|00:00:00.05|10998|0||*16|INDEXUNIQUESCAN|PK_LCCONT|3634|1|3634|00:00:00.02|7364|0||*17|TABLEACCESSBYINDEXROWID|LCCONTHANGUPSTATE|3634|5|24|00:00:00.02|3756|0||*18|INDEXRANGESCAN|PK_LCCONTHANGUPSTATE|3634|1|24|00:00:00.02|3732|0||*19|INDEXRANGESCAN|IDX_ENCN_LCCONTSTATE_C01|3610|1|2436|00:00:00.03|11041|0||*20|TABLEACCESSBYINDEXROWID|LJTEMPFEE|2436|3|80|00:02:55.31|26161|31409||*21|INDEXRANGESCAN|IDX_LJ