一段sql陳述句中內嵌了很多select，left join等，資料庫資料也特別大，該怎么進行優化？
采用臨時表的話，怎么進行連接啊，建立索引啊之類的。真萌新，真不懂，求大神指教。
uj5u.com熱心網友回復：
貼出陳述句，執行計劃，還有每個表的資料量，這樣大神們來分析的時候，我們也可以跟著一起學習了uj5u.com熱心網友回復：
感謝大佬的意見，我會改正的！uj5u.com熱心網友回復：
代碼如下：select aa.*,tt.result1 gp_result, tt.create_time gp_sj,tt.create_user gp_r,tt.xb_user,DECODE(tt.reserved7,'A','早班','B','中班','C','夜班') gp_bz ,asr.* from
(select * from
(select * from
(select a1.*,TO_CHAR(a1.trans_time,'mm')
||'月' mon,
TO_CHAR(a1.trans_time,'yyyy/mm/dd') days,
TO_CHAR(a1.trans_time,'hh24:mi:ss') times,b1.* from
(select a.barcode, a.create_time,a.create_user, a.reserved5 ,(case when a.reserved7='A' THEN '早班' when a.reserved7='B' THEN '中班' else '晚班' end) as zubie,
a.description_s,a.result1,ap.description as description1 from
(select * from
(select * from
(select t1.*,code.description_s from
(select t.barcode, t.create_time,t.create_user, t.reserved5 ,t.reserved7,t.result,
(case when t.result=20 THEN '不合格'
when t.result=30 THEN '改判合格'
when t.result=10 THEN '錄入合格'
end) as result1,
ROW_NUMBER() OVER(PARTITION BY t.BARCODE ORDER BY t.CREATE_TIME DESC) ROW_NUM
from t_mes_test t where t.category='50' and t.result <> '30') t1 left join
(select code_name_s,description_s from AT_BAS_CODE@mes_link) code
on code.code_name_s=t1.reserved5
order by create_time desc)t2
where t2.create_time between to_date('${st}'||' 00:30:00','yyyy-mm-dd hh24:mi:ss') and to_date('${ed}'||' 00:29:59','yyyy-mm-dd hh24:mi:ss')+1)a2 where a2.row_num=1) a
left join
(select description ,user_name from app_user@MES_LINK) ap
on a.create_user=ap.user_name
) b1
left join
(select a.barcode_s, a.part_number_s,part.DESCRIPTION,p.equipment_name,trans_time,at1.shift_name_s ,ap.first_name
from at_wm_stock@MES_LINK a,PART@MES_LINK part, pm_eqp_production p,app_user@mes_link ap,AT_PM_SHIFT_CHANGE@MES_LINK at1
where
--barcode_s = '8C3000847' and
a.part_number_s=part.part_number
and p.id_key = a.from_id_key_s
and ap.user_name=at1.user_name_s
and substr(a.batch_no_s,1,20)=at1.batch_no_s
and at1.equipment_name_s like 'CCXBM%'
--and trans_time between to_date('${st}'||' 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('${ed}'||' 23:59:59','yyyy-mm-dd hh24:mi:ss')
order by trans_time desc) a1
on a1.barcode_s=b1.barcode
) p where p.equipment_name is not null order by create_time desc)kz
LEFT JOIN
(SELECT B.material_spec_s,
B.pattern_s ,
A.part_number
FROM part@MES_LINK A
LEFT JOIN uda_part@MES_LINK B
ON A.PART_KEY = B.OBJECT_KEY
) pp ON kz.part_number_s = pp.part_number
where 1=1 ${if(len(equip) == 0,"", " and equipment_name in ('" +equip+"')")}
${if(len(res) == 0,"", " and result1 in ('" +res+"')")} ) aa
LEFT JOIN (select t.barcode, t.create_time,t.create_user,app.description xb_user, t.reserved6,t.reserved7,DECODE(t.result,'10','錄入合格','20','不合格','30','改判合格') result1,CODE.DESCRIPTION_S,
ROW_NUMBER() OVER(PARTITION BY t.BARCODE ORDER BY t.CREATE_TIME DESC) ROW_NUM
from t_mes_test t
LEFT JOIN app_user@mes_link app ON app.user_name = t.create_user
left join
(select code_name_s,description_s from AT_BAS_CODE@mes_link) code
on code.code_name_s=t.reserved5
where t.category='50' AND t.create_time between to_date('${st}'||' 00:30:00','yyyy-mm-dd hh24:mi:ss')
and to_date('${ed}'||' 00:29:59','yyyy-mm-dd hh24:mi:ss')+1 AND t.result='30'
order by t.create_time desc) tt ON tt.barcode= aa.barcode
left join (select t.barcode_s, TO_CHAR(t.last_modified_time,'yyyy-MM-dd hh24:mi:ss'),sc.code_name_s, sc.REMARKS_s from at_wm_stock_record_code@mes_link sc ,at_wm_stock@mes_link t, (SELECT *
FROM AT_WM_STOCK_OPER_RECORD@mes_link
WHERE ATR_KEY IN
(SELECT MAX(ATR_KEY)
FROM AT_WM_STOCK_OPER_RECORD@mes_link
WHERE OPER_TYPE_S ='81'
GROUP BY STOCK_64_64
)
)sr where t.atr_key = sr.stock_64_64
AND sr.atr_key = sc.stock_record_64) asr on asr.barcode_s= aa.barcode
uj5u.com熱心網友回復：
每個表三百萬資料以上uj5u.com熱心網友回復：
你這個sql能跑出資料嗎？那么多dblink，還不如直接讓上游封裝一下，你們每天采集。這個要跑多久？大神！uj5u.com熱心網友回復：
這就是個mes系統中的一個查詢嗎 ？能不能先處理下再弄 這要暈啊！！！！！uj5u.com熱心網友回復：
確實跑的特別慢！而且因為是生產資料，資料庫的資料每日增加，查詢也是得等好幾分鐘。大佬可否指教下如何上游封裝？剛畢業啥都不懂
uj5u.com熱心網友回復：
它是各種select 嵌套，我不知道哪塊需要優化就全部復制過來了。大佬可否指教下怎么處理？
uj5u.com熱心網友回復：
就是上游系統給你做成一張物體表，把他們的資料重新封裝一下，你們去采，做成視圖估計也不行，物化視圖說不定可以，這個sql的使用頻率大嗎？uj5u.com熱心網友回復：
嗯嗯，經常使用的。上游系統是指？我們公司是制造業，軟體這塊沒有人，，，執行流程是：查詢使用資料庫寫，然后放在帆軟里面，然后網頁查詢的。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/24587.html
標籤：開發