Index Organized Materialized View

Having a materialized view joining two tables. I want to create the materialized view as a index organized table. Materialized view should be possible to be an IOT as documented. Examples of such may be found from support pages "How to create an Index-Organized Materialized View (Snapshot) [ID 114272.1]". But the examples are only for query on top of single table. No join in the materialized view query. There is the ORAGANIZATION INDEX opportunity in create materialized view, but the statement does not have the possibility to state the primary key constraint for the view.

It is possible to create a materialized view ON PREBUILT TABLE. The table may be index organized and attach the materialized view query on top to the existing table. Here is an example of such.

drop materialized view ab;

drop table ab;

drop table b;

drop table a;


create table a(aid int primary key, aname varchar2(20));

create table b(bid int primary key, aid references a, bname varchar2(20));


create table ab(aid int, bid int constraint ab_pk primary key, aname varchar2(20), bname varchar2(20)) organization index;

create materialized view ab (aid, bid, aname, bname) 
on prebuilt table
as
select a.aid, b.bid, a.aname, b.bname
  from a, b
 where a.aid=b.aid
;

insert into a values (1,'A');

insert into b values (1,1,'B');


insert into a values (2,'C');

insert into b values (2,2,'D');


commit;

exec dbms_mview.refresh('AB','C');

select * from ab;

1 1 A B
2 2 C D

select segment_name,segment_type from user_segments where segment_name like 'AB_PK';

AB_PK    INDEX

select table_name,iot_type from user_tables where table_name = 'AB';

AB    IOT