Re: MView refresh question
Date: Wed, 23 May 2012 10:51:43 -0300
Message-ID: <2c0106857481bb839127f249f38dca08.squirrel_at_webmail.mufalani.com.br>
Hi raj,
You can query the mview log to verify how many rows will be processed before refresh. I hope that test script bellow can help you.
23/05/12.SYSTEM_at_dsv.SID=513> DROP MATERIALIZED VIEW MVTESTE;
View materializada eliminada.
Decorrido: 00:00:00.17
23/05/12.SYSTEM_at_dsv.SID=513> DROP TABLE TESTE;
Tabela eliminada.
Decorrido: 00:00:00.14
23/05/12.SYSTEM_at_dsv.SID=513>
23/05/12.SYSTEM_at_dsv.SID=513> CREATE TABLE TESTE
2 (A NUMBER PRIMARY KEY,
3 B DATE);
Tabela criada.
Decorrido: 00:00:00.08
23/05/12.SYSTEM_at_dsv.SID=513>
23/05/12.SYSTEM_at_dsv.SID=513> CREATE MATERIALIZED VIEW LOG ON TESTE;
Log de view materializada criado.
Decorrido: 00:00:00.12
23/05/12.SYSTEM_at_dsv.SID=513>
23/05/12.SYSTEM_at_dsv.SID=513> CREATE MATERIALIZED VIEW MVTESTE
2 BUILD IMMEDIATE
3 REFRESH FAST WITH PRIMARY KEY
4 AS
5 SELECT * FROM TESTE;
View materializada criada.
Decorrido: 00:00:00.12
23/05/12.SYSTEM_at_dsv.SID=513>
23/05/12.SYSTEM_at_dsv.SID=513> INSERT INTO TESTE VALUES (1,SYSDATE);
1 linha criada.
Decorrido: 00:00:00.01
23/05/12.SYSTEM_at_dsv.SID=513>
23/05/12.SYSTEM_at_dsv.SID=513> SELECT COUNT(1) FROM TESTE;
COUNT(1)
1
1 linha selecionada.
Decorrido: 00:00:00.03
23/05/12.SYSTEM_at_dsv.SID=513>
23/05/12.SYSTEM_at_dsv.SID=513> SELECT * FROM DBA_MVIEW_LOGS WHERE MASTER='TESTE';
LOG_OWNER MASTER LOG_TABLE LOG_TRIGGER
ROW PRI Obj FIL
------------------------------ ------------------------------ ------------------------------
------------------------------ --- --- --- ---
SEQ INC PUR PUR PURGE_START
--- --- --- --- --------------------
PURGE_INTERVAL
LAST_PURGE_DATE LAST_PURGE_STATUS NUM_ROWS_PURGED COM
-------------------- ----------------- --------------- ---
SYSTEM TESTE MLOG$_TESTE
NO YES NO NO
NO NO NO NO
23-MAI-2012 10:15:17 0 0 NO
1 linha selecionada.
Decorrido: 00:00:00.08
23/05/12.SYSTEM_at_dsv.SID=513>
23/05/12.SYSTEM_at_dsv.SID=513> SELECT COUNT(1) FROM MLOG$_TESTE;
COUNT(1)
1
1 linha selecionada.
Decorrido: 00:00:00.03
23/05/12.SYSTEM_at_dsv.SID=513>
23/05/12.SYSTEM_at_dsv.SID=513> SELECT COUNT(1) FROM TESTE;
COUNT(1)
1
1 linha selecionada.
Decorrido: 00:00:00.03
23/05/12.SYSTEM_at_dsv.SID=513>
23/05/12.SYSTEM_at_dsv.SID=513> SELECT COUNT(1) FROM MVTESTE;
COUNT(1)
0
1 linha selecionada.
Decorrido: 00:00:00.03
23/05/12.SYSTEM_at_dsv.SID=513>
23/05/12.SYSTEM_at_dsv.SID=513> SELECT COUNT(1) FROM MLOG$_TESTE;
COUNT(1)
1
1 linha selecionada.
Decorrido: 00:00:00.03
23/05/12.SYSTEM_at_dsv.SID=513>
23/05/12.SYSTEM_at_dsv.SID=513> INSERT INTO TESTE VALUES (2,SYSDATE);
1 linha criada.
Decorrido: 00:00:00.01
23/05/12.SYSTEM_at_dsv.SID=513> INSERT INTO TESTE VALUES (3,SYSDATE);
1 linha criada.
Decorrido: 00:00:00.01
23/05/12.SYSTEM_at_dsv.SID=513> INSERT INTO TESTE VALUES (4,SYSDATE);
1 linha criada.
Decorrido: 00:00:00.01
23/05/12.SYSTEM_at_dsv.SID=513>
23/05/12.SYSTEM_at_dsv.SID=513> COMMIT;
Commit concluído.
Decorrido: 00:00:00.03
23/05/12.SYSTEM_at_dsv.SID=513>
23/05/12.SYSTEM_at_dsv.SID=513> SELECT COUNT(1) FROM TESTE;
COUNT(1)
4
1 linha selecionada.
Decorrido: 00:00:00.03
23/05/12.SYSTEM_at_dsv.SID=513>
23/05/12.SYSTEM_at_dsv.SID=513> SELECT COUNT(1) FROM MVTESTE;
COUNT(1)
0
1 linha selecionada.
Decorrido: 00:00:00.03
23/05/12.SYSTEM_at_dsv.SID=513>
23/05/12.SYSTEM_at_dsv.SID=513> SELECT COUNT(1) FROM MLOG$_TESTE;
COUNT(1)
4
1 linha selecionada.
Decorrido: 00:00:00.03
23/05/12.SYSTEM_at_dsv.SID=513>
23/05/12.SYSTEM_at_dsv.SID=513> EXEC DBMS_MVIEW.REFRESH ('MVTESTE','F');
Procedimento PL/SQL concluído com sucesso.
Decorrido: 00:00:00.13
23/05/12.SYSTEM_at_dsv.SID=513>
23/05/12.SYSTEM_at_dsv.SID=513> SELECT COUNT(1) FROM TESTE;
COUNT(1)
4
1 linha selecionada.
Decorrido: 00:00:00.03
23/05/12.SYSTEM_at_dsv.SID=513>
23/05/12.SYSTEM_at_dsv.SID=513> SELECT COUNT(1) FROM MVTESTE;
COUNT(1)
4
1 linha selecionada.
Decorrido: 00:00:00.03
23/05/12.SYSTEM_at_dsv.SID=513>
23/05/12.SYSTEM_at_dsv.SID=513> SELECT COUNT(1) FROM MLOG$_TESTE;
COUNT(1)
0
1 linha selecionada.
Decorrido: 00:00:00.03
Probably a silly question but I have a brain freeze ... In this 11gr2 env, we will be refreshing a mview over a dblink, developers want to know " is there a way that the dbms_mview.refresh() can tell us if it added any new rows without we doing count(*) post refresh " ? To save processing time, we are trying to see if polling for new rows can be eliminated. If this refresh can detect new rows and tell us, we will set a dbms_alert and responsible process will come by and do its work. All to reduce un-necessary polling.
I have looked in docs, googled, binged but didn't get any hits. Does anyone know if it is possible ?
Raj
--
http://www.freelists.org/webpage/oracle-l
Atenciosamente,
Rodrigo Mufalani
Oracle Ace Member
Tel.: +55 21 88994817
http://www.mufalani.com.br
<img src=http://mufalani.com.br/site/wp-content/uploads/2012/01/logo.png /img>
--
http://www.freelists.org/webpage/oracle-l
Received on Wed May 23 2012 - 08:51:43 CDT