Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Selecting Rowid in "Create Materialized View" Statement

977687Dec 3 2012 — edited Dec 6 2012
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
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 3 2013
Added on Dec 3 2012
3 comments
397 views