SQL & PL/SQL

I am building fast refresh MVs on a 3rd party database to enable faster reporting. This is an interim solution whilst we build a new ETL process using CDC.

The source DB has no PKs, so I'm creating the MV logs with ROWID. When I refresh the MV (exec DBMS_MVIEW.REFRESH('<mview_name>') and trace the session I notice:
1. The query joins back to the base table - I think this is necessary as there are two base tables and the MV change could be instigated from either table independently. Therefore the changes might not be in the log.
2. However in this case shouldn't it be possible to just joij mv_log1 to base_table2 and ignore base_table1?
3. There is a HASH_SJ hint in this join, forcing a full table scan on the 7M row base_table1.
4. I am doing 1 update then refreshing the MV
5. In production this table would have many 10s of single row inserts and updates per minute

This is an excerpt from the tkprof'd trace file (I've hidden some table/column names)

...
FROM   (SELECT MAS$.ROWID RID$  
              ,MAS$.*  
        FROM   <base_table1> MAS$ 
        WHERE  ROWID IN (SELECT  /*+ HASH_SJ */  
                                CHARTOROWID(MAS$.M_ROW$$) RID$     
                         FROM   <mview_log1> MAS$   
                         WHERE  MAS$.SNAPTIME$$ > sysdate-1/24 --:1 
                        )
       ) AS OF SNAPSHOT (:2) JV$
       ,<base_table2> AS OF SNAPSHOT (:2)  MAS$0 
WHERE   JV$.<col1>=MAS$0.<col1> 
AND     JV$.<col2>=MAS$0.<col2> 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1     13.78     153.32     490874     551013          3           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     13.78     153.32     490874     551013          3           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 277  (<user>)   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID <base_table2>(cr=551010 pr=490874 pw=0 time=153321352 us)
      3   NESTED LOOPS  (cr=551009 pr=490874 pw=0 time=647 us)
      1    VIEW  (cr=551006 pr=490874 pw=0 time=153321282 us)
      1     HASH JOIN RIGHT SEMI (cr=551006 pr=490874 pw=0 time=153321234 us)
      2      TABLE ACCESS FULL <base_table1_mv_log> (cr=21 pr=0 pw=0 time=36 us)
7194644      TABLE ACCESS FULL <base_table1>(cr=550985 pr=490874 pw=0 time=158282171 us)
      1    INDEX RANGE SCAN <base_table2_index> (cr=3 pr=0 pw=0 time=22 us)(object id 3495055)

As you can see there are two rows in the MV log (one update, old and new values), the FTS on the base table ensure that the MV refresh is far from fast

I have tried this with refreshing on demand and commit with similar results. Implementing this would make my the application impossibly slow.

I will search the knowledge base once I am given access

SQL>select * from v$version;

BANNER
________________________________________________________________
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Thank you for taking the time to read/respond.

Ben