3 Replies Latest reply: Dec 6, 2012 11:24 AM by 977687 RSS

    Selecting Rowid in "Create Materialized View" Statement

    977687
      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