Selecting Rowid in "Create Materialized View" Statement
977687Dec 3 2012 — edited Dec 6 2012I 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
nologging
refresh fast with rowid
enable query rewrite
as
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)?
Thanks,
Deb