來OB這麼久還沒有接觸啥金融的SQL，只能發點其他行業的資料庫SQL最佳化案例。😂
今天拿到手的這個案例SQL 傳統行業的，很奇葩的SQL，表設計三正規化都沒弄好。
什麼醫療，交通，能源這些傳統行業的業務設計，SQL語句最奇葩了，也挺好玩的，有挑戰性。
慢SQL：
SELECT LI.STATUS, FI.SOFL_SEQ_NR ,PI.CLAZZ,PI.HV_TYPE FROM LNF LI, PNF PI, FNF FI WHERE 1=1 AND LI.ALN_CD = DECODE(FI.ALN_CD, 'OQ', 'CZ', FI.ALN_CD) and li.dep_arp_cd = fi.act_dep_cd and li.flt_nr = DECODE(fi.aln_cd,'OQ',substr(( select cz_flt_nr from OQ_FLT_INFO oq where substr(oq.oq_flt_nr,3,4)= fi.flt_nr) ,3,4), fi.flt_nr) and li.FLT_DT=FI.FLT_DT AND LI.FK_PSGR_ID = PI.PSGR_ID AND (PI.HV_TYPE IS NOT NULL OR PI.CLAZZ IN ('F', 'F1', 'J', 'C', 'D', 'I', 'O')) and FI.FLT_DT=date'2024-04-01' and FI.SCH_DEP_CD='CAN' ORDER BY FI.SOFL_SEQ_NR ;
執行時間：
執行計劃（看不看得懂隨緣、反正我沒看）：
1 #NSET2: [2903, 556029, 632] 2 #PIPE2: [2903, 556029, 632] 3 #PIPE2: [2901, 556029, 632] 4 #PRJT2: [2899, 556029, 632]; exp_num(4), is_atom(FALSE) 5 #SORT3: [2899, 556029, 632]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0) 6 #UNION FOR OR2: [2788, 556029, 632]; key_num(0), outer_join(-) 7 #SLCT2: [1334, 278014, 632]; LI.FLT_NR = exp_simple_case 8 #HASH2 INNER JOIN: [1334, 278014, 632]; KEY_NUM(2); KEY(FI.ACT_DEP_CD=LI.DEP_ARP_CD AND exp_simple_case=LI.ALN_CD) KEY_NULL_EQU(0, 0) 9 #BLKUP2: [1, 385, 247]; IDX_FLIGHT_INFO02(FI) 10 #SSEK2: [1, 385, 247]; scan_type(ASC), IDX_FLIGHT_INFO02(FNF as FI), scan_range[(exp_cast(2024-04-01),'CAN',min),(exp_cast(2024-04-01),'CAN',max)) 11 #SLCT2: [1324, 37479, 385]; NOT(PI.HV_TYPE IS NULL) 12 #HASH2 INNER JOIN: [1324, 37479, 385]; RKEY_UNIQUE KEY_NUM(1); KEY(LI.FK_PSGR_ID=PI.PSGR_ID) KEY_NULL_EQU(0) 13 #SLCT2: [1324, 37479, 385]; NOT(PI.HV_TYPE IS NULL) 14 #NEST LOOP INDEX JOIN2: [1324, 37479, 385] 15 #ACTRL: [1324, 37479, 385]; 16 #BLKUP2: [1049, 37479, 247]; INDEX_LU_INTO_TAG_0416(LI) 17 #SLCT2: [1049, 37479, 247]; LI.FLT_DT = var3 18 #SSCN: [1049, 37479, 247]; INDEX_LU_INTO_TAG_0416(LNF as LI); btr_scan(1) 19 #BLKUP2: [244, 1, 30]; INDEX33555482(PI) 20 #SSEK2: [244, 1, 30]; scan_type(ASC), INDEX33555482(PNF as PI), scan_range[LI.FK_PSGR_ID,LI.FK_PSGR_ID] 21 #CSCN2: [475, 3573399, 138]; INDEX33555481(PNF as PI); btr_scan(1) 22 #SLCT2: [1334, 278014, 632]; LI.FLT_NR = exp_simple_case 23 #HASH2 INNER JOIN: [1334, 278014, 632]; KEY_NUM(2); KEY(FI.ACT_DEP_CD=LI.DEP_ARP_CD AND exp_simple_case=LI.ALN_CD) KEY_NULL_EQU(0, 0) 24 #BLKUP2: [1, 385, 247]; IDX_FLIGHT_INFO02(FI) 25 #SSEK2: [1, 385, 247]; scan_type(ASC), IDX_FLIGHT_INFO02(FNF as FI), scan_range[(exp_cast(2024-04-01),'CAN',min),(exp_cast(2024-04-01),'CAN',max)) 26 #SLCT2: [1324, 37479, 385]; (exp11 AND PI.CLAZZ IN LIST) 27 #HASH2 INNER JOIN: [1324, 37479, 385]; RKEY_UNIQUE KEY_NUM(1); KEY(LI.FK_PSGR_ID=PI.PSGR_ID) KEY_NULL_EQU(0) 28 #SLCT2: [1324, 37479, 385]; (exp11 AND PI.CLAZZ IN LIST) 29 #NEST LOOP INDEX JOIN2: [1324, 37479, 385] 30 #ACTRL: [1324, 37479, 385]; 31 #BLKUP2: [1049, 37479, 247]; INDEX_LU_INTO_TAG_0416(LI) 32 #SLCT2: [1049, 37479, 247]; LI.FLT_DT = var4 33 #SSCN: [1049, 37479, 247]; INDEX_LU_INTO_TAG_0416(LNF as LI); btr_scan(1) 34 #BLKUP2: [244, 1, 30]; INDEX33555482(PI) 35 #SSEK2: [244, 1, 30]; scan_type(ASC), INDEX33555482(PNF as PI), scan_range[LI.FK_PSGR_ID,LI.FK_PSGR_ID] 36 #CSCN2: [475, 3573399, 138]; INDEX33555481(PNF as PI); btr_scan(1) 37 #SPL2: [1, 279, 343]; key_num(2), spool_num(1), is_atom(FALSE), has_var(0), sites(-) 38 #PRJT2: [1, 279, 343]; exp_num(2), is_atom(FALSE) 39 #HASH2 INNER JOIN: [1, 279, 343]; KEY_NUM(1); KEY(exp11=FI.FLT_NR) KEY_NULL_EQU(0) 40 #CSCN2: [1, 279, 96]; INDEX33555478(OQ_FLT_INFO as OQ); btr_scan(1) 41 #BLKUP2: [1, 385, 247]; IDX_FLIGHT_INFO02(FI) 42 #SSEK2: [1, 385, 247]; scan_type(ASC), IDX_FLIGHT_INFO02(FNF as FI), scan_range[(exp_cast(2024-04-01),'CAN',min),(exp_cast(2024-04-01),'CAN',max)) 43 #SPL2: [1, 279, 343]; key_num(2), spool_num(0), is_atom(FALSE), has_var(0), sites(-) 44 #PRJT2: [1, 279, 343]; exp_num(2), is_atom(FALSE) 45 #HASH2 INNER JOIN: [1, 279, 343]; KEY_NUM(1); KEY(exp11=FI.FLT_NR) KEY_NULL_EQU(0) 46 #CSCN2: [1, 279, 96]; INDEX33555478(OQ_FLT_INFO as OQ); btr_scan(1) 47 #BLKUP2: [1, 385, 247]; IDX_FLIGHT_INFO02(FI) 48 #SSEK2: [1, 385, 247]; scan_type(ASC), IDX_FLIGHT_INFO02(FNF as FI), scan_range[(exp_cast(2024-04-01),'CAN',min),(exp_cast(2024-04-01),'CAN',max))
表資料量：
上面SQL跑28秒，返回8行資料，還是挺慢的。
DM哥們說他已經加個HINT對 or 進行整體最佳化，但是還要跑12秒，客戶不接受，所以找到我來看看。😅
加HINT最佳化方案：
SELECT /*+ OPTIMIZER_OR_NBEXP(2) */ LI.STATUS, FI.SOFL_SEQ_NR ,PI.CLAZZ,PI.HV_TYPE FROM LNF LI, PNF PI, FNF FI WHERE 1=1 AND LI.ALN_CD = DECODE(FI.ALN_CD, 'OQ', 'CZ', FI.ALN_CD) and li.dep_arp_cd = fi.act_dep_cd and li.flt_nr = DECODE(fi.aln_cd,'OQ',substr(( select cz_flt_nr from OQ_FLT_INFO oq where substr(oq.oq_flt_nr,3,4)= fi.flt_nr) ,3,4), fi.flt_nr) and li.FLT_DT=FI.FLT_DT AND LI.FK_PSGR_ID = PI.PSGR_ID AND (PI.HV_TYPE IS NOT NULL OR PI.CLAZZ IN ('F', 'F1', 'J', 'C', 'D', 'I', 'O')) and FI.FLT_DT=date'2024-04-01' and FI.SCH_DEP_CD='CAN' ORDER BY FI.SOFL_SEQ_NR ;
加HINT後執行計劃：
1 #NSET2: [1372, 278014, 608] 2 #PIPE2: [1372, 278014, 608] 3 #PRJT2: [1371, 278014, 608]; exp_num(4), is_atom(FALSE) 4 #SORT3: [1371, 278014, 608]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0) 5 #SLCT2: [1317, 278014, 608]; LI.FLT_NR = exp_simple_case 6 #HASH2 INNER JOIN: [1317, 278014, 608]; KEY_NUM(2); KEY(FI.ACT_DEP_CD=LI.DEP_ARP_CD AND exp_simple_case=LI.ALN_CD) KEY_NULL_EQU(0, 0) 7 #BLKUP2: [1, 385, 247]; IDX_FLIGHT_INFO02(FI) 8 #SSEK2: [1, 385, 247]; scan_type(ASC), IDX_FLIGHT_INFO02(FNF as FI), scan_range[(exp_cast(2024-04-01),'CAN',min),(exp_cast(2024-04-01),'CAN',max)) 9 #SLCT2: [1307, 37479, 361]; (NOT(PI.HV_TYPE IS NULL) OR PI.CLAZZ IN LIST) 10 #HASH2 INNER JOIN: [1307, 37479, 361]; RKEY_UNIQUE KEY_NUM(1); KEY(LI.FK_PSGR_ID=PI.PSGR_ID) KEY_NULL_EQU(0) 11 #SLCT2: [1307, 37479, 361]; (NOT(PI.HV_TYPE IS NULL) OR PI.CLAZZ IN LIST) 12 #NEST LOOP INDEX JOIN2: [1307, 37479, 361] 13 #ACTRL: [1307, 37479, 361]; 14 #BLKUP2: [1032, 37479, 235]; INDEX_LU_INTO_TAG_0416(LI) 15 #SLCT2: [1032, 37479, 235]; LI.FLT_DT = var2 16 #SSCN: [1032, 37479, 235]; INDEX_LU_INTO_TAG_0416(LNF as LI); btr_scan(1) 17 #BLKUP2: [244, 1, 30]; INDEX33555482(PI) 18 #SSEK2: [244, 1, 30]; scan_type(ASC), INDEX33555482(PNF as PI), scan_range[LI.FK_PSGR_ID,LI.FK_PSGR_ID] 19 #CSCN2: [467, 3573399, 126]; INDEX33555481(PNF as PI); btr_scan(1) 20 #SPL2: [1, 279, 343]; key_num(2), spool_num(0), is_atom(FALSE), has_var(0), sites(-) 21 #PRJT2: [1, 279, 343]; exp_num(2), is_atom(FALSE) 22 #HASH2 INNER JOIN: [1, 279, 343]; KEY_NUM(1); KEY(exp11=FI.FLT_NR) KEY_NULL_EQU(0) 23 #CSCN2: [1, 279, 96]; INDEX33555478(OQ_FLT_INFO as OQ); btr_scan(1) 24 #BLKUP2: [1, 385, 247]; IDX_FLIGHT_INFO02(FI) 25 #SSEK2: [1, 385, 247]; scan_type(ASC), IDX_FLIGHT_INFO02(FNF as FI), scan_range[(exp_cast(2024-04-01),'CAN',min),(exp_cast(2024-04-01),'CAN',max))
HINT 執行時間：
12秒對原來的28秒來說已經提升了很大的空間，但是客戶表示不滿意：ORACLE能秒出結果，到了達夢以後執行時間多了12倍，接受不了。😅
無解，最後找到哥，看看能不能幫他讓這條SQL"秒出結果"。😎
簡單看了看，加索引和使用HINT都不好使，只能等價改寫了，再建立合適的索引，讓這條SQL走上新的索引。
等價改寫 + 索引最佳化方案：
SELECT LI.STATUS, FI.SOFL_SEQ_NR, PI.CLAZZ, PI.HV_TYPE FROM LNF LI INNER JOIN (SELECT ACT_DEP_CD, FLT_DT, SOFL_SEQ_NR, DECODE(ALN_CD, 'OQ', 'CZ', ALN_CD) V1, DECODE(ALN_CD, 'OQ', SUBSTR( (SELECT CZ_FLT_NR FROM OQ_FLT_INFO OQ WHERE SUBSTR(OQ.OQ_FLT_NR, 3, 4) = FLT_NR), 3, 4), FLT_NR) V2 FROM FNF WHERE FLT_DT = DATE'2024-04-01' AND SCH_DEP_CD = 'CAN' AND ROWNUM > 0) FI ON LI.DEP_ARP_CD = FI.ACT_DEP_CD AND LI.FLT_DT = FI.FLT_DT AND LI.ALN_CD = FI.V1 AND LI.FLT_NR = FI.V2 INNER JOIN (WITH PI AS (SELECT PSGR_ID, HV_TYPE, CLAZZ FROM PNF) SELECT DISTINCT PSGR_ID, HV_TYPE, CLAZZ FROM (SELECT PSGR_ID, HV_TYPE, CLAZZ FROM PI WHERE (PI.HV_TYPE IS NOT NULL) UNION ALL SELECT PSGR_ID, HV_TYPE, CLAZZ FROM PI WHERE PI.CLAZZ IN ('F', 'F1', 'J', 'C', 'D', 'I', 'O'))) PI ON LI.FK_PSGR_ID = PI.PSGR_ID ORDER BY FI.SOFL_SEQ_NR; -- 加索引： create index idx_pi_1_2 on PNF( PSGR_ID,CLAZZ,HV_TYPE ); create index idx_fi_1_2 on FNF ( act_dep_cd,FLT_DT ); CREATE index idx_1_2_3_FI on FNF(FLT_DT,SCH_DEP_CD,act_dep_cd,SOFL_SEQ_NR); CREATE index idx_1_2_3_li on LNF(ALN_CD,dep_arp_cd,flt_nr,FLT_DT,FK_PSGR_ID,STATUS); CREATE index idx_1_2_3 on FNF(FLT_DT,SCH_DEP_CD);
最佳化後執行計劃：
#NSET2: [821, 385, 596] 2 #PIPE2: [821, 385, 596] 3 #PRJT2: [821, 385, 596]; exp_num(4), is_atom(FALSE) 4 #SORT3: [821, 385, 596]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0) 5 #SLCT2: [820, 385, 596]; LI.FK_PSGR_ID = PI.PSGR_ID 6 #NEST LOOP INNER JOIN2: [820, 385, 596]; [with var] 7 #HASH2 INNER JOIN: [2, 385, 470]; KEY_NUM(4); KEY(FI.ACT_DEP_CD=LI.DEP_ARP_CD AND FI.FLT_DT=LI.FLT_DT AND FI.V1=LI.ALN_CD AND FI.V2=LI.FLT_NR) KEY_NULL_EQU(0, 0, 0, 0) 8 #NEST LOOP INDEX JOIN2: [2, 385, 470] 9 #ACTRL: [2, 385, 470]; 10 #PRJT2: [1, 385, 235]; exp_num(5), is_atom(FALSE) 11 #RN: [1, 385, 235] 12 #BLKUP2: [1, 385, 235]; IDX_FLIGHT_INFO02(FNF) 13 #SSEK2: [1, 385, 235]; scan_type(ASC), IDX_FLIGHT_INFO02(FNF), scan_range[(exp_cast(2024-04-01),'CAN',min),(exp_cast(2024-04-01),'CAN',max)) 14 #SSEK2: [1, 1, 235]; scan_type(ASC), IDX_1_2_3_LI(LNF as LI), scan_range[(FI.V1,FI.ACT_DEP_CD,FI.V2,FI.FLT_DT,min,min),(FI.V1,FI.ACT_DEP_CD,FI.V2,FI.FLT_DT,max,max)) 15 #SSCN: [989, 6461936, 235]; IDX_1_2_3_LI(LNF as LI); btr_scan(1) 16 #PRJT2: [2, 1, 126]; exp_num(3), is_atom(FALSE) 17 #DISTINCT: [2, 1, 126] 18 #PRJT2: [1, 2, 126]; exp_num(3), is_atom(FALSE) 19 #UNION ALL: [1, 2, 126] 20 #PRJT2: [1, 1, 126]; exp_num(3), is_atom(FALSE) 21 #SLCT2: [1, 1, 126]; NOT(PNF.HV_TYPE IS NULL) 22 #BLKUP2: [1, 1, 126]; INDEX33555482(PNF) 23 #SSEK2: [1, 1, 126]; scan_type(ASC), INDEX33555482(PNF), scan_range[var4,var4] 24 #PRJT2: [1, 1, 126]; exp_num(3), is_atom(FALSE) 25 #HASH RIGHT SEMI JOIN2: [1, 1, 126]; n_keys(1) KEY(DMTEMPVIEW_889228539.colname=PNF.CLAZZ) KEY_NULL_EQU(0) 26 #CONST VALUE LIST: [1, 7, 48]; row_num(7), col_num(1) 27 #BLKUP2: [1, 1, 126]; INDEX33555482(PNF) 28 #SSEK2: [1, 1, 126]; scan_type(ASC), INDEX33555482(PNF), scan_range[var4,var4] 29 #SPL2: [1, 1, 96]; key_num(1), spool_num(0), is_atom(TRUE), has_var(1), sites(-) 30 #PRJT2: [1, 1, 96]; exp_num(1), is_atom(TRUE) 31 #BLKUP2: [1, 6, 96]; INDEX33555479(OQ) 32 #SLCT2: [1, 6, 96]; var3 = exp11 33 #SSCN: [1, 6, 96]; INDEX33555479(OQ_FLT_INFO as OQ); btr_scan(1)
執行時間：
透過改寫+建立索引最佳化後，能做到像ORACLE這樣，真"秒出結果"。😺
堆表和索引組織表的區別還是蠻大的，以前的老系統，業務SQL寫得爛，用堆表跑問題不大，Oracle cbo演算法牛逼也抗得住。
但是現在很多國產資料庫都是使用索引組織表（IOT表），例如 OB、DM、TIDB 等國產資料庫。
由於IOT表的特性在使用場景來說，對些老系統來說並不友好（老系統業務設計隨意、業務邏輯較多在資料庫層面實現），所以會產生很多效能問題：ORACLE 的索引都遷移到國產資料庫了，效能還這麼差，為什麼沒用上原來的索引啥的，等等諸如此類的問題。
要做國產化適配改造，並不能滿足資料庫功能、特性上的實現，業務模型也要配合整體來進行改造。
感謝各位讀者同學能看到這裡，如果有一些奇葩的SQL問題也可以聯絡我。🥰🥰🥰