[prev] 41 [next]

Exercise 3: One vs Multiple Indices

Consider a relation with r = 100,000, B = 4K, defined as: 

create table Students (
    id       integer primary key,
    name     char(10), -- simplified
    gender   char(1)   -- 'm' or 'f',
    birthday date      -- 1980 .. 2000
);
... and a query on this relation ...
select * from Students
where  gender='m' and birthday='YYYY-02-29'

which has a B-tree index on each attribute ...

  • describe the selectivity of each attribute
  • estimate the cost of answering using one index
  • estimate the cost of answering using both indices