Re: Query execution for intervals

CREATE TABLE Events
(event_name CHAR(10) NOT NULL,
 start_date DATE NOT NULL,
 end_date DATE, -- null means active
 CHECK (start_date <= end_date),
  PRIMARY KEY (start_date, event_name));

>> Is it possible for the DBMS to execute this query faster than linear time? <<

Yes. Most products will use a tree index for the primary key. Since the start date is the first column in that index, it will be searchable.

As a matter of style, I would write:

SELECT ..
  FROM Events
WHERE :my_date BETWEEN start_date AND COALESCE (end_date, CURRENT_TIMESTAMP); if the response is still too slow, try putting the end_date into the PRIMARY KEY to get a covering index. Received on Sun Feb 05 2006 - 10:22:34 CST