Hi Tom,
Thanks a lot for all the inputs!!!
I am still not clear about the "including new values" clause usage and cannot seem to find details elsewhere.
Referring to your link and quoting from it: 

**================== begin ===========================**
SQL> create table t1 ( a  int,
  2     b int,
  3     y number,
  4     primary key(a,b) )
  5  /
Table created.

On this table, we will need to keep a materialized view log to capture the changes made to T1 during our transaction. 
We'll keep a summary of column Y rolled up by the values of column A—hence those columns must appear in our materialized view log: 

SQL>    create materialized view log on t1 
  2  with rowid ( a, y ) including new values
  3  /
Materialized view log created.

We are ready for our materialized view: 

SQL> create materialized view t2
  2  build immediate
  3  refresh on commit
  4  as
  5  select a, count(*), count(y), sum(y) from t1 group by a
  6  /
Materialized view created.
**=================== end ==========================**

Please Note: "hence those columns must appear in our materialized view log"
a) Hence, for fast refreshable aggregate Materialized View we definitely need the "column list" which contains all columns getting aggregated. One has to include all columns mentioned in the query. Otherwise one gets Oracle error ORA-12033.
My understanding is: For fast refreshable aggrgate views all the columns getting aggregated need to be included.

b) What I am not understanding is: 
1)Does the "including new values" clause any good (does it help/provide additional info and is it even required for any reason) when you omit the column list when creating the MV Log? 
2) The new/updated values for the columns would be in the master table but the old values are lost as the column list to store the values is not there in the MV log definition.
3) The Materialized View Log would be having an additional record per transaction in the log table, what good it is (for what purpose) ?
The change_vector$$  as per my understanding can basically be used to 
see which columns were updated but does not have the actual (old/new) values of those columns.

c) I guess the MV Log without column list would only be applicable to non-aggregate Materialized Views as else one may get errors when creating/refreshing the Materialized View itself.I feel the statements below:

1) create materialized view log on t1 
     with rowid including new values
/
(would add an additional record in the MV log table: an overhead with no benefit??)

and 
2) create materialized view log on t1 
     with rowid excluding new values
/

provide us the same information.

Please advise. 

Can a materialized view contain columns from multiple tables?  I'm suspecting that's the reason that I got ORA-12033 error, but I'm not too sure.

SQL> 
SQL> CREATE TABLE x_objects NOLOGGING AS
  2  SELECT * FROM all_objects;

Table created.

SQL> 
SQL> CREATE TABLE x_users NOLOGGING AS
  2  SELECT * FROM all_users;

Table created.

SQL> 
SQL> CREATE MATERIALIZED VIEW LOG ON x_objects
  2  WITH ROWID, SEQUENCE (object_type) INCLUDING NEW VALUES;

Materialized view log created.

SQL> 
SQL> CREATE MATERIALIZED VIEW LOG ON x_users
  2  WITH ROWID, SEQUENCE (user_id) INCLUDING NEW VALUES;

Materialized view log created.

SQL> 
SQL> 
SQL> CREATE MATERIALIZED VIEW x_object_cnt_MV
  2     BUILD IMMEDIATE
  3     REFRESH FAST
  4     ON DEMAND
  5  AS
  6  SELECT b.user_id, a.objecT_type, COUNT(*) cnt
  7  FROM   x_objects a, x_users b
  8  WHERE  a.owner = b.username
  9  GROUP  BY b.user_id, a.objecT_type;
FROM   x_objects a, x_users b
       *
ERROR at line 7:
ORA-12033: cannot use filter columns from materialized view log on "SCOTT"."X_USERS"
 

Hi Tom,

Thanks a lot for this discussion! Its been very helpful.

I am using WITH SEQUENCE, INCLUDING NEW VALUES for my agregate MV.

The view us updated nicely whenever I insert and commit a row, but not for update/delete! its driving me nuts.

Here is a short script.


SQL*Plus: Release 11.1.0.6.0 - Production on Thu May 12 15:19:41 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Enter user-name: user/password@localhost:1521/orcl

Connected to:
Personal Oracle Database 11g Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table test (col1 char(2), col2 char(2), col3 char(2));

Table created.

SQL>
SQL> alter table test add primary key (col1, col2);

Table altered.

SQL> create materialized view log on test with sequence, rowid (col1, col3)
  2  including new values;

Materialized view log created.

SQL>
SQL> insert into test values ('01', '01', 'AA');

1 row created.

SQL> insert into test values ('01', '02', 'AA');

1 row created.

SQL> insert into test values ('01', '03', 'BB');

1 row created.

SQL> insert into test values ('01', '05', 'BB');

1 row created.

SQL> insert into test values ('01', '06', 'CC');

1 row created.

SQL> insert into test values ('02', '01', 'AA');

1 row created.

SQL> insert into test values ('02', '02', 'BB');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> create materialized view mv_test
  2  refresh fast on commit
  3  as
  4  select col1,
  5  Sum(decode(col3, 'AA', 1, 0)) AA_Count,
  6  Sum(decode(col3, 'CC', 1, 0)) CC_Count
  7  from test
  8  group by col1;

Materialized view created.

SQL> select * from mv_test;

CO   AA_COUNT   CC_COUNT
-- ---------- ----------
01          2          1
02          1          0

SQL> insert into test values ('02', '03', 'CC');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from MLOG$_TEST;

no rows selected

SQL> insert into test values ('02', '04', 'CC');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from MLOG$_TEST;

no rows selected

SQL> select * from mv_test;

CO   AA_COUNT   CC_COUNT
-- ---------- ----------
01          2          1
02          1          2

SQL> Update test set col3 = 'AA' where col1 = '02' and col2 = '03';

1 row updated.

SQL> Commit;

Commit complete.

SQL> select * from test;

CO CO CO
-- -- --
01 01 AA
01 02 AA
01 03 BB
01 05 BB
01 06 CC
02 01 AA
02 02 BB
02 03 AA
02 04 CC

9 rows selected.

SQL> select * from mv_test;

CO   AA_COUNT   CC_COUNT
-- ---------- ----------
01          2          1
02          1          2

SQL> delete from test where col1 = '02';

4 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from mv_test;

CO   AA_COUNT   CC_COUNT
-- ---------- ----------
01          2          1
02          1          2

SQL>select * from MLOG$_TEST;

02 BB AAARn4AAFAADgJyAAG 01/01/00 D O 00 27
02 AA AAARn4AAFAADgJyAAH 01/01/00 D O 00 28
02 AA AAARn4AAFAADgJyAAF 01/01/00 D O 00 26
02 CC AAARn4AAFAADgJyAAI 01/01/00 D O 00 29

After Update / Delete the result still remains the same, but Insert its able to FAST refresh on Commit!