Re: Query execution for intervals
From: -CELKO- <jcelko212_at_earthlink.net>
Date: 5 Feb 2006 08:22:34 -0800
Message-ID: <1139156554.415774.101630_at_z14g2000cwz.googlegroups.com>
Date: 5 Feb 2006 08:22:34 -0800
Message-ID: <1139156554.415774.101630_at_z14g2000cwz.googlegroups.com>
CREATE TABLE Events
>> 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 ..
(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));
FROM Events
WHERE :my_date BETWEEN start_date AND COALESCE (end_date,
CURRENT_TIMESTAMP);