Re: find nearest, using index in oracle 8.1.5.0.5

Put your definition in a view. This is one example that works in sqlplus but not in PL/SQL. By using a view

create or replace force view v_fast_retr as select i_a, f1 from (
  select f1 from t1 where f1>i_a order by f1)   where rownum=1
/

and

        select fl from v_fast_retr where :a = i_a

you should get the desired behaviour.

Martin

Evgeny Sorokin wrote:
>
> Hello!
> I found, thet queries
> select min(f1) from t1 where f1>:a
> and
> select max(f1) from t1 where f1<:a
> (where t1 indexed on f1) runs SLOWLY!!!
> "explain plan" shows "fast full index scan". If I use /*+ RULE */, then
> "range scan", but also slowly even more.
> Then I found FAST queries:
> select f1 from (
> select f1 from t1 where f1>:a order by f1)
> where rownum=1
> and
> select f1 from (
> select f1 from t1 where f1<:a order by f1 desc)
> where rownum=1
>
> I was so happy, but then I found, that such queries can not be inserted into
> stored procedure
>
> Oracle said, that it can not understand "order by".
> May be I can ommit "order by", but it is NOT GOOD practise, because in other
> versions it can't work with guarantee.
> I don't want to useŘ DBMS_SQL, may be somebody can advise something better?
> Evgeny Sorokin
Received on Mon Feb 21 2000 - 05:08:15 CST