程式SQL优化规范.ppt

ORACLE的解析器按照從右到左的順序處理FROM子句中的表名 ,所以 由小至大去 Table 例如: 表 TAB1 16,384 條記錄 表 TAB2 1 條記錄 ? 選擇TAB2作為基礎表 (最好的方法) select count(*) from tab1,tab2 執行時間0.96秒 選擇TAB2作為基礎表 (不佳的方法) select count(*) from tab2,tab1 執行時間26.09秒 煙台DMDIII WEB有“ Yield rate chart-by one day”(查詢一天內各個工站生產數量及良率並Show出良率圖表)一支報 表,Sql如下: (低效)10分38秒 SELECT r.group_name, SUM (r.pass_qty) + SUM (r.fail_qty) total_qty,SUM (r.pass_qty) pass_qty, SUM (r.fail_qty) fail_qty,TO_CHAR ( 100* SUM (r.pass_qty) / DECODE ((SUM (r.pass_qty) + SUM (r.fail_qty)),0, 1(SUM (r.pass_qty) + SUM (r.fail_qty))), 990.99 ) yield_rate, SUM (r.repass_qty) repass_qty, SUM (r.refail_qty) refail_qty, g.group_code FROM sfism4.r_mo_base_t kk,sfism4.r_station_rec_t r,(SELECT group_code, group_name FROM sfis1.c_group_config_t) g WHERE r.work_id =TO_CHAR (TO_DATE (2006/01/19, yyyy-mm-dd), yyyymmdd)|| 00 AND r.work_id =TO_CHAR (TO_DATE (2006/01/19, yyyy-mm-dd), yyyymmdd)|| 25 AND r.group_name PTH INSPECTAND r.line_name = PTH0703AND kk.cust_no = FOXCONN AND r.mo_number = kk.mo_numberAND kk.mo_type = NORMALAND r.group_name = g.group_name AND r.group_name IN(SMT INSPECT,INIT,TOUCH UP,ICT,FFT,FVI,OQM,SI,IFT) GROUP BY r.group_name, g.group_code ORDER BY g.group_code 優化後SQL語句如下: (高效)1秒 SELECT r.group_name, SUM (r.pass_qty) + SUM (r.fail_qty) total_qty,SUM (r.pass_qty) pass_qty, SUM (r.fail_qty) fail_qty,TO_CHAR ( 100* SUM (r.pass_qty) / DECODE ((SUM (r.pass_qty) + SUM (r.fail_qty)),0, 1(SUM (r.pass_qty) + SUM (r.fail_qty)) ), 990.99 ) yield_rate, SUM (r.repass_qty) repass_qty, SUM (r.refail_qty) refail_qty, g.group_code FROM sfism4.r_station_rec_t r,sfism4.r_mo_base_t kk,(SELECT group_code, group_name FROM sfis1.c_group_config_t) g WHERE r.work_id =TO_CHAR (TO_DATE (2006/01/19, yyyy-mm-dd), yyyymmdd)|| 00