| Materialized View [message #152354] |
Thu, 22 December 2005 12:45  |
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 #152371 is a reply to message #152363] |
Thu, 22 December 2005 13:34  |
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 |
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 |
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 #152899 is a reply to message #152898] |
Tue, 27 December 2005 11:41 |
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 |
skooman
Messages: 913
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 |
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 |
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 |
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 #328260 is a reply to message #327796] |
Thu, 19 June 2008 09:52 |
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;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
