oracle多表联合查询作为一张视图,查询速度超~级~慢~的,怎么办?

Question

create or replace view v_shipowner_all_test as
select a.owner_id shipowner_id,a1.ship_count,a1.ship_count_s,a1.ship_count_s_cgt,a2.new_ship_ty,a2.new_ship_ty_s,a3.new_ship_ly,a3.new_ship_ly_s,a4.ship_now,a4.ship_now_s,a4.ship_now_s_cgt,a5.ship_now_b,
a5.ship_now_s_b ,a6.ship_fin_ty,a6.ship_fin_ty_s,a7.ship_fin_ly,a7.ship_fin_ly_s,a8.ship_sec_ty,a8.ship_sec_ty_s,a9.SHIP_SEC_B_TY,a9.SHIP_SEC_B_TY_s,b1.SHIP_SEC_all_TY,
b1.SHIP_SEC_all_TY_s,b2.SHIP_SEC_all_lY,b2.SHIP_SEC_all_lY_s,b3.ship_yard_id newest_yard,b4.ship_slipt_ty,b4.ship_slipt_ty_s,b5.ship_slipt_ly,b5.ship_slipt_ly_s,b6.sh_x,b6.sh_x_s,b6.sh_x_s_cgt,
b7.sh_d,b7.sh_d_s,b7.sh_d_s_cgt,b8.sh_b,b8.sh_b_s,b8.sh_b_s_cgt,b9.sh_h,b9.sh_h_s,b9.sh_h_s_cgt
from so_shipowner_info a
left join (select t.ship_owner_id,count(1) ship_count,nvl(sum(t.dwt),0) ship_count_s,nvl(sum(t.cgt),0) ship_count_s_cgt from ship_base_info t group by t.ship_owner_id)a1 on a1.ship_owner_id=a.owner_id
left join (select t.ship_owner_id,count(1) new_ship_ty,nvl(sum(t.dwt),0) new_ship_ty_s from order_info_new t where t.contract_date>to_char(sysdate,'yyyy') group by t.ship_owner_id)a2 on a2.ship_owner_id=a.owner_id
left join (select t.ship_owner_id,count(1) new_ship_ly,nvl(sum(t.dwt),0) new_ship_ly_s from order_info_new t where t.contract_date>to_char(ADD_MONTHS(sysdate,-12),'yyyy') and t.contract_date left join (select t.ship_owner_id,count(1) ship_now,nvl(sum(t.dwt),0) ship_now_s,nvl(sum(t.cgt),0) ship_now_s_cgt from order_info_onorder t group by t.ship_owner_id)a4 on a4.ship_owner_id=a.owner_id
left join (select t.ship_owner_id,count(1) ship_now_b,nvl(sum(t.dwt),0) ship_now_s_b from order_info_begingyear t group by t.ship_owner_id)a5 on a5.ship_owner_id=a.owner_id
left join (select t.ship_owner_id,count(1) ship_fin_ty,nvl(sum(t.dwt),0) ship_fin_ty_s from order_info_deliveries t where t.ship_built>to_char(sysdate,'yyyy') group by t.ship_owner_id)a6 on a6.ship_owner_id=a.owner_id
left join (select t.ship_owner_id,count(1) ship_fin_ly,nvl(sum(t.dwt),0) ship_fin_ly_s from order_info_deliveries t where t.ship_built>to_char(ADD_MONTHS(sysdate,-12),'yyyy') and t.ship_built left join (select t.sellers_id,count(1) ship_sec_ty,nvl(sum(t.dwt),0) ship_sec_ty_s from order_info_secondhand t where t.ship_business_date>to_char(sysdate,'yyyy') group by t.sellers_id)a8 on a8.sellers_id=a.owner_id
left join (select t.buyer_id,count(1) SHIP_SEC_B_TY,nvl(sum(t.dwt),0) SHIP_SEC_B_TY_s from order_info_secondhand t where t.ship_business_date>to_char(sysdate,'yyyy') group by t.buyer_id)a9 on a9.buyer_id=a.owner_id
left join (select t.buyer_id,t.sellers_id,count(1) SHIP_SEC_all_TY,nvl(sum(t.dwt),0) SHIP_SEC_all_TY_s from order_info_secondhand t where t.ship_business_date>to_char(sysdate,'yyyy') group by t.buyer_id,t.sellers_id)b1 on b1.buyer_id=a.owner_id or b1.sellers_id=a.owner_id
left join (select t.buyer_id,t.sellers_id,count(1) SHIP_SEC_all_lY,nvl(sum(t.dwt),0) SHIP_SEC_all_lY_s from order_info_secondhand t where t.ship_business_date>to_char(ADD_MONTHS(sysdate,-12),'yyyy') and t.ship_business_date left join (select t.ship_yard_id,t.ship_owner_id from order_info_new t order by t.contract_date desc) b3 on b3.ship_owner_id=a.owner_id and rownum=1
left join (select t.ship_owner_id,count(1) ship_slipt_ty,nvl(sum(t.dwt),0) ship_slipt_ty_s from order_info_split t where t.split_date>to_char(sysdate,'yyyy') group by t.ship_owner_id)b4 on b4.ship_owner_id=a.owner_id
left join (select t.ship_owner_id,count(1) ship_slipt_ly,nvl(sum(t.dwt),0) ship_slipt_ly_s from order_info_split t where t.split_date>to_char(ADD_MONTHS(sysdate,-12),'yyyy') and t.split_date<to_char(ADD_MONTHS(sysdate,-12),'yyyy-mm-dd') group by t.ship_owner_id)b5 on b5.ship_owner_id=a.owner_id
left join (select t.ship_owner_id,count(1) sh_x,nvl(sum(t.dwt),0) sh_x_s,nvl(sum(t.cgt),0) sh_x_s_cgt from ship_base_info t where t.ship_type_dwt_id='4' group by t.ship_owner_id)b6 on b6.ship_owner_id=a.owner_id
left join (select t.ship_owner_id,count(1) sh_d,nvl(sum(t.dwt),0) sh_d_s,nvl(sum(t.cgt),0) sh_d_s_cgt from ship_base_info t where t.ship_type_dwt_id='3' group by t.ship_owner_id)b7 on b7.ship_owner_id=a.owner_id
left join (select t.ship_owner_id,count(1) sh_b,nvl(sum(t.dwt),0) sh_b_s,nvl(sum(t.cgt),0) sh_b_s_cgt from ship_base_info t where t.ship_type_dwt_id='2' group by t.ship_owner_id)b8 on b8.ship_owner_id=a.owner_id
left join (select t.ship_owner_id,count(1) sh_h,nvl(sum(t.dwt),0) sh_h_s,nvl(sum(t.cgt),0) sh_h_s_cgt from ship_base_info t where t.ship_type_dwt_id='1' group by t.ship_owner_id)b9 on b9.ship_owner_id=a.owner_id

group by a.owner_id,a1.ship_count,a1.ship_count_s,a1.ship_count_s_cgt,a2.new_ship_ty,a2.new_ship_ty_s,a3.new_ship_ly,a3.new_ship_ly_s,a4.ship_now,a4.ship_now_s,a4.ship_now_s_cgt,a5.ship_now_b,
a5.ship_now_s_b ,a6.ship_fin_ty,a6.ship_fin_ty_s,a7.ship_fin_ly,a7.ship_fin_ly_s,a8.ship_sec_ty,a8.ship_sec_ty_s,a9.SHIP_SEC_B_TY,a9.SHIP_SEC_B_TY_s,b1.SHIP_SEC_all_TY,
b1.SHIP_SEC_all_TY_s,b2.SHIP_SEC_all_lY,b2.SHIP_SEC_all_lY_s,b3.ship_yard_id,b4.ship_slipt_ty,b4.ship_slipt_ty_s,b5.ship_slipt_ly,b5.ship_slipt_ly_s,b6.sh_x,b6.sh_x_s,b6.sh_x_s_cgt,
b7.sh_d,b7.sh_d_s,b7.sh_d_s_cgt,b8.sh_b,b8.sh_b_s,b8.sh_b_s_cgt,b9.sh_h,b9.sh_h_s,b9.sh_h_s_cgt

这只是一部分,一共50多个表,想做一张大视图.结果发现,查询了一个晚上也没有查询出结果,怎么样才能提高查询速度呢?或者有什么其他解决办法?????

Answer 1

个人建议。

oracle视图优化不是很清楚，不知道可不可以加索引之类，就算可以加也不推荐这么操作。
你是50多个表的查询，数据的量级比较大，做成视图会导致每次条件查询都会先做一次全表查询（至少mysql数据库是这样，oralce可能会有这方面的优化）。这非常浪费资源。
推荐：不用视图，直接用sql语句。原则上就是多加where条件，给关键的部分加上索引，oracle应该也有mysql类似的explain函数用来分析sql，你把你的查询语句多分析分析，减少查询量，减少查询次数，提高查询方式（索引就是一种比较快速的查询方式）。