CREATE TABLE data1_tab
(
fk_key NUMBER,
col1 varchar2(10),
part_col varchar2(5)
) PARTITION BY LIST (part_col) AUTOMATIC
(PARTITION P1 VALUES ('PART1'))
Table created.
CREATE TABLE data2_tab
(
fk_key NUMBER,
col2 varchar2(10),
part_col varchar2(5)
) PARTITION BY LIST (part_col) AUTOMATIC
(PARTITION P1 VALUES ('PART1'))
Table created.
CREATE MATERIALIZED VIEW LOG ON data1_tab
NOCACHE
WITH ROWID, COMMIT SCN, SEQUENCE (fk_key,col1,part_col)
INCLUDING NEW VALUES
FOR FAST REFRESH
Statement processed.
CREATE MATERIALIZED VIEW LOG ON data2_tab
NOCACHE
WITH ROWID, COMMIT SCN, SEQUENCE (fk_key,col2,part_col)
INCLUDING NEW VALUES
FOR FAST REFRESH
Statement processed.
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
Statement processed.
CREATE INDEX data1j2_tab_mv_idx1 on data1j2_tab_mv(data1_tab_fk_key,data1_tab_part_col) local
Index created.
CREATE INDEX data1j2_tab_mv_idx2 on data1j2_tab_mv(data2_tab_fk_key,data2_tab_part_col) local
Index created.
CREATE INDEX data1j2_tab_mv_idx3 on data1j2_tab_mv(data1_tab1_rowid,data1_tab_part_col) local
Index created.
CREATE INDEX data1j2_tab_mv_idx4 on data1j2_tab_mv(data1_tab2_rowid,data2_tab_part_col) local
Index created.
CREATE INDEX data1j2_tab_mv_idx5 on data1j2_tab_mv(col1,col2,data1_tab_part_col) local
Index created.
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;