Re: Using Index to find row near value

From: <jan.stulrajter_at_zoznam.sk>
Date: 14 Mar 2007 01:58:57 -0700
Message-ID: <1173862737.279482.135630@l77g2000hsb.googlegroups.com>

Hi,

I had a similar issue a short time ago, once I wanted to use Analytic Functions, it changed the execution plan from Index Range Scan to Full table Scan with a much worse response time.

Fortunately I found a workaround, but had no time to find out why it works in this way. The solution is - hide the Analytics into SELECT FROM DUAL as follows:

Jan

---

SELECT
 *
 FROM
 (
 SELECT
 TS_START,
 (SELECT LEAD(TS_START) OVER (ORDER BY TS_START) FROM dual) 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')

---

Björn Wächter napísal(a):
> 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.
>
> Thanks Björn
Received on Wed Mar 14 2007 - 03:58:57 CDT