一段sql语句中内嵌了很多select,left join等,数据库数据也特别大,该怎么进行优化?
采用临时表的话,怎么进行连接啊,建立索引啊之类的。真萌新,真不懂,求大神指教。
采用临时表的话,怎么进行连接啊,建立索引啊之类的。真萌新,真不懂,求大神指教。
解决方案 »
- oracle 触发器 不知道什么错误 无法运行
- 关于sqldeveloper 连接oracle出现ora12505的奇怪问题
- 求教,oracle创建触发器,更新表A时触发而更新B,使B表的记录加上表C的值
- 安装完CRS后,OracleCSService服务启动不了.....
- oracle性能问题?
- 那位大侠详细讲讲alter system archive log current和alter system switch logfile的区别啊?
- 在windows操作系统环境下,如何设置oracle?高手请进……
- 小弟百拜求救解决方法
- 如何用sqlplus插入行
- 快照刷新时间问题
- oracle统计查询怎么写?
- 有大佬帮忙看一下吗
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