Does DB2 implement multi-table indexes?

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?

Answer

Db2 (for Linux, UNIX, and Windows) supports indexes on tables, i.e., you can only index a single table.

The table can be a MQT (materialized query table) which could be based on a view. This is different from indexing multiple tables directly.

Advertisement

Answer