I am on Oracle EE 10g. I need to create a materialized view with a two-table join that is FAST refreshable. The only way I've found that Oracle allows me to do this is to create both the materialized view log and materialized view itself with ROWID (as follows):
create materialized view log on TABLE1 with rowid;
create materialized view log on TABLE2 with rowid;
create materialized view SAMPLE_MV
refresh fast with rowid
enable query rewrite
select r.*, pt.terr_cd, r.rowid r_rowid, pt.rowid pt_rowid
from TABLE 1 r, TABLE 2 pt
where r.txn_seq_no = pt.txn_seq_no;
(*txn_seq_no is the PK on TABLE2)
The problem is that this requires having to select the rowid from both underlying tables as part of the materialized view's select statement. I do not want these columns to be visiable to the user and I do not want to create another view or materialized view on top of this one to filter them out. Is there any way to avoid having to select the rowid's as part of the materialized view? Or a way to essentially "hide" them within the database on the mv object itself (other than using another object on top of it to filter thru)?
Is there any way to avoid having to select the rowid's as part of the materialized view?
No, unless you'd like to sacrifice FAST refresh option.
Or a way to essentially "hide" them within the database on the mv object itself
The only thing that comes to mind is column masking that is part of Virtual Private Database, but I’m afraid it’s an overkill for this problem.
Why are you so opposed to creating a view on top of the mv?