select-oracle多表聯合查詢作為一張視圖,查詢速度超~級~慢~的,怎麼辦?

oracle多表聯合查詢作為一張視圖,查詢速度超~級~慢~的,怎麼辦?

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多個表,想做一張大視圖.結果發現,查詢了一個晚上也沒有查詢出結果,怎麼樣才能提高查詢速度呢?或者有什麼其他解決辦法?????

最佳回答：

個人建議。

oracle視圖優化不是很清楚，不知道可不可以加索引之類，就算可以加也不推薦這麼操作。
你是50多個表的查詢，數據的量級比較大，做成視圖會導致每次條件查詢都會先做一次全表查詢（至少mysql數據庫是這樣，oralce可能會有這方面的優化）。這非常浪費資源。
推薦：不用視圖，直接用sql語句。原則上就是多加where條件，給關鍵的部分加上索引，oracle應該也有mysql類似的explain函數用來分析sql，你把你的查詢語句多分析分析，減少查詢量，減少查詢次數，提高查詢方式（索引就是一種比較快速的查詢方式）。