Avoiding union by join?

Question

Hi,

My problem is on Oracle, but is probably database independent (?).

I have the following tables:

aa

vid   cb
---   --
  1   10
  2   15

bb

vid   cb
---   --
  3   25
  4   24

*rep

repid  vid   p
-----  ---  --
   99    1  aa
   99    2  aa
   99    3  bb
   99    4  bb

The column p indicates in which table to get the row. In reality, aa and bb are much more different, and p does not match to the table name, but gives a way to get there. The example is just a simplication where I have a problem. Note than in reality, there are more than 2 tables aa and bb (there are 6). I want a query that returns this:

repid  vid   p  cb
-----  ---  --  --
   99    1  aa  10
   99    2  aa  15
   99    3  bb  25
   99    4  bb  24

The following works: (a)

select rep.vid, rep.p, cb 
from (
select 'aa' as p,vid,cb from aa
union all 
select 'bb' as p, vid,cb from bb) u,rep
where rep.p=u.p and rep.vid=u.vid

(b)

select rep.vid, rep.p, 
   decode(rep.p, 'aa', (select cb from aa where vid=rep.vid), 
                 'bb', (select cb from bb where vid=rep.vid)) cb
from rep

But I would like to use the query in a view, on which there can be predicate pushing.

So question 1 is: would the following allow predicate pushing. Question 2: (even if yes for question 1) is there a way to do this without union, but with joins. Question 3: Or just simply, a better way?

Script to create the data:

create table bb (vid number(1), cb number(2));
create table aa (vid number(1), cb number(2));
create table rep(rid number(2), vid number(1), p varchar2(2));
insert into rep (rid,vid,p) values (99, 4,'bb');
insert into rep (rid,vid,p) values (99, 3,'bb');
insert into rep (rid,vid,p) values (99, 2,'aa');
insert into rep (rid,vid,p) values (99, 1,'aa');
insert into bb (vid,cb) values (4,24);
insert into bb (vid,cb) values (3,25);
insert into aa (vid,cb) values (2,15);
insert into aa (vid,cb) values (1,10);
commit;

Answer 1

A join can specify multiple conditions. The table name can be one. For example, if table1 has a column called TableName that references other tables, you could use:

select      *
from        table1 t1
left join   table2 t2
on          t1.TableName = 'table2'
            and t1.id = t2.id
left join   table3 t3
on          t1.TableName = 'table3'
            and t1.id = t3.id

You can add an arbitrary number of tables this way.

As to your third question, there is always a better way. The question is, does this way suffice? If not, can you define the requirements for an acceptable solution?

Answer 2

I don't have an Oracle instance to hand any more, but did try things with PostgreSQL, which might be of interest anyway?

My experiment with PostgreSQL suggests that actually the union works better. I created a view based on your union query, and postgres was able to push a predicate such as "cb BETWEEN 12 AND 27" into the scans of aa and bb.

By constrast, I created a view that uses joins:

create view rep2 as
  select rep.vid, p, coalesce(aa.cb, bb.cb) as cb
  from rep
       left join aa on aa.vid = rep.vid and rep.p = 'aa'
       left join bb on bb.vid = rep.vid and rep.p = 'bb'

The problem now is that the coalesce() blocks a predicate involving cb being pushed into the scans of aa and bb.