CREATE TABLE data1_tab 
( 
fk_key NUMBER, 
col1 varchar2(10), 
part_col varchar2(5) 
) PARTITION BY LIST (part_col)  AUTOMATIC 
(PARTITION P1 VALUES ('PART1'))

CREATE TABLE data2_tab 
( 
fk_key NUMBER, 
col2 varchar2(10), 
part_col varchar2(5) 
) PARTITION BY LIST (part_col)  AUTOMATIC 
(PARTITION P1 VALUES ('PART1'))

CREATE MATERIALIZED VIEW LOG ON data1_tab 
NOCACHE 
WITH ROWID, COMMIT SCN, SEQUENCE (fk_key,col1,part_col) 
INCLUDING NEW VALUES 
FOR FAST REFRESH

CREATE MATERIALIZED VIEW LOG ON data2_tab 
NOCACHE 
WITH ROWID, COMMIT SCN, SEQUENCE (fk_key,col2,part_col) 
INCLUDING NEW VALUES 
FOR FAST REFRESH

CREATE MATERIALIZED VIEW data1j2_tab_mv 
PARTITION BY LIST (data1_tab_part_col)  AUTOMATIC 
(PARTITION P1 VALUES ('PART1')) 
BUILD IMMEDIATE  
REFRESH FAST 
ON DEMAND 
ENABLE QUERY REWRITE  
AS 
SELECT  data1_tab.fk_key as data1_tab_fk_key,data2_tab.fk_key as data2_tab_fk_key, 
data1_tab.part_col as data1_tab_part_col,data2_tab.part_col as data2_tab_part_col,  
data1_tab.col1,data2_tab.col2, 
data1_tab.rowid as data1_tab1_rowid, data2_tab.rowid as data1_tab2_rowid 
FROM   data1_tab, data2_tab 
WHERE data1_tab.fk_key=data2_tab.fk_key and data1_tab.part_col=data2_tab.part_col 

CREATE INDEX data1j2_tab_mv_idx1 on data1j2_tab_mv(data1_tab_fk_key,data1_tab_part_col) local

CREATE INDEX data1j2_tab_mv_idx2 on data1j2_tab_mv(data2_tab_fk_key,data2_tab_part_col) local

CREATE INDEX data1j2_tab_mv_idx3 on data1j2_tab_mv(data1_tab1_rowid,data1_tab_part_col) local

CREATE INDEX data1j2_tab_mv_idx4 on data1j2_tab_mv(data1_tab2_rowid,data2_tab_part_col) local

CREATE INDEX data1j2_tab_mv_idx5 on data1j2_tab_mv(col1,col2,data1_tab_part_col) local

INSERT INTO data1_tab VALUES (1,'A','P_A');
INSERT INTO data1_tab VALUES (1,'B','P_A');
INSERT INTO data1_tab VALUES (2,'C','P_A');
INSERT INTO data1_tab VALUES (2,'C','P_A');
INSERT INTO data1_tab VALUES (2,'Z','P_A');

INSERT INTO data2_tab VALUES (1,'WQ','P_A');
INSERT INTO data2_tab VALUES (1,'MN','P_A');
INSERT INTO data2_tab VALUES (2,'PO','P_A');
INSERT INTO data2_tab VALUES (2,'RE','P_A');
INSERT INTO data2_tab VALUES (1,'JU','P_A');

COMMIT;