OraFAQ Forum: SQL & PL/SQL » Data not inserted after MView creation

Home » SQL & PL/SQL » SQL & PL/SQL » Data not inserted after MView creation (Oracle Database 11g Enterprise Edition Release 11.2.0.2.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Data not inserted after MView creation [message #651972]

Mon, 30 May 2016 02:21

smvmohan
Messages: 4
Registered: May 2016
Location: Singapore

Junior Member

Hi All,

I am trying to drop and recreate materialized view like stated below and once the query runs, no data inserted. did not thrown any error while droping/Creating . Can you please suggest what could be the problem.

Note : If I run the select statement then able to see the result very fast.

Time taking to refresh : 4 to 5 hours
Total number Of Records : 258 thousand
DB version : Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
OS : Linux 2.4.xx x86_64

Kindly help me to resolve this issue. Thank you!

MView
 ------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------
DROP MATERIALIZED VIEW "MV_FORMS_MGMT_MONTHLY_DATA"  ;
 
CREATE MATERIALIZED VIEW "MV_FORMS_MGMT_MONTHLY_DATA"
  AS (with
data as
(  select fm.fm_form_id,fm.fm_formtype_id,fm.fm_date,fm.vessel_code,ft.fm_periodicity_id,replace(replace(fm.fm_Data,'xmlns=''generic''',''),'<?xml version=''1.0'' encoding=''utf-8''?>','') XmlString  from
   forms_master fm ,
   form_types ft
     WHERE fm.fm_formtype_id     = ft.fm_formtype_id
AND ft.fm_periodicity_id    in (4)
)
select t.fm_form_id,t.fm_formtype_id,t.fm_date,to_number(to_char(t.fm_date,'YYYYMM')) month_num,t.vessel_code,replace(x.nams,'_',' ') data_Node,extractvalue(xmltype(t.XmlString),'//'||x.nams||'/@attr') Data_point, nvl(x.vals,0) data_value,sysdate last_refresh_date,'This MV stores MONTHLY forms Data' DESCRIPTION
  from data t,
  xmltable('for $n in //*[count(./*) = 0]
                 return
                 <paths>
                   <nams>{name($n)}</nams>
                   <vals>{$n}</vals>
                 </paths>
                '
                passing xmltype(t.XmlString)
                columns nams varchar2(4000) path '/paths/nams',
                        vals varchar2(4000) path '/paths/vals'
               ) X
    where is_number(x.vals)=1 and x.vals is not null and x.vals <> '0');

XML Code example for one vessel code : Soruce Data. Stored in the table Forms Master Table.

<?xml version='1.0' encoding='utf-8'?>
<FormsManagement xmlns='generic'>
  <Bunkering_Bunker_Transfer_Ops attr='Bunkering & Bunker Transfer Ops'>
  <No_of_Operations attr='No of Operations'>1</No_of_Operations>
  <No_Of_Spills attr='No Of Spills'>
  <Overside attr='Overside'>NIL</Overside>
  <Contained_On_Board attr='Contained On-Board'>NIL</Contained_On_Board>
  </No_Of_Spills>
  </Bunkering_Bunker_Transfer_Ops>
  <Garbage_Management attr='Garbage Management'>
  <Garbage_Accumulated attr='Garbage Accumulated(Cub.M)'>
  <Plastics attr='Plastics'>
  <![CDATA[3.65]]>
  </Plastics>
  <All_Other_Garbage attr='All Other Garbage'>
  <![CDATA[4.03]]>
  </All_Other_Garbage>
  </Garbage_Accumulated>
  <Garbage_Incinerated attr='Garbage Incinerated(Cub.M)'>
  <Plastics1 attr='Plastics'>0</Plastics1>
  <All_Other_Garbage1 attr='All Other Garbage'>0</All_Other_Garbage1>
  </Garbage_Incinerated>
  <Garbage_Discharge_to_Shore_R attr='Garbage Discharge to Shore R'>
  <Plastics2 attr='Plastics'>
  <![CDATA[3.65]]>
  </Plastics2>
  <All_Other_Garbage2 attr='All Other Garbage'>
  <![CDATA[3.40]]>
  </All_Other_Garbage2>
  </Garbage_Discharge_to_Shore_R>
  </Garbage_Management>
  <Cargo_Loading_Discharge_Transfer_Ops attr='Cargo Loading, Discharge & Transfer Ops'>
  <No_of_Operations2 attr='No of Operations'>4</No_of_Operations2>
  <No_Of_Spills1 attr='No. Of Spills'>
  <Overside1 attr='Overside'>NIL</Overside1>
  <Contained_On_Board1 attr='Contained On-Board'>NIL</Contained_On_Board1>
  </No_Of_Spills1>
  </Cargo_Loading_Discharge_Transfer_Ops>
  <Bunker_Consumption attr='Bunker Consumption'>
  <Bunker_ROB1 attr='Bunker ROB (@01/0000 UTC)(MT)'>500</Bunker_ROB1>
  <Bunker_ROB2 attr='Bunker ROB (@31/2400 UTC)(MT)'>384</Bunker_ROB2>
  <Total_Bunker_received_During_the_Period attr='Total Bunker received during the period(MT)'>100</Total_Bunker_received_During_the_Period>
  </Bunker_Consumption>
</FormsManagement>

OUT PUT for one vessel code:

FM_FORM_ID	FM_FORMTYPE_ID	FM_DATE	MONTH_NUM	VESSEL_CODE	DATA_NODE	DATA_POINT	DATA_VALUE	LAST_REFRESH_DATE
2	3	30/06/15 12:00:00	201506	2068	No of Operations	No of Operations	1	30/05/16 12:11:42
2	3	30/06/15 12:00:00	201506	2068	Plastics	Plastics	3.65	30/05/16 12:11:42
2	3	30/06/15 12:00:00	201506	2068	All Other Garbage	All Other Garbage	4.03	30/05/16 12:11:42
2	3	30/06/15 12:00:00	201506	2068	Plastics2	Plastics	3.65	30/05/16 12:11:42
2	3	30/06/15 12:00:00	201506	2068	All Other Garbage2	All Other Garbage	3.40	30/05/16 12:11:42
2	3	30/06/15 12:00:00	201506	2068	No of Operations2	No of Operations	4	30/05/16 12:11:42
2	3	30/06/15 12:00:00	201506	2068	Bunker ROB1	Bunker ROB (@01/0000 UTC)(MT)	500	30/05/16 12:11:42
2	3	30/06/15 12:00:00	201506	2068	Bunker ROB2	Bunker ROB (@31/2400 UTC)(MT)	384	30/05/16 12:11:42
2	3	30/06/15 12:00:00	201506	2068	Total Bunker received During the Period	Total Bunker received during the period(MT)	100	30/05/16 12:11:42

Kind Regards,
Mohan

--moderator update: added [code] tags

[Updated on: Mon, 30 May 2016 02:30] by Moderator