Re: Using Index to find row near value

On 13.03.2007 08:38, Björn Wächter wrote:
> Hi all,
>
> I have a table with a TIMESTAMP column. Now I try to search
> the row where the TIMESTAMP of the row is less than a given value
> and the next TIMESTAMP in the table is greater than the given value.
> I can find this row with this query:
>
> SELECT
> *
> FROM
> (
> SELECT
> TS_START,
> LEAD(TS_START) OVER (ORDER BY TS_START) NEXT_TS_START
> FROM
> TAB_TIME
> )
> WHERE
> TS_START <= TO_TIMESTAMP('05.10.2006 14:24:35','DD.MM.YYYY HH24:MI:SS') AND
> NEXT_TS_START >= TO_TIMESTAMP('05.10.2006 14:24:35','DD.MM.YYYY HH24:MI:SS')
>
>
> But this results always in a full table scan. Is there a way I can get
> oracle to use the index in the TIMESTAMP column? I don't know that
> much about indexes but I think this should be possible.

You don't post Oracle version and DDL so it's difficult to help you here.

It seems though that you just need to compare once unless you do not want to fetch the last (highest timestamp) row if it matches. My naive approach would be this:

select ....
from tab_time t1,
(
select max(ts_start) maxts
from tab_time
where ts_start <=

   TO_TIMESTAMP('05.10.2006 14:24:35','DD.MM.YYYY HH24:MI:SS') ) t2
where t1.ts_start = t2.maxts

Note, this can return multiple rows if timestamps are not unique.

Kind regards

        robert Received on Tue Mar 13 2007 - 03:22:19 CDT