CODEWITHSUNDEEP
indexingdb2
mkuff
mkuff

Reputation: 1682

DB2 Index over two Date fields

I am quite new to DB2 and i want to support my query with an index over two date columns:

WHERE (t0.start_date <= ? AND (t0.till_date > ? OR t0.till_date IS 
    NULL))

Is there an optimal way to place the index or is it better just to index till_date?

Second question is do i have to manually put indexes on foreign key columns?

best regards, m

Upvotes: 1

Views: 903

Answers (1)

mustaccio
mustaccio

Reputation: 18945

In multi-column indexes it is better to have columns with higher cardinalities (most distinct values) towards the beginning. In your example, assuming start_date is never null and till_date can be null, it would be better to build the index on start_date, till_date.

DB2 does not automatically create indexes for referential integrity constraints. It requires a unique or primary key constraint on the parent columns, and those do create indexes automatically, but you will have to manually create the corresponding index on the child table.

Upvotes: 2

Related Questions