Is it possible to restrict record creation in Mlog table based on few columns update?
Records created in mlog when only few columns like address/dob updated in base table. we have many column in base table like last_upd,last_db_access,... so we want to restrict fast refresh based on few columns only.
You can specify which columns to include in your MV log. But as far as I know you'll still get an entry in the log if you update any column.
For example, the following creates the MV log on Y (and X, the primary key). But updates to Z still generate rows in the log:
create table t (
x int primary key, y int , z int
);
create materialized view log on t with (y);
insert into t values (1, 1, 1);
commit;
select * from mlog$_t;
X Y SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ XID$$
1 1 01-JAN-4000 00:00:00 I N FE 3377794209808581
update t set z = 10;
update t set z = 5;
update t set z = 1;
select * from mlog$_t;
X Y SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ XID$$
1 1 01-JAN-4000 00:00:00 I N FE 3377794209808581
1 1 01-JAN-4000 00:00:00 U U 08 1688849860273700
1 1 01-JAN-4000 00:00:00 U U 08 1688849860273700
1 1 01-JAN-4000 00:00:00 U U 08 1688849860273700