Oracle 多表联查优化

-----------------------------------------未优化代码------------------------------------------------------------------------------

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 join

G_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