求助！！！关联视图较多的SQL查询优化
SQL语句，其中apps.yw_erp_yoflhsample_v和apps.yw_erp_yofggsample_v为视图
SQL> explain plan for
select *
from
(select a.*,
b.attribute1 batch_no,
&nb...显示全部
SQL语句，其中apps.yw_erp_yoflhsample_v和apps.yw_erp_yofggsample_v为视图 SQL> explain plan for select * from (select a.*, b.attribute1 batch_no, b.sample_no ggsample, b.date_drawn1 ggsamdate, b.ggggd, b.gghp, b.gghgd, b.ggsjk, b.ggzz, b.gghdp, b.ggysp, b.gorder from apps.yw_erp_yoflhsample_v a , apps.yw_erp_yofggsample_v b where a.attribute1(+)=b.attribute1 and a.vorder(+)=b.gorder and b.attribute1 between 'YD_20151026202' and 'YD_20151026202' union all select a.*, b.attribute1 batch_no, b.sample_no ggsample, b.date_drawn1 ggsamdate, b.ggggd, b.gghp, b.gghgd, b.ggsjk, b.ggzz, b.gghdp, b.ggysp, b.gorder from apps.yw_erp_yoflhsample_v a , apps.yw_erp_yofggsample_v b where a.attribute1=b.attribute1(+) and a.vorder=b.gorder(+) and b.attribute1 is null and a.attribute1 between 'YD_20151026202' and 'YD_20151026202') order by nvl(attribute1,batch_no); 该sql的执行计划： PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost | ------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 48 | 71088 | | 84572 | | 1 | SORT ORDER BY | | 48 | 71088 | | 84572 | | 2 | VIEW | | 48 | 71088 | | 84538 | | 3 | UNION-ALL | | | | | | | 4 | HASH JOIN OUTER | | 47 | 69607 | | 42269 | | 5 | VIEW | YW_ERP_YOFGGSAMPLE_V | 47 | 13019 | | 5703 | | 6 | WINDOW BUFFER | | 47 | 9259 | | 5703 | | 7 | SORT GROUP BY | | 47 | 9259 | | 5703 | | 8 | NESTED LOOPS | | 47 | 9259 | | 5677 | | 9 | NESTED LOOPS | | 47 | 8836 | | 5630 | | 10 | FILTER | | | | | | | 11 | NESTED LOOPS OUTER | | | | | | | 12 | NESTED LOOPS | | 47 | 7520 | | 5536 | | 13 | HASH JOIN | | 14255 | 2115K| | 5536 | | 14 | TABLE ACCESS FULL | GMD_QC_TESTS_B | 3027 | 78702 | | 10 | | 15 | TABLE ACCESS BY INDEX ROWID | GMD_RESULTS | 17 | 255 | | 4 | | 16 | NESTED LOOPS | | 14255 | 1754K| | 5523 | | 17 | NESTED LOOPS | | 816 | 90576 | | 2259 | | 18 | HASH JOIN | | 817 | 87419 | | 2259 | | 19 | NESTED LOOPS OUTER | | 817 | 80066 | | 1866 | | 20 | FILTER | | | | | | | 21 | HASH JOIN OUTER | | | | | | | 22 | INLIST ITERATOR | | | | | | | 23 | TABLE ACCESS BY INDEX ROWID | GMD_SAMPLES | 817 | 61275 | | 1861 | | 24 | INDEX RANGE SCAN | GMD_SAMPLES_U1 | 3267 | | | 567 | | 25 | TABLE ACCESS FULL | GMD_OPERATIONS_B | 176 | 3344 | | 4 | | 26 | INDEX UNIQUE SCAN | IC_ITEM_MST_B_PK | 1 | 4 | | | | 27 | TABLE ACCESS FULL | GMD_EVENT_SPEC_DISP | 312K| 2749K| | 388 | | 28 | INDEX UNIQUE SCAN | GMD_SPECIFICATIONS_B_PK | 1 | 4 | | | | 29 | INDEX RANGE SCAN | GMD_RESULTS_N1 | 17 | | | 2 | | 30 | INDEX UNIQUE SCAN | GMD_SPEC_TESTS_B_PK | 1 | 8 | | | | 31 | TABLE ACCESS BY INDEX ROWID | GME_BATCH_HEADER | 1 | 23 | | 1 | | 32 | INDEX UNIQUE SCAN | GME_BATCH_HEADER_PK | 1 | | | | | 33 | INDEX UNIQUE SCAN | GMD_SAMPLING_EVENTS_PK | 1 | 5 | | 1 | | 34 | INLIST ITERATOR | | | | | | | 35 | INDEX RANGE SCAN | GMD_SAMPLE_SPEC_DISP_N1 | 1 | 9 | | 1 | | 36 | VIEW | YW_ERP_YOFLHSAMPLE_V | 1 | 1204 | | 36566 | | 37 | HASH JOIN | | 1 | 1228 | | 36566 | | 38 | VIEW | | 1 | 57 | | 227 | | 39 | SORT GROUP BY | | 1 | 57 | | 227 | | 40 | VIEW | YW_OPM_YOF_DAILY_V | 611 | 34827 | | 200 | | 41 | SORT GROUP BY | | 611 | 62322 | | 200 | | 42 | VIEW | YW_OPM_BATCH_V | 611 | 62322 | | 167 | | 43 | SORT ORDER BY | | 611 | 177K| | 167 | | 44 | HASH JOIN | | 611 | 177K| | 117 | | 45 | FILTER | | | | | | | 46 | NESTED LOOPS OUTER | | | | | | | 47 | FILTER | | | | | | | 48 | NESTED LOOPS OUTER | | | | | | | 49 | NESTED LOOPS | | 1 | 206 | | 16 | | 50 | MERGE JOIN CARTESIAN | | 1 | 194 | | 15 | | 51 | NESTED LOOPS | | 1 | 175 | | 11 | | 52 | NESTED LOOPS | | 1 | 166 | | 10 | | 53 | NESTED LOOPS | | 1 | 158 | | 10 | | 54 | NESTED LOOPS | | 1 | 142 | | 9 | | 55 | NESTED LOOPS | | 1 | 125 | | 6 | | 56 | NESTED LOOPS | | 1 | 84 | | 5 | | 57 | INLIST ITERATOR | | | | | | | 58 | TABLE ACCESS BY INDEX ROWID| GME_BATCH_HEADER | 1 | 71 | | 4 | | 59 | INDEX RANGE SCAN | GME_BATCH_HEADER_U1 | 1 | | | 3 | | 60 | TABLE ACCESS BY INDEX ROWID | GMD_RECIPE_VALIDITY_RULES | 1 | 13 | | 1 | | 61 | INDEX UNIQUE SCAN | GMD_RECIPE_VALIDITY_RULES_PK | 1 | | | | | 62 | TABLE ACCESS BY INDEX ROWID | GMD_RECIPES_B | 1 | 41 | | 1 | | 63 | INDEX UNIQUE SCAN | GMD_RECIPES_B_PK | 1 | | | | | 64 | TABLE ACCESS BY INDEX ROWID | GME_BATCH_STEPS | 3 | 51 | | 3 | | 65 | INDEX RANGE SCAN | GME_BATCH_STEPS_U1 | 3 | | | 2 | | 66 | TABLE ACCESS BY INDEX ROWID | FM_FORM_MST_B | 1 | 16 | | 1 | | 67 | INDEX UNIQUE SCAN | FM_FORM_MST_B_PK | 1 | | | | | 68 | INDEX UNIQUE SCAN | FM_FORM_MST_TL_PK | 1 | 8 | | | | 69 | TABLE ACCESS BY INDEX ROWID | SY_ORGN_MST_B | 1 | 9 | | 1 | | 70 | INDEX UNIQUE SCAN | SY_ORGN_MST_B_PK | 1 | | | | | 71 | BUFFER SORT | | 176 | 3344 | | 14 | | 72 | TABLE ACCESS FULL | GMD_OPERATIONS_B | 176 | 3344 | | 4 | | 73 | TABLE ACCESS BY INDEX ROWID | FM_ROUT_DTL | 1 | 12 | | 1 | | 74 | INDEX RANGE SCAN | FM_ROUT_DTLI2 | 1 | | | | | 75 | TABLE ACCESS BY INDEX ROWID | GMD_RECIPE_STEP_MATERIALS | 251 | 3263 | | 28 | | 76 | INDEX RANGE SCAN | GMD_RECIPE_STEP_MATERIALS_N1 | 287 | | | 1 | | 77 | TABLE ACCESS BY INDEX ROWID | GME_MATERIAL_DETAILS | 27 | 1080 | | 2 | | 78 | INDEX RANGE SCAN | GME_MATERIAL_DETAILS_N3 | 39 | | | 1 | | 79 | TABLE ACCESS FULL | IC_ITEM_MST_B | 4394 | 167K| | 27 | | 80 | VIEW | | 2805 | 3207K| | 36338 | | 81 | WINDOW SORT | | 2805 | 3172K| 7496K| 36338 | | 82 | VIEW | YW_OPM_GMD_ANALYSE_V2 | 2805 | 3172K| | 35850 | | 83 | SORT GROUP BY | | 2805 | 577K| | 35850 | | 84 | HASH JOIN | | 2805 | 577K| | 35742 | | 85 | NESTED LOOPS | | 2805 | 553K| | 35681 | | 86 | HASH JOIN | | 855K| 158M| | 35681 | | 87 | TABLE ACCESS FULL | GMD_QC_TESTS_B | 3027 | 78702 | | 10 | | 88 | HASH JOIN | | 855K| 137M| 7792K| 35532 | | 89 | NESTED LOOPS | | 48931 | 7215K| | 5311 | | 90 | HASH JOIN | | 49007 | 7035K| 6416K| 5311 | | 91 | TABLE ACCESS FULL | GMD_EVENT_SPEC_DISP | 312K| 2749K| | 388 | | 92 | FILTER | | | | | | | 93 | HASH JOIN OUTER | | | | | | | 94 | NESTED LOOPS OUTER | | 49007 | 4737K| | 4597 | | 95 | FILTER | | | | | | | 96 | HASH JOIN OUTER | | | | | | | 97 | HASH JOIN | | 49007 | 3445K| 3784K| 3947 | | 98 | TABLE ACCESS FULL | GMD_SAMPLES | 49007 | 3206K| | 2913 | | 99 | INDEX FULL SCAN | GMD_SAMPLING_EVENTS_PK | 314K| 1536K| | 893 | | 100 | TABLE ACCESS FULL | GME_BATCH_HEADER | 121K| 2740K| | 522 | | 101 | INDEX UNIQUE SCAN | GMD_OPERATIONS_B_PK | 1 | 4 | | | | 102 | TABLE ACCESS FULL | IC_ITEM_MST_B | 4394 | 167K| | 27 | | 103 | INDEX UNIQUE SCAN | GMD_SPECIFICATIONS_B_PK | 1 | 4 | | | | 104 | TABLE ACCESS FULL | GMD_RESULTS | 9588K| 155M| | 25875 | | 105 | INDEX UNIQUE SCAN | GMD_SPEC_TESTS_B_PK | 1 | 8 | | | | 106 | INDEX FULL SCAN | GMD_SAMPLE_SPEC_DISP_N1 | 327K| 2879K| | 26 | | 107 | FILTER | | | | | | | 108 | HASH JOIN OUTER | | | | | | | 109 | HASH JOIN | | 1 | 1228 | | 36566 | | 110 | VIEW | | 1 | 57 | | 227 | | 111 | SORT GROUP BY | | 1 | 57 | | 227 | | 112 | VIEW | YW_OPM_YOF_DAILY_V | 611 | 34827 | | 200 | | 113 | SORT GROUP BY | | 611 | 62322 | | 200 | | 114 | VIEW | YW_OPM_BATCH_V | 611 | 62322 | | 167 | | 115 | SORT ORDER BY | | 611 | 177K| | 167 | | 116 | HASH JOIN | | 611 | 177K| | 117 | | 117 | FILTER | | | | | | | 118 | NESTED LOOPS OUTER | | | | | | | 119 | FILTER | | | | | | | 120 | NESTED LOOPS OUTER | | | | | | | 121 | NESTED LOOPS | | 1 | 206 | | 16 | | 122 | MERGE JOIN CARTESIAN | | 1 | 194 | | 15 | | 123 | NESTED LOOPS | | 1 | 175 | | 11 | | 124 | NESTED LOOPS | | 1 | 166 | | 10 | | 125 | NESTED LOOPS | | 1 | 158 | | 10 | | 126 | NESTED LOOPS | | 1 | 142 | | 9 | | 127 | NESTED LOOPS | | 1 | 125 | | 6 | | 128 | NESTED LOOPS | | 1 | 84 | | 5 | | 129 | INLIST ITERATOR | | | | | | | 130 | TABLE ACCESS BY INDEX ROWID| GME_BATCH_HEADER | 1 | 71 | | 4 | | 131 | INDEX RANGE SCAN | GME_BATCH_HEADER_U1 | 1 | | | 3 | | 132 | TABLE ACCESS BY INDEX ROWID | GMD_RECIPE_VALIDITY_RULES | 1 | 13 | | 1 | | 133 | INDEX UNIQUE SCAN | GMD_RECIPE_VALIDITY_RULES_PK | 1 | | | | | 134 | TABLE ACCESS BY INDEX ROWID | GMD_RECIPES_B | 1 | 41 | | 1 | | 135 | INDEX UNIQUE SCAN | GMD_RECIPES_B_PK | 1 | | | | | 136 | TABLE ACCESS BY INDEX ROWID | GME_BATCH_STEPS | 3 | 51 | | 3 | | 137 | INDEX RANGE SCAN | GME_BATCH_STEPS_U1 | 3 | | | 2 | | 138 | TABLE ACCESS BY INDEX ROWID | FM_FORM_MST_B | 1 | 16 | | 1 | | 139 | INDEX UNIQUE SCAN | FM_FORM_MST_B_PK | 1 | | | | | 140 | INDEX UNIQUE SCAN | FM_FORM_MST_TL_PK | 1 | 8 | | | | 141 | TABLE ACCESS BY INDEX ROWID | SY_ORGN_MST_B | 1 | 9 | | 1 | | 142 | INDEX UNIQUE SCAN | SY_ORGN_MST_B_PK | 1 | | | | | 143 | BUFFER SORT | | 176 | 3344 | | 14 | | 144 | TABLE ACCESS FULL | GMD_OPERATIONS_B | 176 | 3344 | | 4 | | 145 | TABLE ACCESS BY INDEX ROWID | FM_ROUT_DTL | 1 | 12 | | 1 | | 146 | INDEX RANGE SCAN | FM_ROUT_DTLI2 | 1 | | | | | 147 | TABLE ACCESS BY INDEX ROWID | GMD_RECIPE_STEP_MATERIALS | 251 | 3263 | | 28 | | 148 | INDEX RANGE SCAN | GMD_RECIPE_STEP_MATERIALS_N1 | 287 | | | 1 | | 149 | TABLE ACCESS BY INDEX ROWID | GME_MATERIAL_DETAILS | 27 | 1080 | | 2 | | 150 | INDEX RANGE SCAN | GME_MATERIAL_DETAILS_N3 | 39 | | | 1 | | 151 | TABLE ACCESS FULL | IC_ITEM_MST_B | 4394 | 167K| | 27 | | 152 | VIEW | | 2805 | 3207K| | 36338 | | 153 | WINDOW SORT | | 2805 | 3172K| 7496K| 36338 | | 154 | VIEW | YW_OPM_GMD_ANALYSE_V2 | 2805 | 3172K| | 35850 | | 155 | SORT GROUP BY | | 2805 | 577K| | 35850 | | 156 | HASH JOIN | | 2805 | 577K| | 35742 | | 157 | NESTED LOOPS | | 2805 | 553K| | 35681 | | 158 | HASH JOIN | | 855K| 158M| | 35681 | | 159 | TABLE ACCESS FULL | GMD_QC_TESTS_B | 3027 | 78702 | | 10 | | 160 | HASH JOIN | | 855K| 137M| 7792K| 35532 | | 161 | NESTED LOOPS | | 48931 | 7215K| | 5311 | | 162 | HASH JOIN | | 49007 | 7035K| 6416K| 5311 | | 163 | TABLE ACCESS FULL | GMD_EVENT_SPEC_DISP | 312K| 2749K| | 388 | | 164 | FILTER | | | | | | | 165 | HASH JOIN OUTER | | | | | | | 166 | NESTED LOOPS OUTER | | 49007 | 4737K| | 4597 | | 167 | FILTER | | | | | | | 168 | HASH JOIN OUTER | | | | | | | 169 | HASH JOIN | | 49007 | 3445K| 3784K| 3947 | | 170 | TABLE ACCESS FULL | GMD_SAMPLES | 49007 | 3206K| | 2913 | | 171 | INDEX FULL SCAN | GMD_SAMPLING_EVENTS_PK | 314K| 1536K| | 893 | | 172 | TABLE ACCESS FULL | GME_BATCH_HEADER | 121K| 2740K| | 522 | | 173 | INDEX UNIQUE SCAN | GMD_OPERATIONS_B_PK | 1 | 4 | | | | 174 | TABLE ACCESS FULL | IC_ITEM_MST_B | 4394 | 167K| | 27 | | 175 | INDEX UNIQUE SCAN | GMD_SPECIFICATIONS_B_PK | 1 | 4 | | | | 176 | TABLE ACCESS FULL | GMD_RESULTS | 9588K| 155M| | 25875 | | 177 | INDEX UNIQUE SCAN | GMD_SPEC_TESTS_B_PK | 1 | 8 | | | | 178 | INDEX FULL SCAN | GMD_SAMPLE_SPEC_DISP_N1 | 327K| 2879K| | 26 | | 179 | VIEW | YW_ERP_YOFGGSAMPLE_V | 47 | 13019 | | 5703 | | 180 | WINDOW BUFFER | | 47 | 9259 | | 5703 | | 181 | SORT GROUP BY | | 47 | 9259 | | 5703 | | 182 | NESTED LOOPS | | 47 | 9259 | | 5677 | | 183 | NESTED LOOPS | | 47 | 8836 | | 5630 | | 184 | FILTER | | | | | | | 185 | NESTED LOOPS OUTER | | | | | | | 186 | NESTED LOOPS | | 47 | 7520 | | 5536 | | 187 | HASH JOIN | | 14255 | 2115K| | 5536 | | 188 | TABLE ACCESS FULL | GMD_QC_TESTS_B | 3027 | 78702 | | 10 | | 189 | TABLE ACCESS BY INDEX ROWID | GMD_RESULTS | 17 | 255 | | 4 | | 190 | NESTED LOOPS | | 14255 | 1754K| | 5523 | | 191 | NESTED LOOPS | | 816 | 90576 | | 2259 | | 192 | HASH JOIN | | 817 | 87419 | | 2259 | | 193 | NESTED LOOPS OUTER | | 817 | 80066 | | 1866 | | 194 | FILTER | | | | | | | 195 | HASH JOIN OUTER | | | | | | | 196 | INLIST ITERATOR | | | | | | | 197 | TABLE ACCESS BY INDEX ROWID | GMD_SAMPLES | 817 | 61275 | | 1861 | | 198 | INDEX RANGE SCAN | GMD_SAMPLES_U1 | 3267 | | | 567 | | 199 | TABLE ACCESS FULL | GMD_OPERATIONS_B | 176 | 3344 | | 4 | | 200 | INDEX UNIQUE SCAN | IC_ITEM_MST_B_PK | 1 | 4 | | | | 201 | TABLE ACCESS FULL | GMD_EVENT_SPEC_DISP | 312K| 2749K| | 388 | | 202 | INDEX UNIQUE SCAN | GMD_SPECIFICATIONS_B_PK | 1 | 4 | | | | 203 | INDEX RANGE SCAN | GMD_RESULTS_N1 | 17 | | | 2 | | 204 | INDEX UNIQUE SCAN | GMD_SPEC_TESTS_B_PK | 1 | 8 | | | | 205 | TABLE ACCESS BY INDEX ROWID | GME_BATCH_HEADER | 1 | 23 | | 1 | | 206 | INDEX UNIQUE SCAN | GME_BATCH_HEADER_PK | 1 | | | | | 207 | INDEX UNIQUE SCAN | GMD_SAMPLING_EVENTS_PK | 1 | 5 | | 1 | | 208 | INLIST ITERATOR | | | | | | | 209 | INDEX RANGE SCAN | GMD_SAMPLE_SPEC_DISP_N1 | 1 | 9 | | 1 | ------------------------------------------------------------------------------------------------------------------------------- 该sql响应时间约1分钟，请问各位大侠帮忙看一下，有什么优化建议提供一些，如果执行计划看不清请查看附件。
附件：
SQL优化.txt (28.33 KB)