Home » Server Options » Replication » Materialized View
Materialized View [message #152354] Thu, 22 December 2005 12:45 Go to next message
feroze
Messages: 144
Registered: September 2005
Location: chicago
Senior Member
Hi,


How can i use refresh option as 1 hr in creation of materialzed view,
Can i use unoin all in the materialized view and other clause like CONNECT BY PRIOR .




Thanks

[Updated on: Thu, 22 December 2005 12:58]

Report message to a moderator

Re: Materialized View [message #152363 is a reply to message #152354] Thu, 22 December 2005 13:03 Go to previous messageGo to next message
wpaillan
Messages: 35
Registered: March 2005
Location: Santiago - Chile
Member

Hello

Exists a lot of information in this URL.

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96567/repmview.htm#31780

Another option or possibility is, in a procedure to refresh the materialized view

dbms_mview.refresh('owner.view_name');


And this procedure is execute for a Job programmed each an hour

williams
Re: Materialized View [message #152371 is a reply to message #152363] Thu, 22 December 2005 13:34 Go to previous messageGo to next message
feroze
Messages: 144
Registered: September 2005
Location: chicago
Senior Member
Hi

Some clarification and adivce needed from experts there.

I have 4 different SQL statements in a report, they are using a view,in the report select we are getting the date from view and other 4-5 tables joined with view.

My question is shall i create a materialized view for the view

or

Shall i create a materialized view for the select statements of report itself which are using view and other table joins

Hope i was able to clear my doubt.

This is all to improve the performance of slow running SQL in report.


Thanks
Re: Materialized View [message #152374 is a reply to message #152371] Thu, 22 December 2005 14:15 Go to previous messageGo to next message
wpaillan
Messages: 35
Registered: March 2005
Location: Santiago - Chile
Member

hi

The bringing up to date give it view materialized is before report

Attachment some simple examples

1.- view materialized
----------------------------------------------
DROP MATERIALIZED VIEW M4_LICMESPAGO;
CREATE materialized VIEW M4_LICMESPAGO
AS SELECT *
FROM M4_LICENCIAS_MES;
------------------------------------------------
View ---> M4_LICENCIAS_MES, is a very heavy sight

2.- One example.
This procedure generates information, of the view materilized
In addition to refresh her before using the cursor

---------------------------------------------------
DROP PROCEDURE M4PR_GEN_PAGO;
CREATE OR REPLACE PROCEDURE M4PR_GEN_PAGO (
IN_SOCIEDAD IN VARCHAR,
IN_FECPAGO IN DATE
) IS
BEGIN
DECLARE
CURSOR C1 IS -- cursor sql declaration
SELECT A.ID_SOCIEDAD,A.ID_EMPLEADO,b.*
FROM M4_ACUMULADO_RL A, M4_LICMESPAGO b
WHERE A.ID_SOCIEDAD = IN_SOCIEDAD
AND A.FEC_PAGO = IN_FECPAGO
AND A.ID_PLANILLA IN (1,2,3,6,7,8,9,10)
AND (B.ID_SOCIEDAD=A.ID_SOCIEDAD
AND B.ID_EMPLEADO=A.ID_EMPLEADO
AND B.FEC_ALTA_EMPLEADO=A.FEC_ALTA_EMPLEADO
AND B.FEC_PAGO=A.FEC_PAGO)
BEGIN

/* refrescamos la vista materializada de las licencias mensuales */
dbms_mview.refresh('owner.M4_LICMESPAGO',null,null,true,false,1,0,0,true);
or
dbms_mview.refresh('owner.M4_LICMESPAGO');

DELETE M4_PAGO_COTIZA_STGO;
COMMIT;

for D in c1 loop
BEGIN
INSERT INTO M4_PAGO_COTIZA_STGO (all field)
VALUES (d.referencias field)
END;
End loop;
END;
END M4PR_GEN_PAGO_COTIZA_STGO;
/
-----------------------------------------------------------



3.- Two example.
This exclusive procedure, only to refresh the view materilized
------------------------------------------
DROP PROCEDURE M4PR_refresh_viewm;
CREATE OR REPLACE PROC
EDURE M4PR_refresh_viewm
IS
BEGIN
/* refrescamos la vista materializada de las licencias mensuales */
dbms_mview.refresh('owner.M4_LICMESPAGO',null,null,true,false,1,0,0,true);
or
dbms_mview.refresh('owner.M4_LICMESPAGO');
END M4PR_refresh_viewm;
/
-------------------------------------------

I hope that this be useful for you

williams
Re: Materialized View [message #152468 is a reply to message #152374] Fri, 23 December 2005 06:58 Go to previous messageGo to next message
wpaillan
Messages: 35
Registered: March 2005
Location: Santiago - Chile
Member

hi

YOU HAVE THE EXAMPLE OF THE SIGHT WITH AUTOMATIC REFRESH, EACH one HOUR HERE ALSO

--------------------------------
DROP MATERIALIZED VIEW MM2;
CREATE MATERIALIZED VIEW MM2
REFRESH COMPLETE with rowid
START WITH sysdate
NEXT sysdate + 1/24
AS
select * from M4_LICENCIAS_MES;
---------------------------------------

Regards

williams
Re: Materialized View [message #152897 is a reply to message #152468] Tue, 27 December 2005 11:19 Go to previous messageGo to next message
feroze
Messages: 144
Registered: September 2005
Location: chicago
Senior Member
Hi


Which data dictionary table shows details about MATERIALIZED VIEW




Thanks
Re: Materialized View [message #152898 is a reply to message #152897] Tue, 27 December 2005 11:25 Go to previous messageGo to next message
wpaillan
Messages: 35
Registered: March 2005
Location: Santiago - Chile
Member


HI

HERE A LOT OF INFORMATION EXISTS, BE MORE THAN ENOUGH

http://www.oracle.com/pls/db92/db92.drilldown?word=MATERIALIZED+VIEW&verb=Setting&expand_all=1&method=LIKE&remark=Ver b+hint

BYE

WILLIAMS

Re: Materialized View [message #152899 is a reply to message #152898] Tue, 27 December 2005 11:41 Go to previous messageGo to next message
wpaillan
Messages: 35
Registered: March 2005
Location: Santiago - Chile
Member

SORRY

SPECIFICALLY IN THIS VIEWS of the DICTIONARY

------------------------------
SELECT *
FROM DBA_SNAPSHOTS
WHERE NAME='NAME_VIEW_MATERIALIZED'
----------------------------------------
SELECT *
FROM USER_SNAPSHOTS
WHERE NAME='NAME_VIEW_MATERIALIZED'
-----------------------------------------


WILLIAMS
Re: Materialized View [message #153121 is a reply to message #152899] Thu, 29 December 2005 05:19 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Williams, YOU CAN STOP SHOUTING NOW, WE'VE HEARD YOU. Thanks.


Feroze, I don't think your question was answered yet (the one about creating materialized views and than query on the combination of creating one materialized view for all). Probably, the best performance is gained when you create the separate materialized views, create the required indexes on those and then query on them (or even create another materialized view "on top of" the other ones). Advantage is that you can use the customized indexes.

Regards,
Sabine
Re: Materialized View [message #154131 is a reply to message #153121] Thu, 05 January 2006 16:54 Go to previous messageGo to next message
feroze
Messages: 144
Registered: September 2005
Location: chicago
Senior Member
Hi

General Question, does recreation of materialized view on tables with large data will take time.

i want to give 5 minutes refresh option
is this correct

REFRESH COMPLETE START WITH SYSDATE
NEXT SYSDATE + 1/288


for 5 minutes is this correct SYSDATE + 1/288, or any other format..



Thanks
Re: Materialized View [message #154599 is a reply to message #154131] Mon, 09 January 2006 11:29 Go to previous messageGo to next message
feroze
Messages: 144
Registered: September 2005
Location: chicago
Senior Member
Hi


1 question is, I created Materialized view and kept the refresh option as every 5 minutes, but in TOAD what i am seeing is the last refresh column of materialized view details as same data and time as it was created not the latest refresh date, does it really refreshing or not,

Another thing is does DBA has to set job queue process for refresing option in init.ora file is it must for materialized view also.


Thanks
Re: Materialized View [message #326516 is a reply to message #152354] Wed, 11 June 2008 17:39 Go to previous messageGo to next message
rivard007
Messages: 9
Registered: June 2008
Junior Member
Hello Everyone,



following the above tips I ran:

CREATE materialized VIEW test
AS
select ..........


followed by:

CREATE OR REPLACE PROCEDURE test
IS
BEGIN
/* les views materalizeee eh sweet*/
dbms_mview.refresh('owner.test');
END test;


.....

then i run this job

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'test();'
,next_date => to_date('16/10/2007 00:10:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE + 1) + 10/1440'
,no_parse => TRUE
);
SYS.DBMS_JOB.BROKEN
(job => X,
broken => TRUE);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;



Dont know if this works as it takes to long to test this close to the end of the day... will this line dbms_mview.refresh('owner.test'); automatically delete and update the materialized view.

Thanks,

Adam
Re: Materialized View [message #326539 is a reply to message #326516] Wed, 11 June 2008 23:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Dont know if this works as it takes to long to test this close to the end of the day...

Why don't you use a table with a couple of lines which refresh in few seconds to make your test?

Quote:
will this line dbms_mview.refresh...

Why don't you execute it to see what happens?

Regards
Michel
Re: Materialized View [message #327234 is a reply to message #326539] Sun, 15 June 2008 09:27 Go to previous messageGo to next message
rivard007
Messages: 9
Registered: June 2008
Junior Member
Sure ill run it and see what happens

[Updated on: Sun, 15 June 2008 13:59]

Report message to a moderator

Re: Materialized View [message #327256 is a reply to message #327234] Sun, 15 June 2008 14:09 Go to previous messageGo to next message
rivard007
Messages: 9
Registered: June 2008
Junior Member
it would have been nice if you would have told me as you could have used just as much effort to tell me
Re: Materialized View [message #327258 is a reply to message #327256] Sun, 15 June 2008 14:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why did you remove it from your previous post just to repost the same thing?
By the way, this is wrong.

Regards
Michel
Re: Materialized View [message #327262 is a reply to message #152354] Sun, 15 June 2008 18:50 Go to previous messageGo to next message
rivard007
Messages: 9
Registered: June 2008
Junior Member
im having a rough day lol .... what is wrong with it??
Re: Materialized View [message #327486 is a reply to message #327262] Mon, 16 June 2008 10:09 Go to previous messageGo to next message
rivard007
Messages: 9
Registered: June 2008
Junior Member
select * from DBA_MVIEWS to view code ... just found this out

[Updated on: Mon, 16 June 2008 10:11]

Report message to a moderator

Re: Materialized View [message #327493 is a reply to message #327486] Mon, 16 June 2008 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A 10 second search and I found:
How to get source code for the materalized view created Razz

Regards
Michel
Re: Materialized View [message #327529 is a reply to message #327493] Mon, 16 June 2008 15:16 Go to previous messageGo to next message
rivard007
Messages: 9
Registered: June 2008
Junior Member
No Message Body

[Updated on: Mon, 16 June 2008 15:20]

Report message to a moderator

Re: Materialized View [message #327795 is a reply to message #327529] Tue, 17 June 2008 12:52 Go to previous messageGo to next message
rivard007
Messages: 9
Registered: June 2008
Junior Member
Alright Im lost ... I have a bunch of materialized views ... how do I make a DBA jobs to update them completely once a week. aka C
Re: Materialized View [message #327796 is a reply to message #327795] Tue, 17 June 2008 12:53 Go to previous messageGo to next message
rivard007
Messages: 9
Registered: June 2008
Junior Member
my above solution doesnt work
Re: Materialized View [message #328260 is a reply to message #327796] Thu, 19 June 2008 09:52 Go to previous message
rivard007
Messages: 9
Registered: June 2008
Junior Member
sorry i was making this a lot more difficult than it is thanks... i got it now with the first example form michael

DROP MATERIALIZED VIEW MM2;
CREATE MATERIALIZED VIEW MM2
REFRESH COMPLETE with rowid
START WITH sysdate
NEXT sysdate + 1/24
AS
select * from M4_LICENCIAS_MES;
Previous Topic: Move a talble to different tablespace in a multimaster replication
Next Topic: Fast Refresh of Materalized Views
Goto Forum:
  


Current Time: Wed Dec 07 10:52:47 CST 2016

Total time taken to generate the page: 0.10945 seconds