Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Query execution for intervals

Re: Query execution for intervals

From: <lennart_at_kommunicera.umea.se>
Date: 5 Feb 2006 08:27:21 -0800
Message-ID: <1139156841.445991.166580@o13g2000cwo.googlegroups.com>

frebe73_at_gmail.com wrote:
> Hi,
> I have a table representing events in a calendar
> event(id, start, end, ....)
>
> I have two indexes, one on the start column and one on the end column.
>
> I have a select query for finding all events containing a given time
> select *
> from event
> where start <= :time and end >= :time
>
> Is it possible for the DBMS to execute this query faster than linear
> time? From my understanding the DBMS can use one of the indexes, lets
> say the start index, to find every event starting before the given
> time. Then all these events has to be traversed to check if the end
> time is after the given time. The only thing the query optimizer migth
> do is to choose to use the the start index or the end index depending
> on the time value. But it will still be a linear search.
>
> Are my assumptions correct? Are any way to change the database
> structure to enable a search faster than linear time.
>
> Best regards,
> frebe

Not sure what you mean by linear time, but for this query (IMHO) it is probably better with an index like:

create index x1 on calendar_event (event_start, event_end)

than to have two separate indexes. Also, for this query it probably wont help, but it migth be a good idea to inform the optimizer that event_start <= event_end

alter table calendar_event add constraint whatever check (event_start, event_end)

HTH
/Lennart Received on Sun Feb 05 2006 - 10:27:21 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US