Dear Oracle-Team,

we need a daily snapshot from the company's personal data for our software.
For that reason we want to use an index-organized materialized view (with daily 'refesh complete'). Unfortunately there are two user id's for every employee in the company. The primary key in the master table is the USERID_A. But for our software we are only using USERID_B (see LiveSQL Link - Step 1-6). But because that is not the primary key oracle perform an 'INDEX FAST FULL SCAN' for that search (Step 7, 8). Of cource I can add an index for the USERID_B but than I will miss the performance advantage of an index-organized materialized view (Step 9-11).

Summary:
* we only need an 'refesh complete'
* we need the mv read-only
* we want to have performance advantages from an index-organized materialized view

Question: With the summary points in mind, can I create an mv with an different primary key than the master table?

Thank you in advance for your expertise!

Greetings, Walter

PS: For sure I can fix this problem also on an other way (IOT with an job), but the way with an index-organized materialized view seems to be much more elegent.

SQL> create table t as
  2    select
  3      object_id userid1,
  4      object_id userid2,
  5      owner,
  6      object_name
  7  from dba_objects
  8  where object_id is not null;

Table created.

SQL>
SQL> alter table t add primary key ( userid1);

Table altered.

SQL>
SQL> create table t_mv ( userid1, userid2, owner, object_name, constraint t_mv_pk primary key ( userid2)   )
  2  organization index as select * from t;

Table created.

SQL> create materialized view t_mv
  2  on prebuilt table
  3  refresh complete on demand
  4  as select * from t ;

Materialized view created.

SQL> exec dbms_mview.refresh('T_MV')

PL/SQL procedure successfully completed.