oracle - Materialized View fast refresh taking a long time -

I have a large desk that is Oracle 10.2.0.4 and using MView replication on the network from Oracle 9i database Repeated. The master table is about 50 GB, about 160 M lines and there are approximately 2 - 3m new or updated rows per day.

A physical visual log has been created using the roaded in the master table.

Refresh the visual works completely and take approximately 5 hours, which we can live with.

However, it is struggling to keep refreshing fast. To refresh the Oracle, two questions are required against malog and master tables, first it looks like:

  SELECT / * + / DISTINCT "A1". "M_ROW $$" FROM "JENVA_ADMIN" "MOLG $ _BILLSUMMARY" "A1" Where "A1" "M_ORO $$" & lt; & Gt; Select all ("A2". "BINSUME_ADMIN". "BILLSUMMARY" "A2" WHERE "A2" .ROWID = "A1". "M_ROW $$") and "A1" "SNAPTIME $$" & gt; : 1 and "A1". "DMLTYPE $$" & lt; & Gt; 'I'  

The current plan is:

  --------------------- - ----------------------------------------- | ID | Operation | Name | -------------------------------------------------- ------------- | 0 | Select Command | | | 1 | Hash unique | | | 2 | Filter | | | 3 | Table access by INDEX ROWID. MLOG $ _BILLSUMMARY | | 4 | Index Range Scan | MLOG $ _BILLSUMMARY_AK1 | | 5 | User Access to table access by ROID.  

When 3M rows are changed, then this query really goes on forever - it's basically useless though, if I write it a little bit and the master table and I ask for the full scan of the malog table, then it is completed in 20 minutes.

The problem is that the above questions are coming out from the beginning of Oracle and I. It can not change the problem is actually a filter operation on line 2 - if I include it in both tables and hashes / anti-join I am sure that I can get it to be done quickly, but I will not get any receipt of signals, to stop the use of the filter operator. Rei - Maybe it's not even legitimate. I can use gestures to make it full scan of both the tables, but the filter operation remains, and I think it is 5 for each row for row 3 Performs, which will have 2-3m rows.

Thanks, <,

/ P>

Stefan As you've written that questions are part of an internal Oracle system, your tuning options are limited as