I have the following schema and data.
--drop table table_c;
--drop table table_b;
--drop table table_a;
create table table_a (
id number(3,0) primary key,
value varchar2(10)
);
create table table_b (
id number(3,0) primary key,
a_id number(3,0) not null,
value varchar2(10),
constraint b_fk1 foreign key (a_id) references table_a(id)
);
create table table_c (
id number(3,0) primary key,
a_id number(3,0) null,
b_id number(3,0) null,
value varchar2(10),
constraint c_fk1 foreign key (a_id) references table_a(id),
constraint c_fk2 foreign key (b_id) references table_b(id)
);
-- table a
insert into table_a (id, value) values (1, 'a');
insert into table_a (id, value) values (2, 'b');
-- table b
insert into table_b (id, a_id, value) values (1, 1, 'aa');
insert into table_b (id, a_id, value) values (2, 2, 'bb');
-- table c with ref to a
insert into table_c (id, a_id, value) values (1, 1, 'aaa');
insert into table_c (id, a_id, value) values (2, 2, 'bbb');
-- table c with ref to b
insert into table_c (id, b_id, value) values (3, 1, 'ccc');
insert into table_c (id, b_id, value) values (4, 2, 'ddd');
COMMIT;
It's basically a relation between table_a and table_c with a route through table_b if we don't have a direct link from table_c to table_a.
Each element in table_c will have either a_id or b_id filled. If we have a_id, we don't have b_id. If we have b_id, we don't have a_id. Both cannot be null at the same time, or non-null at the same time.
Now I'm asked to create a materialized view that shows the relation between table_a and table_c.
My first idea was to update table_c so that a_id is always up to date. The customer has a strong grip on the database and forbids me to do so!
--drop materialized view mv_d;
--drop materialized view log on table_c;
--drop materialized view log on table_b;
--drop materialized view log on table_a;
create materialized view log on table_a with rowid, sequence;
create materialized view log on table_b with rowid, sequence;
create materialized view log on table_c with rowid, sequence;
create materialized view mv_d
refresh fast on commit
enable query rewrite
as
select a.value as a_val,
c.value as c_val,
a.rowid as a_rowid,
b.rowid as b_rowid,
c.rowid as c_rowid
from table_a a,
table_b b,
table_c c
where (c.a_id is null and c.b_id = b.id and b.a_id = a.id)
or (c.a_id is not null and c.a_id = a.id);
execute dbms_stats.gather_table_stats( user, 'mv_d' ) ;
My problem with this mv is that the result isn't what I expect. Here's what I get. Note that the rowid are abbreviated to show their differences and the actual result, meaning why they are duplicates.
select * from mv_d;
-- note, the rowids are for information only, but are abbreviated to only show how they're different.
a_val | c_val | a_rowid | b_rowid | c_rowid
-------+-------+---------+---------+---------
a | aaa | GAAA | WAAA | mAAA
a | ccc | GAAA | WAAA | mAAC
a | aaa | GAAA | WAAB | mAAA
b | bbb | GAAB | WAAA | mAAB
b | bbb | GAAB | WAAB | mAAB
b | ddd | GAAB | WAAB | mAAD
Ideally, I'd get the following result from the select * from mv_d (bar the rowid columns, ofc).
a_val | c_val
-------+-------
a | aaa
a | ccc
b | bbb
b | ddd
How can I get that result in my materialized view?
Note that my actual database has respectively 3 million, 6 million and 1 million records for table_a, table_b, table_c. The actual result with everything filtered exists in a materialized view with roughly 10k records.
