Home » SQL & PL/SQL » SQL & PL/SQL » Data not inserted after MView creation (Oracle Database 11g Enterprise Edition Release 11.2.0.2.0)
Data not inserted after MView creation [message #651972] Mon, 30 May 2016 02:21 Go to next message
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

Report message to a moderator

Re: Data not inserted after MView creation [message #651974 is a reply to message #651972] Mon, 30 May 2016 02:33 Go to previous messageGo to next message
John Watson
Messages: 8580
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

I've added the [code] tags for you on this occasion - please do it yourself in future.

However, even with the tags, I do not understand your question. Are you saying the the materialized view contains no rows? Or that it contains 258,000 rows?
Re: Data not inserted after MView creation [message #651975 is a reply to message #651974] Mon, 30 May 2016 02:54 Go to previous messageGo to next message
smvmohan
Messages: 4
Registered: May 2016
Location: Singapore
Junior Member
Hi John,

Thank you for your reply.

In future I will make my question and code in OraFAQ standard.

I am trying to drop and recreate the materialized view. While creating the MView, Its created with out error. But Data is not inserted(No rows loaded).

When I Query the select statement then am able to see the data also.

Let me know if you have any idea. Thank you

[Updated on: Mon, 30 May 2016 02:55]

Report message to a moderator

Re: Data not inserted after MView creation [message #651976 is a reply to message #651975] Mon, 30 May 2016 03:00 Go to previous messageGo to next message
John Watson
Messages: 8580
Registered: January 2010
Location: Global Village
Senior Member
OK, now you need to provide some more information. You say that the query does return the rows, "very fast". Please can you prove this by running the query like this:

orclz>
orclz> set timing on
orclz> set autotrace traceonly statistics
orclz> select /* substitute your query for this one */ * from emp;

14 rows selected.

Elapsed: 00:00:00.01

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          6  physical reads
          0  redo size
       1593  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

orclz>
It seems very unlikely that a query as complex as that (particularly with the horrible predicate at the end, which will be crippling performance) should be fast.

Also, please show what happens when you create the materialized view. Use SQL*Plus and copy/paste the results as I did. And show your query that proves that the MV has no rows.
Re: Data not inserted after MView creation [message #651985 is a reply to message #651976] Mon, 30 May 2016 20:41 Go to previous messageGo to next message
smvmohan
Messages: 4
Registered: May 2016
Location: Singapore
Junior Member
Hi John,

I ran it in SQLPLUS and its took 4 hours 41 mins. below is the script that I executed.

When I create Mview with this script after run it should insert data. but its not inserting.

SQL> set timing on
SQL> set autotrace traceonly statistics
SQL> with
2 data as
3 ( select fm.fm_form_id,fm.fm_formtype_id,fm.fm_date,fm.vessel_code,ft.fm_p
eriodicity_id,
4 replace(replace(fm.fm_Data,'xmlns=''generic''',''),'<?xml version=''1.0'' e
ncoding=''utf-8''?>','') XmlString from
5 forms_master fm ,
6 form_types ft
7 WHERE fm.fm_formtype_id = ft.fm_formtype_id
8 AND ft.fm_periodicity_id in (4)
9 AND fm.FM_FORMTYPE_ID <> '145'
10 )
11 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,
12 replace(x.nams,'_',' ') data_Node,
13 extractvalue(xmltype(t.XmlString),'//'||x.nams||'/@attr') Data_point, nvl(x
.vals,0) data_value,
14 sysdate last_refresh_date
15 from data t,
16 xmltable('for $n in //*[count(./*) = 0]
17 return
18 <paths>
19 <nams>{name($n)}</nams>
20 <vals>{$n}</vals>
21 </paths>
22 '
23 passing xmltype(t.XmlString)
24 columns nams varchar2(4000) path '/paths/nams',
25 vals varchar2(4000) path '/paths/vals'
26 ) X
27 where is_number(x.vals)=1 and x.vals is not null and x.vals <> '0';

290263 rows selected.

Elapsed: 04:41:24.66

Statistics
----------------------------------------------------------
86 recursive calls
65200436 db block gets
25626623 consistent gets
3158165 physical reads
212 redo size
14057046 bytes sent via SQL*Net to client
213188 bytes received via SQL*Net from client
19352 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
290263 rows processed
Re: Data not inserted after MView creation [message #651993 is a reply to message #651985] Tue, 31 May 2016 01:50 Go to previous messageGo to next message
John Watson
Messages: 8580
Registered: January 2010
Location: Global Village
Senior Member
We have established that you were wrong when you said the query was "very fast".
You have not shown what happens when you create the MV, or how you know that the MV has no rows.

Furthermore, you did not use [code] tags to format your output.
Re: Data not inserted after MView creation [message #652018 is a reply to message #651993] Tue, 31 May 2016 05:25 Go to previous messageGo to next message
smvmohan
Messages: 4
Registered: May 2016
Location: Singapore
Junior Member
Thank you for your help.

Since I have executed the select query and its very fast in SQL Developer. But I execute same query in SQLPLUS then its taking more time.

I have selected the MView query and it returns no data but Mview created without error.

Is there any way to troubleshoot Why the data is not inserted when I drop and recreate the MVIEW.

Thank you,
Mohan
Re: Data not inserted after MView creation [message #652019 is a reply to message #652018] Tue, 31 May 2016 05:30 Go to previous messageGo to next message
John Watson
Messages: 8580
Registered: January 2010
Location: Global Village
Senior Member
No, the query does not run "very fast" in SQL Developer. You are seeing just the first few rows, not all 290000.

Since you refuse to show what happens when you create and query the MV, I do not see how anyone can assist you.

Goodbye.

[Updated on: Tue, 31 May 2016 05:31]

Report message to a moderator

Re: Data not inserted after MView creation [message #652034 is a reply to message #652019] Tue, 31 May 2016 08:33 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Why are you using the WITH clause. I have seen in the past that using some clauses causes the MVIEW to be created but empty. For example in oracle 10, you can use an inline select in the select list, but it will fail in an MVIEW selection. Change the with to an inline select in the from clause and try it again.
Re: Data not inserted after MView creation [message #652037 is a reply to message #652034] Tue, 31 May 2016 08:40 Go to previous messageGo to next message
John Watson
Messages: 8580
Registered: January 2010
Location: Global Village
Senior Member
You sre correct, Bill: WITH doesn't work.
I do not think that Mohan is telling the truth, which is probably why he refuses to show what actually happens when he tries to create the MV. For example, I think this attenmpts to do the same as his code:
orclz>
orclz> create materialized view mv1 as
  2  (with data as (select * from dept)
  3  select * from emp natural join data) x
  4  where x.sal=5000;
select * from emp natural join data) x
                                   *
ERROR at line 3:
ORA-32034: unsupported use of WITH clause

orclz>


Re: Data not inserted after MView creation [message #652039 is a reply to message #652037] Tue, 31 May 2016 08:51 Go to previous messageGo to next message
John Watson
Messages: 8580
Registered: January 2010
Location: Global Village
Senior Member
Mohan's own code gives that same error:
orclz> CREATE MATERIALIZED VIEW "MV_FORMS_MGMT_MONTHLY_DATA"
  2    AS (with
  3  data as
  4  (  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
  5     forms_master fm ,
  6     form_types ft
  7       WHERE fm.fm_formtype_id     = ft.fm_formtype_id
  8  AND ft.fm_periodicity_id    in (4)
  9  )
 10  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
 11    from data t,
 12    xmltable('for $n in //*[count(./*) = 0]
 13                   return
 14                   <paths>
 15                     <nams>{name($n)}</nams>
 16                     <vals>{$n}</vals>
 17                   </paths>
 18                  '
 19                  passing xmltype(t.XmlString)
 20                  columns nams varchar2(4000) path '/paths/nams',
 21                          vals varchar2(4000) path '/paths/vals'
 22                 ) X
 23      where is_number(x.vals)=1 and x.vals is not null and x.vals <> '0');
    where is_number(x.vals)=1 and x.vals is not null and x.vals <> '0')
                                                                      *
ERROR at line 23:
ORA-32034: unsupported use of WITH clause


orclz>


Clearly, this whole topic is a waste of space.
Re: Data not inserted after MView creation [message #652040 is a reply to message #652039] Tue, 31 May 2016 09:10 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's always possible that the OPs specific oracle version doesn't throw the error it's supposed to when the view is created.
Re: Data not inserted after MView creation [message #652056 is a reply to message #652040] Tue, 31 May 2016 11:40 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Create your MVIEW using the following code

CREATE MATERIALIZED VIEW MV_FORMS_MGMT_MONTHLY_DATA
AS
   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 (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)) 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 X.Vals = '1' AND X.Vals IS NOT NULL AND X.Vals <> '0';

[Updated on: Tue, 31 May 2016 11:41]

Report message to a moderator

Re: Data not inserted after MView creation [message #652058 is a reply to message #652037] Tue, 31 May 2016 12:00 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3067
Registered: January 2010
Location: Connecticut, USA
Senior Member
John Watson wrote on Tue, 31 May 2016 09:40
You sre correct, Bill: WITH doesn't work.


Error has nothing to do with WITH clause. Just look at create MV syntax diagram - there is no parenthesis after AS, just a query. It just happens (while it shouldn't) so it works if non-CTE query is used:

SQL> create materialized view emp_mv as (select * from emp);

Materialized view created.

SQL> drop materialized view emp_mv;

Materialized view dropped.

SQL> create materialized view emp_mv as (with t as (select * from emp) select * from t);
create materialized view emp_mv as (with t as (select * from emp) select * from t)
                                                                                 *
ERROR at line 1:
ORA-32034: unsupported use of WITH clause


SQL> create materialized view emp_mv as with t as (select * from emp) select * from t;

Materialized view created.

SQL> 


SY.
Re: Data not inserted after MView creation [message #652147 is a reply to message #652058] Thu, 02 June 2016 14:28 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
run the following command in the schema holding the MVIEW and paste the results here.

select mview_name,refresh_method,build_mode,last_refresh_type,staleness
from user_mviews
where mview_name = 'MV_FORMS_MGMT_MONTHLY_DATA';



Previous Topic: encrypt and decrypt URL
Next Topic: percentages of data population
Goto Forum:
  


Current Time: Sun Aug 01 19:06:37 CDT 2021