Re: MView refresh question

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@xxxxxxx=513> DROP MATERIALIZED VIEW MVTESTE;

View materializada eliminada.

Decorrido: 00:00:00.17
23/05/12.SYSTEM@xxxxxxx=513> DROP TABLE TESTE;

Tabela eliminada.

Decorrido: 00:00:00.14
23/05/12.SYSTEM@xxxxxxx=513> 
23/05/12.SYSTEM@xxxxxxx=513> CREATE TABLE TESTE
  2  (A NUMBER PRIMARY KEY,
  3  B DATE);

Tabela criada.

Decorrido: 00:00:00.08
23/05/12.SYSTEM@xxxxxxx=513> 
23/05/12.SYSTEM@xxxxxxx=513> CREATE MATERIALIZED VIEW LOG ON TESTE;

Log de view materializada criado.

Decorrido: 00:00:00.12
23/05/12.SYSTEM@xxxxxxx=513> 
23/05/12.SYSTEM@xxxxxxx=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@xxxxxxx=513> 
23/05/12.SYSTEM@xxxxxxx=513> INSERT INTO TESTE VALUES (1,SYSDATE);

1 linha criada.

Decorrido: 00:00:00.01
23/05/12.SYSTEM@xxxxxxx=513> 
23/05/12.SYSTEM@xxxxxxx=513> SELECT COUNT(1) FROM TESTE;

  COUNT(1)
----------
         1

1 linha selecionada.

Decorrido: 00:00:00.03
23/05/12.SYSTEM@xxxxxxx=513> 
23/05/12.SYSTEM@xxxxxxx=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@xxxxxxx=513> 
23/05/12.SYSTEM@xxxxxxx=513> SELECT COUNT(1) FROM MLOG$_TESTE;

  COUNT(1)
----------
         1

1 linha selecionada.

Decorrido: 00:00:00.03
23/05/12.SYSTEM@xxxxxxx=513> 
23/05/12.SYSTEM@xxxxxxx=513> SELECT COUNT(1) FROM TESTE;

  COUNT(1)
----------
         1

1 linha selecionada.

Decorrido: 00:00:00.03
23/05/12.SYSTEM@xxxxxxx=513> 
23/05/12.SYSTEM@xxxxxxx=513> SELECT COUNT(1) FROM MVTESTE;

  COUNT(1)
----------
         0

1 linha selecionada.

Decorrido: 00:00:00.03
23/05/12.SYSTEM@xxxxxxx=513> 
23/05/12.SYSTEM@xxxxxxx=513> SELECT COUNT(1) FROM MLOG$_TESTE;

  COUNT(1)
----------
         1

1 linha selecionada.

Decorrido: 00:00:00.03
23/05/12.SYSTEM@xxxxxxx=513> 
23/05/12.SYSTEM@xxxxxxx=513> INSERT INTO TESTE VALUES (2,SYSDATE);

1 linha criada.

Decorrido: 00:00:00.01
23/05/12.SYSTEM@xxxxxxx=513> INSERT INTO TESTE VALUES (3,SYSDATE);

1 linha criada.

Decorrido: 00:00:00.01
23/05/12.SYSTEM@xxxxxxx=513> INSERT INTO TESTE VALUES (4,SYSDATE);

1 linha criada.

Decorrido: 00:00:00.01
23/05/12.SYSTEM@xxxxxxx=513> 
23/05/12.SYSTEM@xxxxxxx=513> COMMIT;

Commit concluído.

Decorrido: 00:00:00.03
23/05/12.SYSTEM@xxxxxxx=513> 
23/05/12.SYSTEM@xxxxxxx=513> SELECT COUNT(1) FROM TESTE;

  COUNT(1)
----------
         4

1 linha selecionada.

Decorrido: 00:00:00.03
23/05/12.SYSTEM@xxxxxxx=513> 
23/05/12.SYSTEM@xxxxxxx=513> SELECT COUNT(1) FROM MVTESTE;

  COUNT(1)
----------
         0

1 linha selecionada.

Decorrido: 00:00:00.03
23/05/12.SYSTEM@xxxxxxx=513> 
23/05/12.SYSTEM@xxxxxxx=513> SELECT COUNT(1) FROM MLOG$_TESTE;

  COUNT(1)
----------
         4

1 linha selecionada.

Decorrido: 00:00:00.03
23/05/12.SYSTEM@xxxxxxx=513> 
23/05/12.SYSTEM@xxxxxxx=513> EXEC DBMS_MVIEW.REFRESH ('MVTESTE','F');

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:00:00.13
23/05/12.SYSTEM@xxxxxxx=513> 
23/05/12.SYSTEM@xxxxxxx=513> SELECT COUNT(1) FROM TESTE;

  COUNT(1)
----------
         4

1 linha selecionada.

Decorrido: 00:00:00.03
23/05/12.SYSTEM@xxxxxxx=513> 
23/05/12.SYSTEM@xxxxxxx=513> SELECT COUNT(1) FROM MVTESTE;

  COUNT(1)
----------
         4

1 linha selecionada.

Decorrido: 00:00:00.03
23/05/12.SYSTEM@xxxxxxx=513> 
23/05/12.SYSTEM@xxxxxxx=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


Other related posts: