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多个表,想做一张大视图.结果发现,查询了一个晚上也没有查询出结果,怎么样才能提高查询速度呢?或者有什么其他解决办法?????
