Materialized views: how can I find the number of updates, inserts, and deletes applied during the update?

Question

I have a dataset retrieved from our Oracle OLTP database using basic materialized views with quick update capabilities. The update is working fine. I want to add some statistics about the update of each materialized view, for example, the number of inserts, updates, and deletes that have been applied to the main table since the last update, like this data that I can find in user_tab_modifications . Is this possible for materialized representations?

Answer 1

Before you perform an update, you can query a materialized view log to find out which variable vectors it stores. These will be the vectors of changes that need to be applied to the materialized view during the update process (provided that there is only one materialized view that depends on this materialized view log).

For example, if I create my table, my materialized view log and my materialized view.

 SQL> create table foo( col1 number primary key); Table created. SQL> create materialized view log on foo; Materialized view log created. SQL> ed Wrote file afiedt.buf 1 create materialized view mv_foo 2 refresh fast on demand 3 as 4 select * 5* from foo SQL> / Materialized view created. SQL> insert into foo values( 1 ); 1 row created. SQL> insert into foo values( 2 ); 1 row created. SQL> commit; Commit complete. 

Now I am updating the materialized view and checking that the table and the materialized view are in sync

 SQL> exec dbms_mview.refresh( 'MV_FOO' ); PL/SQL procedure successfully completed. SQL> select * from user_tab_modifications where table_name = 'MV_FOO'; no rows selected SQL> select * from foo; COL1 ---------- 1 2 SQL> select * from mv_foo; COL1 ---------- 1 2 

Since the two objects are synchronized, the materialized view log is empty (the materialized view log will be named MLOG$_<<table name>>

 SQL> select * from mlog$_foo; no rows selected 

Now, if I insert a new row into the table, I will see the row in the materialized view log with DMLTYPE$$ of I with INSERT

 SQL> insert into foo values( 3 ); 1 row created. SQL> select * from mlog$_foo; COL1 SNAPTIME$ DO ---------- --------- - - CHANGE_VECTOR$$ -------------------------------------------------------------------------------- XID$$ ---------- 3 01-JAN-00 IN FE 2.2519E+15 

So, you can do something like this to get the number of pending inserts, updates, and deletes.

 SELECT SUM( CASE WHEN dmltype$$ = 'I' THEN 1 ELSE 0 END ) num_pending_inserts, SUM( CASE WHEN dmltype$$ = 'U' THEN 1 ELSE 0 END ) num_pending_updates, SUM( CASE WHEN dmltype$$ = 'D' THEN 1 ELSE 0 END ) num_pending_deletes FROM mlog$_foo 

However, if you update the materialized browsing history, this information will disappear.

On the other hand, USER_TAB_MODIFICATIONS should track the approximate number of changes made to the materialized view since the last statistics collection on it, as well as tracking information for the table. You will almost certainly need to call DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO to make the data visible if you want to capture data before and after updating the materialized view.

 SELECT inserts, updates, deletes INTO l_starting_inserts, l_starting_updates, l_starting_deletes FROM user_tab_modifications WHERE table_name = 'MV_FOO'; dbms_mview.refresh( 'MV_FOO' ); dbms_stats.flush_database_monitoring_info; SELECT inserts, updates, deletes INTO l_ending_inserts, l_ending_updates, l_ending_deletes FROM user_tab_modifications WHERE table_name = 'MV_FOO'; l_incremental_inserts := l_ending_inserts - l_starting_inserts; l_incremental_updates := l_ending_updates - l_starting_updates; l_incremental_deletes := l_ending_deletes - l_starting_deletes;