index for `where wakeup_time < sysdate`

Question

Hi

For most records this field is null. I need to wake up only some records.

The table is very big and I want to know which records to wake up. I don't want database to seek every row. Could you show me your idea?

Answer 1

Why don't you just create an index on wakeup_time? Also, you may fill it with a far away date by default, like 3000-01-01.

Answer 2

Create an ordinary index on just (WAKEUP_TIME). Only the rows where WAKEUP_TIME is not null will be stored in the index. When WAKEUP_TIME is updated to NULL, the entry will be removed from the index, so the index will be the most efficient way of finding all the rows that match your predicate.