基于列转行大表的复杂查询优化
横版流程卡 优化（复杂SQL的优化），留档
一、横版流程卡原SQL
1 SELECT SOL.*,para.* FROM 2 (SELECT 3 A.SERIAL_NUMBER, 4 SUM(casewhen B.spc_item='Speed'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS1'then A.SPC_VALUE ELSE 0end)as LAS1_Speed , 5 SUM(casewhen B.spc_item='Acceleration'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS1'then A.SPC_VALUE ELSE 0end)as LAS1_Acceleration , 6 SUM(casewhen B.spc_item='Frequency'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS1'then A.SPC_VALUE ELSE 0end)as LAS1_Frequency , 7 SUM(casewhen B.spc_item='Current'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS1'then A.SPC_VALUE ELSE 0end)as LAS1_Current , 8 SUM(casewhen B.spc_item='Frequency2'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS1'then A.SPC_VALUE ELSE 0end)as LAS1_Frequency2 , 9 SUM(casewhen B.spc_item='Acceleration2' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS1'then A.SPC_VALUE ELSE 0end)as LAS1_Acceleration2 , 10 SUM(casewhen B.spc_item='Speed2'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS1'then A.SPC_VALUE ELSE 0end)as LAS1_Speed2, 11 MAX(case WHEN SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS1'then A.update_TIME ELSE NULL end)as LAS1_UPDATETIME, 12 MAX(case WHEN SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS1'then C.TERMINAL_NAME ELSE NULL end)as LAS1_TERMINAL, 13 SUM(casewhen B.spc_item='Frequency2'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS2'then A.SPC_VALUE ELSE 0END)as LAS2_Frequency2, 14 SUM(casewhen B.spc_item='Speed2'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS2'then A.SPC_VALUE ELSE 0END)as LAS2_Speed2, 15 SUM(casewhen B.spc_item='Frequency'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS2'then A.SPC_VALUE ELSE 0END)as LAS2_Frequency, 16 SUM(casewhen B.spc_item='Current2'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS2'then A.SPC_VALUE ELSE 0END)as LAS2_Current2, 17 SUM(casewhen B.spc_item='Current'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS2'then A.SPC_VALUE ELSE 0END)as LAS2_Current, 18 SUM(casewhen B.spc_item='Acceleration' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS2'then A.SPC_VALUE ELSE 0END)as LAS2_Acceleration, 19 SUM(casewhen B.spc_item='Acceleration2'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS2'then A.SPC_VALUE ELSE 0END)as LAS2_Acceleration2, 20 SUM(casewhen B.spc_item='Speed'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS2'then A.SPC_VALUE ELSE 0END)as LAS2_Speed, 21 MAX(case WHEN SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS2'then A.UPDATE_TIME ELSE NULL end)as LAS2_UPDATETIME, 22 MAX(case WHEN SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS2'then C.TERMINAL_NAME ELSE NULL end)as LAS2_TERMINAL, 23 SUM(casewhen B.spc_item='Frequency2'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS3'then A.SPC_VALUE ELSE 0END)as LAS3_Frequency2, 24 SUM(casewhen B.spc_item='Speed2'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS3'then A.SPC_VALUE ELSE 0END)as LAS3_Speed2, 25 SUM(casewhen B.spc_item='Frequency'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS3'then A.SPC_VALUE ELSE 0END)as LAS3_Frequency, 26 SUM(casewhen B.spc_item='Current2'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS3'then A.SPC_VALUE ELSE 0END)as LAS3_Current2, 27 SUM(casewhen B.spc_item='Current'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS3'then A.SPC_VALUE ELSE 0END)as LAS3_Current, 28 SUM(casewhen B.spc_item='Acceleration' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS3'then A.SPC_VALUE ELSE 0END)as LAS3_Acceleration, 29 SUM(casewhen B.spc_item='Acceleration2'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS3'then A.SPC_VALUE ELSE 0END)as LAS3_Acceleration2, 30 SUM(casewhen B.spc_item='Speed'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS3'then A.SPC_VALUE ELSE 0END)as LAS3_Speed, 31 MAX(case WHEN SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS3'then A.UPDATE_TIME ELSE NULL end)as LAS3_UPDATETIME, 32 MAX(case WHEN SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS3'then C.TERMINAL_NAME ELSE NULL end)as LAS3_TERMINAL, 33 SUM(casewhen B.spc_item='Frequency'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS4'then A.SPC_VALUE ELSE 0END)as LAS4_Frequency, 34 SUM(casewhen B.spc_item='Current'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS4'then A.SPC_VALUE ELSE 0END)as LAS4_Current, 35 SUM(casewhen B.spc_item='Acceleration' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS4'then A.SPC_VALUE ELSE 0END)as LAS4_Acceleration, 36 SUM(casewhen B.spc_item='Speed'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS4'then A.SPC_VALUE ELSE 0END)as LAS4_Speed, 37 MAX(case WHEN SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS4'then A.UPDATE_TIME ELSE NULL end)as LAS4_UPDATETIME, 38 MAX(case WHEN SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='LAS4'then C.TERMINAL_NAME ELSE NULL end)as LAS4_TERMINAL, 39 SUM(casewhen B.spc_item='去離子水電阻率'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PECVD'then A.SPC_VALUE ELSE 0END)as"PECVD_去離子水電阻率", 40 SUM(casewhen B.spc_item='右側中部溫度(PV2)'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PECVD'then A.SPC_VALUE ELSE 0END)as"PECVD_右側中部溫度(PV2)", 41 SUM(casewhen B.spc_item='右側後部溫度(PV3)'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PECVD'then A.SPC_VALUE ELSE 0END)as"PECVD_右側後部溫度(PV3)", 42 SUM(casewhen B.spc_item='左側前部溫度(PV1)'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PECVD'then A.SPC_VALUE ELSE 0END)as"PECVD_左側前部溫度(PV1)", 43 SUM(casewhen B.spc_item='本底真空度'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PECVD'then A.SPC_VALUE ELSE 0END)as"PECVD_本底真空度", 44 SUM(casewhen B.spc_item='第二步開始時間' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PECVD'then A.SPC_VALUE ELSE 0END)as"PECVD_第二步開始時間", 45 SUM(casewhen B.spc_item='腔室內溫度(PV4)'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PECVD'then A.SPC_VALUE ELSE 0END)as"PECVD_腔室內溫度(PV4)", 46 MAX(case WHEN SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PECVD'then A.UPDATE_TIME ELSE NULL end)as PECVD_UPDATETIME, 47 MAX(case WHEN SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PECVD'then C.TERMINAL_NAME ELSE NULL end)as PECVD_TERMINAL, 48 SUM(casewhen B.spc_item='COUNT'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_COUNT, 49 SUM(casewhen B.spc_item='HSPEED'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_HSPEED, 50 SUM(casewhen B.spc_item='LSPEED'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_LSPEED, 51 SUM(casewhen B.spc_item='T1AR'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T1AR, 52 SUM(casewhen B.spc_item='T1I'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T1I, 53 SUM(casewhen B.spc_item='T1LIFE' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T1LIFE, 54 SUM(casewhen B.spc_item='T1O2'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T1O2, 55 SUM(casewhen B.spc_item='T1P'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T1P, 56 SUM(casewhen B.spc_item='T1V'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T1V, 57 SUM(casewhen B.spc_item='T2AR'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T2AR, 58 SUM(casewhen B.spc_item='T2I'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T2I, 59 SUM(casewhen B.spc_item='T2LIFE'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T2LIFE, 60 SUM(casewhen B.spc_item='T2O2' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T2O2, 61 SUM(casewhen B.spc_item='T2P'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T2P, 62 SUM(casewhen B.spc_item='T2V'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T2V, 63 SUM(casewhen B.spc_item='T3AR'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T3AR, 64 SUM(casewhen B.spc_item='T3I'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T3I, 65 SUM(casewhen B.spc_item='T3LIFE'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T3LIFE, 66 SUM(casewhen B.spc_item='T3O2'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T3O2, 67 SUM(casewhen B.spc_item='T3P' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T3P, 68 SUM(casewhen B.spc_item='T3V'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T3V, 69 SUM(casewhen B.spc_item='T4AR'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T4AR, 70 SUM(casewhen B.spc_item='T4I'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T4I, 71 SUM(casewhen B.spc_item='T4LIFE'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T4LIFE, 72 SUM(casewhen B.spc_item='T4O2'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T4O2, 73 SUM(casewhen B.spc_item='T4P'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T4P, 74 SUM(casewhen B.spc_item='T4V' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T4V, 75 SUM(casewhen B.spc_item='T5AR'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T5AR, 76 SUM(casewhen B.spc_item='T5I'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T5I, 77 SUM(casewhen B.spc_item='T5LIFE'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T5LIFE, 78 SUM(casewhen B.spc_item='T5P'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T5P, 79 SUM(casewhen B.spc_item='T5V'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T5V, 80 SUM(casewhen B.spc_item='T6AR'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T6AR, 81 SUM(casewhen B.spc_item='T6I' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T6I, 82 SUM(casewhen B.spc_item='T6LIFE'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T6LIFE, 83 SUM(casewhen B.spc_item='T6P'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T6P, 84 SUM(casewhen B.spc_item='T6V'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T6V, 85 SUM(casewhen B.spc_item='T7AR'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T7AR, 86 SUM(casewhen B.spc_item='T7I'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T7I, 87 SUM(casewhen B.spc_item='T7LIFE'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T7LIFE, 88 SUM(casewhen B.spc_item='T7P' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T7P, 89 SUM(casewhen B.spc_item='T7V'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T7V, 90 SUM(casewhen B.spc_item='T8AR'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T8AR, 91 SUM(casewhen B.spc_item='T8I'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T8I, 92 SUM(casewhen B.spc_item='T8LIFE'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T8LIFE, 93 SUM(casewhen B.spc_item='T8P'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T8P, 94 SUM(casewhen B.spc_item='T8V'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_T8V, 95 SUM(casewhen B.spc_item='j1vacuum' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_j1vacuum, 96 SUM(casewhen B.spc_item='j2vacuum'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_j2vacuum, 97 SUM(casewhen B.spc_item='j3vacuum'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_j3vacuum, 98 SUM(casewhen B.spc_item='j4vacuum'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_j4vacuum, 99 SUM(casewhen B.spc_item='t1'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_t1, 100 SUM(casewhen B.spc_item='t2'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_t2, 101 SUM(casewhen B.spc_item='t3'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_t3, 102 SUM(casewhen B.spc_item='t4' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_t4, 103 SUM(casewhen B.spc_item='t5'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_t5, 104 SUM(casewhen B.spc_item='t6'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_t6, 105 SUM(casewhen B.spc_item='t7' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_t7, 106 SUM(casewhen B.spc_item='t8'and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.SPC_VALUE ELSE 0END)as PVD_t8, 107 MAX(case WHEN SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then A.UPDATE_TIME ELSE NULL end)as PVD_UPDATETIME, 108 MAX(case WHEN SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1)='PVD'then C.TERMINAL_NAME ELSE NULL end)as PVD_TERMINAL 109 FROM SAJET.G_SPC A, SAJET.SYS_SPC B, SAJET.SYS_TERMINAL C 110 WHERE B.SPC_ID = A.SPC_ID 111 AND C.TERMINAL_ID = A.TERMINAL_ID 112 AND A.PROCESS_ID NOT IN (100018,100028) 113 AND a.update_time >=sysdate-2 114 GROUP BY A.SERIAL_NUMBER 115 ) para 116 inner join 117 (SELECT C.TERMINAL_NAME, 118 A.SERIAL_NUMBER, 119 A.VOC, 120 A.ISC, 121 A.VPM, 122 A.IPM, 123 A.PM, 124 A.EFF, 125 A.FF, 126 A.RS, 127 A.UPDATE_TIME , 128 dense_rank() OVER (partition by A.SERIAL_NUMBER ORDER BY A.UPDATE_TIME DESC) rank1 129 FROM SAJET.G_TEST_VALUE_MOT A, SAJET.SYS_TERMINAL C 130 WHERE C.TERMINAL_ID = A.TERMINAL_ID AND C.TERMINAL_NAME LIKE 'CET%' 131 ) SOL 132 on para.SERIAL_NUMBER = SOL.SERIAL_NUMBER 133 WHERE SOL.rank1=1;
二、拆解
可以将此SQL拆开来看，简化后如下：
select max from G_SPC 工艺参数表竖表转横标 group by 序号inner joinselect 芯片测试数据 fromG_TEST_VALUE_MOT
典型的列转行再进行join查询，业务端的访问情况：一般会用时间范围做查询条件
以下为2表所占空间,表空间block 为8k
SYS@HEMESDB1(10.1)> select table_name,blocks*8/1024 MB,num_rows from dba_tables where lower(table_name) like '%g_spc%';TABLE_NAME MB NUM_ROWS--------------------------------------------------------------------------------G_SPC 6023.14844 59773957G_SPC_BOX 0 0G_SPC_XBRC 0 0G_SPC_XBSC 0 0G_SPC_XRMC 0 0SYS@HEMESDB1(10.1)>SYS@HEMESDB1(10.1)>SYS@HEMESDB1(10.1)> select table_name,blocks*8/1024 MB,num_rows from dba_tables where upper(table_name) like '%G_TEST_VALUE_MOT%';TABLE_NAME MB NUM_ROWS--------------------------------------------------------------------------------G_TEST_VALUE_MOT 414.6875 1763297
三、优化思路
很明显，瓶颈在G_SPC表
- 针对G_SPC并行 parallel 查询（不可行，因为数据库版本为标准版，需要企业版支持）
- 物化视图（不可行，由于竖表转横表导致复杂查询，物化视图无法快速刷新）
- 分区表（以周或月的方式 将G_SPC分区，使得SQL依照分区进行扫描查询，同样也因为企业版支持受到限制）
用磁盘空间换运行的查询时间，将G_SPC的相关查询作为中间表，定期 insert then update的方式更新，这样即可将查询逻辑简化为2表关联。
四、过程
- create table as 复杂查询 ，创建中间表，创建对应索引
- 创建对应的作业，进行merge的操作，定期更新中间表
- 修改对应程序，使其关联中间表
/*第一部分先创建一个基表 */
1 ALTER session set workarea_size_policy=manual; 2 ALTER session set workarea_size_policy=manual; 3 ALTER session set sort_area_size=214683648; 4 ALTER session set sort_area_size=214683648; 5 ALTER session set sort_area_retained_size=214683648; 6 ALTER session set sort_area_retained_size=214683648; 7 ALTER session set db_file_multiblock_read_count=256; 8 ALTER session set db_file_multiblock_read_count=256; 9 CREATE TABLE sajet.MMV_SPC_FLOW_QUERY1 nologging AS 10 SELECT 11 A.SERIAL_NUMBER, 12 SUM(case when B.spc_item='Speed' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS1' then A.SPC_VALUE ELSE 0 end ) as LAS1_Speed , 13 SUM(case when B.spc_item='Acceleration' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS1' then A.SPC_VALUE ELSE 0 end ) as LAS1_Acceleration , 14 SUM(case when B.spc_item='Frequency' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS1' then A.SPC_VALUE ELSE 0 end ) as LAS1_Frequency , 15 SUM(case when B.spc_item='Current' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS1' then A.SPC_VALUE ELSE 0 end ) as LAS1_Current , 16 SUM(case when B.spc_item='Frequency2' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS1' then A.SPC_VALUE ELSE 0 end ) as LAS1_Frequency2 , 17 SUM(case when B.spc_item='Acceleration2' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS1' then A.SPC_VALUE ELSE 0 end ) as LAS1_Acceleration2 , 18 SUM(case when B.spc_item='Speed2' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS1' then A.SPC_VALUE ELSE 0 end ) as LAS1_Speed2, 19 MAX(case WHEN SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS1' then A.update_TIME ELSE NULL end ) as LAS1_UPDATETIME, 20 MAX(case WHEN SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS1' then C.TERMINAL_NAME ELSE NULL end ) as LAS1_TERMINAL, 21 SUM(case when B.spc_item='Frequency2' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS2' then A.SPC_VALUE ELSE 0 END ) as LAS2_Frequency2, 22 SUM(case when B.spc_item='Speed2' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS2' then A.SPC_VALUE ELSE 0 END ) as LAS2_Speed2, 23 SUM(case when B.spc_item='Frequency' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS2' then A.SPC_VALUE ELSE 0 END ) as LAS2_Frequency, 24 SUM(case when B.spc_item='Current2' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS2' then A.SPC_VALUE ELSE 0 END ) as LAS2_Current2, 25 SUM(case when B.spc_item='Current' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS2' then A.SPC_VALUE ELSE 0 END ) as LAS2_Current, 26 SUM(case when B.spc_item='Acceleration' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS2' then A.SPC_VALUE ELSE 0 END ) as LAS2_Acceleration, 27 SUM(case when B.spc_item='Acceleration2' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS2' then A.SPC_VALUE ELSE 0 END ) as LAS2_Acceleration2, 28 SUM(case when B.spc_item='Speed' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS2' then A.SPC_VALUE ELSE 0 END ) as LAS2_Speed, 29 MAX(case WHEN SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS2' then A.UPDATE_TIME ELSE NULL end ) as LAS2_UPDATETIME, 30 MAX(case WHEN SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS2' then C.TERMINAL_NAME ELSE NULL end ) as LAS2_TERMINAL, 31 SUM(case when B.spc_item='Frequency2' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS3' then A.SPC_VALUE ELSE 0 END ) as LAS3_Frequency2, 32 SUM(case when B.spc_item='Speed2' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS3' then A.SPC_VALUE ELSE 0 END ) as LAS3_Speed2, 33 SUM(case when B.spc_item='Frequency' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS3' then A.SPC_VALUE ELSE 0 END ) as LAS3_Frequency, 34 SUM(case when B.spc_item='Current2' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS3' then A.SPC_VALUE ELSE 0 END ) as LAS3_Current2, 35 SUM(case when B.spc_item='Current' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS3' then A.SPC_VALUE ELSE 0 END ) as LAS3_Current, 36 SUM(case when B.spc_item='Acceleration' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS3' then A.SPC_VALUE ELSE 0 END ) as LAS3_Acceleration, 37 SUM(case when B.spc_item='Acceleration2' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS3' then A.SPC_VALUE ELSE 0 END ) as LAS3_Acceleration2, 38 SUM(case when B.spc_item='Speed' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS3' then A.SPC_VALUE ELSE 0 END ) as LAS3_Speed, 39 MAX(case WHEN SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS3' then A.UPDATE_TIME ELSE NULL end ) as LAS3_UPDATETIME, 40 MAX(case WHEN SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS3' then C.TERMINAL_NAME ELSE NULL end ) as LAS3_TERMINAL, 41 SUM(case when B.spc_item='Frequency' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS4' then A.SPC_VALUE ELSE 0 END ) as LAS4_Frequency, 42 SUM(case when B.spc_item='Current' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS4' then A.SPC_VALUE ELSE 0 END ) as LAS4_Current, 43 SUM(case when B.spc_item='Acceleration' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS4' then A.SPC_VALUE ELSE 0 END ) as LAS4_Acceleration, 44 SUM(case when B.spc_item='Speed' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS4' then A.SPC_VALUE ELSE 0 END ) as LAS4_Speed, 45 MAX(case WHEN SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS4' then A.UPDATE_TIME ELSE NULL end ) as LAS4_UPDATETIME, 46 MAX(case WHEN SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS4' then C.TERMINAL_NAME ELSE NULL end ) as LAS4_TERMINAL, 47 SUM(case when B.spc_item='去離子水電阻率' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PECVD' then A.SPC_VALUE ELSE 0 END ) as "PECVD_去離子水電阻率", 48 SUM(case when B.spc_item='右側中部溫度(PV2)' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PECVD' then A.SPC_VALUE ELSE 0 END ) as "PECVD_右側中部溫度(PV2)", 49 SUM(case when B.spc_item='右側後部溫度(PV3)' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PECVD' then A.SPC_VALUE ELSE 0 END ) as "PECVD_右側後部溫度(PV3)", 50 SUM(case when B.spc_item='左側前部溫度(PV1)' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PECVD' then A.SPC_VALUE ELSE 0 END ) as "PECVD_左側前部溫度(PV1)", 51 SUM(case when B.spc_item='本底真空度' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PECVD' then A.SPC_VALUE ELSE 0 END ) as "PECVD_本底真空度", 52 SUM(case when B.spc_item='第二步開始時間' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PECVD' then A.SPC_VALUE ELSE 0 END ) as "PECVD_第二步開始時間", 53 SUM(case when B.spc_item='腔室內溫度(PV4)' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PECVD' then A.SPC_VALUE ELSE 0 END ) as "PECVD_腔室內溫度(PV4)", 54 MAX(case WHEN SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PECVD' then A.UPDATE_TIME ELSE NULL end ) as PECVD_UPDATETIME, 55 MAX(case WHEN SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PECVD' then C.TERMINAL_NAME ELSE NULL end ) as PECVD_TERMINAL, 56 SUM(case when B.spc_item='COUNT' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_COUNT, 57 SUM(case when B.spc_item='HSPEED' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_HSPEED, 58 SUM(case when B.spc_item='LSPEED' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_LSPEED, 59 SUM(case when B.spc_item='T1AR' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T1AR, 60 SUM(case when B.spc_item='T1I' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T1I, 61 SUM(case when B.spc_item='T1LIFE' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T1LIFE, 62 SUM(case when B.spc_item='T1O2' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T1O2, 63 SUM(case when B.spc_item='T1P' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T1P, 64 SUM(case when B.spc_item='T1V' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T1V, 65 SUM(case when B.spc_item='T2AR' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T2AR, 66 SUM(case when B.spc_item='T2I' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T2I, 67 SUM(case when B.spc_item='T2LIFE' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T2LIFE, 68 SUM(case when B.spc_item='T2O2' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T2O2, 69 SUM(case when B.spc_item='T2P' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T2P, 70 SUM(case when B.spc_item='T2V' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T2V, 71 SUM(case when B.spc_item='T3AR' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T3AR, 72 SUM(case when B.spc_item='T3I' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T3I, 73 SUM(case when B.spc_item='T3LIFE' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T3LIFE, 74 SUM(case when B.spc_item='T3O2' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T3O2, 75 SUM(case when B.spc_item='T3P' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T3P, 76 SUM(case when B.spc_item='T3V' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T3V, 77 SUM(case when B.spc_item='T4AR' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T4AR, 78 SUM(case when B.spc_item='T4I' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T4I, 79 SUM(case when B.spc_item='T4LIFE' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T4LIFE, 80 SUM(case when B.spc_item='T4O2' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T4O2, 81 SUM(case when B.spc_item='T4P' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T4P, 82 SUM(case when B.spc_item='T4V' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T4V, 83 SUM(case when B.spc_item='T5AR' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T5AR, 84 SUM(case when B.spc_item='T5I' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T5I, 85 SUM(case when B.spc_item='T5LIFE' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T5LIFE, 86 SUM(case when B.spc_item='T5P' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T5P, 87 SUM(case when B.spc_item='T5V' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T5V, 88 SUM(case when B.spc_item='T6AR' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T6AR, 89 SUM(case when B.spc_item='T6I' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T6I, 90 SUM(case when B.spc_item='T6LIFE' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T6LIFE, 91 SUM(case when B.spc_item='T6P' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T6P, 92 SUM(case when B.spc_item='T6V' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T6V, 93 SUM(case when B.spc_item='T7AR' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T7AR, 94 SUM(case when B.spc_item='T7I' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T7I, 95 SUM(case when B.spc_item='T7LIFE' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T7LIFE, 96 SUM(case when B.spc_item='T7P' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T7P, 97 SUM(case when B.spc_item='T7V' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T7V, 98 SUM(case when B.spc_item='T8AR' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T8AR, 99 SUM(case when B.spc_item='T8I' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T8I, 100 SUM(case when B.spc_item='T8LIFE' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T8LIFE, 101 SUM(case when B.spc_item='T8P' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T8P, 102 SUM(case when B.spc_item='T8V' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_T8V, 103 SUM(case when B.spc_item='j1vacuum' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_j1vacuum, 104 SUM(case when B.spc_item='j2vacuum' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_j2vacuum, 105 SUM(case when B.spc_item='j3vacuum' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_j3vacuum, 106 SUM(case when B.spc_item='j4vacuum' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_j4vacuum, 107 SUM(case when B.spc_item='t1' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_t1, 108 SUM(case when B.spc_item='t2' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_t2, 109 SUM(case when B.spc_item='t3' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_t3, 110 SUM(case when B.spc_item='t4' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_t4, 111 SUM(case when B.spc_item='t5' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_t5, 112 SUM(case when B.spc_item='t6' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_t6, 113 SUM(case when B.spc_item='t7' AND SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_t7, 114 SUM(case when B.spc_item='t8' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.SPC_VALUE ELSE 0 END ) as PVD_t8, 115 MAX(case WHEN SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then A.UPDATE_TIME ELSE NULL end ) as PVD_UPDATETIME, 116 MAX(case WHEN SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='PVD' then C.TERMINAL_NAME ELSE NULL end ) as PVD_TERMINAL 117 FROM SAJET.G_SPC A, SAJET.SYS_SPC B, SAJET.SYS_TERMINAL C 118 WHERE B.SPC_ID = A.SPC_ID 119 AND C.TERMINAL_ID = A.TERMINAL_ID 120 AND A.PROCESS_ID NOT IN (100018, 100028) 121 GROUP BY A.SERIAL_NUMBER; 122 commit;
alter table SAJET.MMV_SPC_FLOW_QUERY1 add constraint PK_MMV_SPC_QRY_SN primary key (SERIAL_NUMBER);
/*创建索引的方式，可以将以上的分解为如下三步骤，用nologging 与online +并行的方式，并行需要企业版支持*/
CREATE UNIQUE INDEX SAJET.PK_MMV_SPC_QRY_SN ON SAJET.MMV_SPC_FLOW_QUERY1(SERIAL_NUMBER) parallel(degree 12) ONLINE NOLOGGING;
ALTER TABLE SAJET.MMV_SPC_FLOW_QUERY1 ADD CONSTRAINT PK_MMV_SPC_QRY_SN PRIMARY KEY (SERIAL_NUMBER) enable novalidate;
alter TABLE SAJET.MMV_SPC_FLOW_QUERY1 modify constraint PK_MMV_SPC_QRY_SN enable validate; /*进行merge的操作，定期更新中间表，merge的量为sysdate-2 两天*/
MERGE INTO sajet.MMV_SPC_FLOW_QUERY1 f_tb USING ( SELECT A.SERIAL_NUMBER, SUM(case when B.spc_item='Speed' and SUBSTR(C.TERMINAL_NAME,1,INSTR(C.TERMINAL_NAME,'-',1,1)-1) ='LAS1' then A.SPC_VALUE ELSE 0 end ) as LAS1_Speed , /*省去列转行这部分*/ FROM SAJET.G_SPC A, SAJET.SYS_SPC B, SAJET.SYS_TERMINAL C WHERE B.SPC_ID = A.SPC_ID AND C.TERMINAL_ID = A.TERMINAL_ID AND A.PROCESS_ID NOT IN (100018, 100028) AND EXISTS(SELECT * FROM sajet.g_spc g_spc WHERE g_spc.UPDATE_TIME >= SYSDATE-2 AND g_spc.serial_number=a.serial_number) /*两天的merge 增量*/ AND A.SERIAL_NUMBER LIKE 'HE%' GROUP BY A.SERIAL_NUMBER ) final_tb on (f_tb.SERIAL_NUMBER = final_tb.SERIAL_NUMBER) WHEN MATCHED THEN UPDATE SET f_tb.LAS1_SPEED = final_tb.LAS1_SPEED ,f_tb.LAS1_ACCELERATION = final_tb.LAS1_ACCELERATION ,f_tb.LAS1_FREQUENCY = final_tb.LAS1_FREQUENCY ,f_tb.LAS1_CURRENT = final_tb.LAS1_CURRENT ,f_tb.LAS1_FREQUENCY2 = final_tb.LAS1_FREQUENCY2 ,f_tb.LAS1_ACCELERATION2 = final_tb.LAS1_ACCELERATION2 ,f_tb.LAS1_SPEED2 = final_tb.LAS1_SPEED2 ,f_tb.LAS1_UPDATETIME = final_tb.LAS1_UPDATETIME ,f_tb.LAS1_TERMINAL = final_tb.LAS1_TERMINAL ,f_tb.LAS2_FREQUENCY2 = final_tb.LAS2_FREQUENCY2 ,f_tb.LAS2_SPEED2 = final_tb.LAS2_SPEED2 ,f_tb.LAS2_FREQUENCY = final_tb.LAS2_FREQUENCY ,f_tb.LAS2_CURRENT2 = final_tb.LAS2_CURRENT2 ,f_tb.LAS2_CURRENT = final_tb.LAS2_CURRENT ,f_tb.LAS2_ACCELERATION = final_tb.LAS2_ACCELERATION ,f_tb.LAS2_ACCELERATION2 = final_tb.LAS2_ACCELERATION2 ,f_tb.LAS2_SPEED = final_tb.LAS2_SPEED ,f_tb.LAS2_UPDATETIME = final_tb.LAS2_UPDATETIME ,f_tb.LAS2_TERMINAL = final_tb.LAS2_TERMINAL ,f_tb.LAS3_FREQUENCY2 = final_tb.LAS3_FREQUENCY2 ,f_tb.LAS3_SPEED2 = final_tb.LAS3_SPEED2 ,f_tb.LAS3_FREQUENCY = final_tb.LAS3_FREQUENCY ,f_tb.LAS3_CURRENT2 = final_tb.LAS3_CURRENT2 ,f_tb.LAS3_CURRENT = final_tb.LAS3_CURRENT ,f_tb.LAS3_ACCELERATION = final_tb.LAS3_ACCELERATION ,f_tb.LAS3_ACCELERATION2 = final_tb.LAS3_ACCELERATION2 ,f_tb.LAS3_SPEED = final_tb.LAS3_SPEED ,f_tb.LAS3_UPDATETIME = final_tb.LAS3_UPDATETIME ,f_tb.LAS3_TERMINAL = final_tb.LAS3_TERMINAL ,f_tb.LAS4_FREQUENCY = final_tb.LAS4_FREQUENCY ,f_tb.LAS4_CURRENT = final_tb.LAS4_CURRENT ,f_tb.LAS4_ACCELERATION = final_tb.LAS4_ACCELERATION ,f_tb.LAS4_SPEED = final_tb.LAS4_SPEED ,f_tb.LAS4_UPDATETIME = final_tb.LAS4_UPDATETIME ,f_tb.LAS4_TERMINAL = final_tb.LAS4_TERMINAL ,f_tb."PECVD_去離子水電阻率" = final_tb."PECVD_去離子水電阻率" ,f_tb."PECVD_右側中部溫度(PV2)" = final_tb."PECVD_右側中部溫度(PV2)" ,f_tb."PECVD_右側後部溫度(PV3)" = final_tb."PECVD_右側後部溫度(PV3)" ,f_tb."PECVD_左側前部溫度(PV1)" = final_tb."PECVD_左側前部溫度(PV1)" ,f_tb."PECVD_本底真空度" = final_tb."PECVD_本底真空度" ,f_tb."PECVD_第二步開始時間" = final_tb."PECVD_第二步開始時間" ,f_tb."PECVD_腔室內溫度(PV4)" = final_tb."PECVD_腔室內溫度(PV4)" ,f_tb.PECVD_UPDATETIME = final_tb.PECVD_UPDATETIME ,f_tb.PECVD_TERMINAL = final_tb.PECVD_TERMINAL ,f_tb.PVD_COUNT = final_tb.PVD_COUNT ,f_tb.PVD_HSPEED = final_tb.PVD_HSPEED ,f_tb.PVD_LSPEED = final_tb.PVD_LSPEED ,f_tb.PVD_T1AR = final_tb.PVD_T1AR ,f_tb.PVD_T1I = final_tb.PVD_T1I ,f_tb.PVD_T1LIFE = final_tb.PVD_T1LIFE ,f_tb.PVD_T1O2 = final_tb.PVD_T1O2 ,f_tb.PVD_T1P = final_tb.PVD_T1P ,f_tb.PVD_T1V = final_tb.PVD_T1V ,f_tb.PVD_T2AR = final_tb.PVD_T2AR ,f_tb.PVD_T2I = final_tb.PVD_T2I ,f_tb.PVD_T2LIFE = final_tb.PVD_T2LIFE ,f_tb.PVD_T2O2 = final_tb.PVD_T2O2 ,f_tb.PVD_T2P = final_tb.PVD_T2P ,f_tb.PVD_T2V = final_tb.PVD_T2V ,f_tb.PVD_T3AR = final_tb.PVD_T3AR ,f_tb.PVD_T3I = final_tb.PVD_T3I ,f_tb.PVD_T3LIFE = final_tb.PVD_T3LIFE ,f_tb.PVD_T3O2 = final_tb.PVD_T3O2 ,f_tb.PVD_T3P = final_tb.PVD_T3P ,f_tb.PVD_T3V = final_tb.PVD_T3V ,f_tb.PVD_T4AR = final_tb.PVD_T4AR ,f_tb.PVD_T4I = final_tb.PVD_T4I ,f_tb.PVD_T4LIFE = final_tb.PVD_T4LIFE ,f_tb.PVD_T4O2 = final_tb.PVD_T4O2 ,f_tb.PVD_T4P = final_tb.PVD_T4P ,f_tb.PVD_T4V = final_tb.PVD_T4V ,f_tb.PVD_T5AR = final_tb.PVD_T5AR ,f_tb.PVD_T5I = final_tb.PVD_T5I ,f_tb.PVD_T5LIFE = final_tb.PVD_T5LIFE ,f_tb.PVD_T5P = final_tb.PVD_T5P ,f_tb.PVD_T5V = final_tb.PVD_T5V ,f_tb.PVD_T6AR = final_tb.PVD_T6AR ,f_tb.PVD_T6I = final_tb.PVD_T6I ,f_tb.PVD_T6LIFE = final_tb.PVD_T6LIFE ,f_tb.PVD_T6P = final_tb.PVD_T6P ,f_tb.PVD_T6V = final_tb.PVD_T6V ,f_tb.PVD_T7AR = final_tb.PVD_T7AR ,f_tb.PVD_T7I = final_tb.PVD_T7I ,f_tb.PVD_T7LIFE = final_tb.PVD_T7LIFE ,f_tb.PVD_T7P = final_tb.PVD_T7P ,f_tb.PVD_T7V = final_tb.PVD_T7V ,f_tb.PVD_T8AR = final_tb.PVD_T8AR ,f_tb.PVD_T8I = final_tb.PVD_T8I ,f_tb.PVD_T8LIFE = final_tb.PVD_T8LIFE ,f_tb.PVD_T8P = final_tb.PVD_T8P ,f_tb.PVD_T8V = final_tb.PVD_T8V ,f_tb.PVD_J1VACUUM = final_tb.PVD_J1VACUUM ,f_tb.PVD_J2VACUUM = final_tb.PVD_J2VACUUM ,f_tb.PVD_J3VACUUM = final_tb.PVD_J3VACUUM ,f_tb.PVD_J4VACUUM = final_tb.PVD_J4VACUUM ,f_tb.PVD_T1 = final_tb.PVD_T1 ,f_tb.PVD_T2 = final_tb.PVD_T2 ,f_tb.PVD_T3 = final_tb.PVD_T3 ,f_tb.PVD_T4 = final_tb.PVD_T4 ,f_tb.PVD_T5 = final_tb.PVD_T5 ,f_tb.PVD_T6 = final_tb.PVD_T6 ,f_tb.PVD_T7 = final_tb.PVD_T7 ,f_tb.PVD_T8 = final_tb.PVD_T8 ,f_tb.PVD_UPDATETIME = final_tb.PVD_UPDATETIME ,f_tb.PVD_TERMINAL = final_tb.PVD_TERMINAL WHEN NOT MATCHED THEN INSERT ( f_tb.serial_number /*省略字段部分*/ ) values ( final_tb.serial_number /*省略字段部分*/ );
/*将merge语句创建存储过程*/
CREATE OR REPLACE PROCEDURE Refresh_MMV_SPC_FLOW_QUERY1(Flag IN NUMBER) IS
TmpVar NUMBER;
I INT;
BEGIN
/*把刚才的存储过程，并加入job定期执行即可。*/
/*比如，可以定期每30分钟执行一次*/
commit;
END;
最后更改横版流程卡程序里的SQL，套用中间表即可 MMV_SPC_FLOW_QUERY1
SELECT cvd_m.*,para.*,SOL.* FROM ( SELECT A.PECVD_RECID, A.UNLOAD_START_DATE, B.MACHINE_DESC2 AS PEB_MACHINE_ID, C.MACHINE_DESC2 AS WSHA_MACHINE_ID, D.MACHINE_DESC2 AS PHF_MACHINE_ID, E.MACHINE_DESC2 AS PE_MACHINE_ID, A.WSHA_START_DATE, A.WSHA_END_DATE, A.PHF_START_DATE, A.PHF_END_DATE, A.PHF_TEMPER, A.PE_START_DATE, A.PE_END_DATE, A.ITEM_1, A.ITEM_2, A.ITEM_3, A.ITEM_4, A.ITEM_5, A.ITEM_6, A.ITEM_7, A.Item_9, A.Item_10, A.Machine_Date, A.Machine_No, A.Machine_Seq, F.EMP_NAME, A.Update_Time, A.Used_Count FROM SAJET.APOLLO_PECVD_DATA A, SAJET.SYS_MACHINE B, SAJET.SYS_MACHINE C, SAJET.SYS_MACHINE D, SAJET.SYS_MACHINE E, SAJET.SYS_EMP F WHERE A.PEB_MACHINE_ID = B.MACHINE_ID AND A.WSHA_MACHINE_ID = C.MACHINE_ID AND A.PHF_MACHINE_ID = D.MACHINE_ID AND A.PE_MACHINE_ID = E.MACHINE_ID AND A.ITEM_8 = F.EMP_ID )cvd_m INNER JOIN sajet.apollo_pecvd_sn cvd_s ON cvd_m.pecvd_recid = cvd_s.pecvd_recid LEFT JOIN (SELECT CC.TERMINAL_NAME, AA.SERIAL_NUMBER, AA.VOC, AA.ISC, AA.VPM, AA.IPM, AA.PM, AA.EFF, AA.FF, AA.RS, AA.UPDATE_TIME , dense_rank() OVER (partition by AA.SERIAL_NUMBER ORDER BY AA.UPDATE_TIME DESC) rank1 FROM SAJET.G_TEST_VALUE_MOT AA, SAJET.SYS_TERMINAL CC WHERE CC.TERMINAL_ID = AA.TERMINAL_ID AND CC.TERMINAL_NAME LIKE 'CET%' ) SOL on cvd_s.SERIAL_NUMBER = SOL.SERIAL_NUMBER LEFT JOIN Sajet.MMV_SPC_FLOW_QUERY1 para ON para.serial_number=cvd_s.serial_number WHERE SOL.rank1=1 [AND cvd_m.update_time BETWEEN :PARAM1] ORDER BY cvd_m.update_time ASC
涉及140余字段，2000+行的查询结果量，优化前后为100+秒至10秒内。
后续的数据量如果还有进一步的提升，还可以用通用的优化手段：
- 针对大型的列转行的源表，按时间对表进行分区
- 物化视图，关联最后的结果集。同时可以进行并行的物化视图刷新，写进job里依照顺序执行。 并行刷新部分可以参考：MV_Refresh_Parallel.pdf
Over.
