Olivier Grégoire Olivier Grégoire - 23 days ago 5
SQL Question

Relation between two tables with an optional third table in between

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 results in a materialized view with roughly 10k records.

Answer
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

            left join   table_b b

            on          b.a_id  = a.id                            

            join        table_c c

            on          c.a_id  = a.id
                    or  c.b_id  = b.id
;

For the materialized view please use this old style code

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       b.a_id (+)  = a.id   

        and (    c.a_id  = a.id
             or  c.b_id  = b.id
            )
;