问题描述

基于rowid的物化视图没有数据?????

本人刚刚接触物化视图,有些地方不太清楚,刚创建好的物化视图没有数据是几个意思啊?
select 后面是多个表左连接,用的是(+)而不是left join ,哪位大神搭救一下??谢谢
create materialized view V_SHIPOWNER_ALL_TEST
refresh complete on demand
start with to_date('07-09-2015 12:00:00', 'dd-mm-yyyy hh24:mi:ss') next SYSDATE+1
as
select a.owner_id shipowner_id,count(a1.id) ship_count,nvl(sum(a1.dwt),0) ship_count_s,nvl(sum(a1.cgt),0) ship_count_s_cgt,count(a2.ship_id) new_ship_ty,nvl(sum(a2.dwt),0) new_ship_ty_s,
count(a2.ship_id) new_ship_ly,nvl(sum(a2.dwt),0) new_ship_ly_s,count(a4.ship_id) ship_now,nvl(sum(a4.dwt),0) ship_now_s,nvl(sum(a4.cgt),0) ship_now_s_cgt,count(a5.ship_id) ship_now_b,
nvl(sum(a5.dwt),0) ship_now_s_b ,count(a6.ship_id) ship_fin_ty,nvl(sum(a6.dwt),0) ship_fin_ty_s,count(a6.ship_id) ship_fin_ly,nvl(sum(a6.dwt),0) ship_fin_ly_s,
count(a8.ship_id) ship_sec_ty,nvl(sum(a8.dwt),0) ship_sec_ty_s,count(a8.ship_id) SHIP_SEC_B_TY,nvl(sum(a8.dwt),0) SHIP_SEC_B_TY_s,count(a8.ship_id) SHIP_SEC_all_TY,
nvl(sum(a8.dwt),0) SHIP_SEC_all_TY_s,count(a8.ship_id) SHIP_SEC_all_lY,nvl(sum(a8.dwt),0) SHIP_SEC_all_lY_s

          from so_shipowner_info a ,ship_base_info a1,order_info_onorder a4,order_info_new a2,order_info_begingyear a5,order_info_deliveries a6,order_info_secondhand_formal a8,
(select ship_yard_id,ship_owner_id from order_info_new order by contract_date desc) b3,order_info_split b4

where a1.ship_owner_id(+)=a.owner_id
 and a2.contract_date>to_char(sysdate,'yyyy') and a2.ship_owner_id(+)=a.owner_id

and a2.contract_date>to_char(add_months(sysdate,-12),'yyyy') and a2.contract_date<to_char(add_months(sysdate,-12),'yyyy-mm-dd') and a2.ship_owner_id(+)=a.owner_id

 and a4.ship_owner_id(+)=a.owner_id

 and a5.ship_owner_id(+)=a.owner_id

 and a6.ship_built>to_char(sysdate,'yyyy') and a6.ship_owner_id(+)=a.owner_id
  and a6.ship_built>to_char(add_months(sysdate,-12),'yyyy') and  a6.ship_built<to_char(add_months(sysdate,-12),'yyyy-mm-dd') and a6.ship_owner_id(+)=a.owner_id

 and a8.ship_business_date>to_char(sysdate,'yyyy') and a8.sellers_id(+)=a.owner_id
 and a8.ship_business_date>to_char(sysdate,'yyyy') and a8.buyer_id(+)=a.owner_id
 and a8.ship_business_date>to_char(sysdate,'yyyy') and (a8.buyer_id=a.owner_id or a8.sellers_id=a.owner_id)
 and a8.ship_business_date>to_char(add_months(sysdate,-12),'yyyy') and a8.ship_business_date<to_char(add_months(sysdate,-12),'yyyy-mm-dd') and (a8.buyer_id=a.owner_id or a8.sellers_id=a.owner_id)
 and b3.ship_owner_id(+)=a.owner_id and rownum=1
     and to_char(add_months(sysdate,-60),'yyyy') <=a1.built_date and a1.built_date<to_char(sysdate,'yyyy') and a1.ship_owner_id(+)=a.owner_id
 and to_char(add_months(sysdate,-120),'yyyy') <= a1.built_date and a1.built_date<to_char(add_months(sysdate,-60),'yyyy') and a1.ship_owner_id(+)=a.owner_id
 and to_char(add_months(sysdate,-180),'yyyy') <=a1.built_date and a1.built_date<to_char(add_months(sysdate,-120),'yyyy') and a1.ship_owner_id(+)=a.owner_id
 and to_char(add_months(sysdate,-240),'yyyy') <=a1.built_date and a1.built_date<to_char(add_months(sysdate,-180),'yyyy') and a1.ship_owner_id(+)=a.owner_id
 and to_char(add_months(sysdate,-300),'yyyy') <=a1.built_date and a1.built_date<to_char(add_months(sysdate,-240),'yyyy') and a1.ship_owner_id(+)=a.owner_id
 and to_char(add_months(sysdate,-360),'yyyy') <=a1.built_date and a1.built_date<to_char(add_months(sysdate,-300),'yyyy') and a1.ship_owner_id(+)=a.owner_id
 and to_char(add_months(sysdate,-360),'yyyy')>=a1.built_date and a1.ship_owner_id(+)=a.owner_id

group by a.owner_id,b3.ship_yard_id;

解决方案

物化视图rowid
----------------------