Re: Query execution for intervals

> if the response is still too slow, try putting the end_date into the
> PRIMARY KEY to get a covering index.

Can end_date be part of the PRIMARY KEY if it is declared NULL ?

"-CELKO-" <jcelko212_at_earthlink.net> wrote in message news:1139156554.415774.101630_at_z14g2000cwz.googlegroups.com...
> 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 - 21:44:22 CET