I'm afraid this functionality may not exist [yet]. I wanted to use an index that spans multiple tables in DB2. I know Oracle and SQL server implement them (with more or less options) and that PostreSQL doesn't seem to implement them yet.
Note: I asked a similar question about PosgreSQL a few weeks ago.
A multi-table index could be very beneficial for some specific queries.
For reference, here are the multi-table index examples for Oracle and SQL Server:
Oracle Example
Oracle can create bitmap join indexes, as shown below:
create table dealer (
id int primary key not null,
city varchar2(20) not null
);
create table car (
id int primary key not null,
brand varchar2(20),
price int,
dealer_id int references dealer (id)
);
create bitmap index bix1 on car (d.city, c.brand)
from car c, dealer d
where d.id = c.dealer_id;
select avg(c.price)
from dealer d
join car c on c.dealer_id = d.id
where d.city = 'Chicago' and c.brand = 'Buick';
SQL Server Example
SQL Server can create indexed views:
create table dealer (
id int primary key not null,
city varchar(20) not null
);
create table car (
id int primary key not null,
brand varchar(20),
price int,
dealer_id int references dealer (id)
);
create view v with schemabinding as
select d.city, c.brand, c.price, c.dealer_id
from dbo.dealer d
join dbo.car c on c.dealer_id = d.id;
create unique clustered index uix1 on v (city, brand, price);
select avg(c.price)
from dealer d
join car c on c.dealer_id = d.id
where d.city = 'Chicago' and c.brand = 'Buick';
Is there anything similar in DB2?
