-----------------------------------------未优化代码------------------------------------------------------------------------------
select
A.Gvm_id,
B.B_company_name_cn as deptName,
B1.B_company_name_cn as areaName,
F.F_variGy,
G.GVL_ODOMGER asGvm_now_odomGer
fromG_vehicle_maintenance A
leftjoin G_vehicle F on A.Gvm_F_id=F.F_id
left join (selects.Gvl_F_id, s.GVL_ODOMGER
from G_vehicle_oil s
inner join (select Gvo.Gvl_F_id, max(Gvo.create_time)as numG
from G_vehicle_oil Gvo
group by Gvo.Gvl_F_id) ss
on ss.Gvl_F_id = s.Gvl_F_id
and ss.numG = s.create_time
) G on G.Gvl_F_id =A.Gvm_F_id
leftjoin es_company B on F.F_B_id=B.B_id
leftjoin es_company B1 onB.B_parent_B_id=B1.B_id
where nvl(G.GVL_ODOMGER,0)- nvl(A.Gvm_next_mileage,0)>=0
------------------------------------------优化后代码-------------------------------------------------------------------
select
A.Gvm_id,
B.B_company_name_cn as deptName,
B1.B_company_name_cn as areaName,
F.F_variGy,
G.GVL_ODOMGER asGvm_now_odomGer
from
(selects.Gvl_F_id, s.GVL_ODOMGER
from G_vehicle_oil s
inner join (select Gvo.Gvl_F_id, max(Gvo.create_time)as numG
from G_vehicle_oil Gvo
group by Gvo.Gvl_F_id) ss
on ss.Gvl_F_id = s.Gvl_F_id
and ss.numG = s.create_time
) G
left joinG_vehicle_maintenance A on G.Gvl_F_id =A.Gvm_F_id
leftjoin G_vehicle F on A.Gvm_F_id=F.F_id
leftjoin es_company B on F.F_B_id=B.B_id
leftjoin es_company B1 onB.B_parent_B_id=B1.B_id
where nvl(G.GVL_ODOMGER,0) >= nvl(A.Gvm_next_mileage,0)
-----------------------------------------------总结-----------------------------------------------------------------
第一次运行未优化SQL语句时 运行时间4s 接着第二次运行 卡住 不报错也不显示查询结果
排查错误 发现 不显示查询结果原因出在 nvl(G.GVL_ODOMGER,0)- nvl(A.Gvm_next_mileage,0)>=0
修改为 nvl(G.GVL_ODOMGER,0) >= nvl(A.Gvm_next_mileage,0) 运行 显示结果 运行时间8s
运行时间还是太长 接着排查原因 :
查看SQL执行计划 发现花费时间最长的一段是
left join (selects.Gvl_F_id, s.GVL_ODOMGER
from G_vehicle_oil s
inner join (select Gvo.Gvl_F_id, max(Gvo.create_time)as numG
from G_vehicle_oil Gvo
group by Gvo.Gvl_F_id) ss
on ss.Gvl_F_id = s.Gvl_F_id
and ss.numG = s.create_time
) G on G.Gvl_F_id =A.Gvm_F_id
查资料发现使用 lift join 进行多表联查 表的前后顺序会影响SQL的执行效率
果断调整表的联查的顺序 接着运行SQL语句 显示查询结果 运行时间2s
