Re: Rownum in Oracle 8.0.x

From: Gerard H. Pille <ghp_at_skynet.be>
Date: Thu, 23 Aug 2001 22:08:06 +0200
Message-ID: <3B8562A6.A3F13452@skynet.be>

Christian Hartmann wrote:
>
> Hi there,
>
> this works propery in Oracle 8.1.x and higher:
>
> select * from
> (select eur_kurs
> from waehrung
> where c_waehrung='USD'
> and i_firm_refid=:i_firm_refid
> and d_tag<=:d_tag
> order by d_tag desc)
> where rownum=1
>
> Unfortunately it doesn't work within an Oralce 8.0.x :-(
>
> Is there a workaround?
>
> Mfg,
>
> Christian Hartmann
> .tmp Informationssysteme GmbH & Co. KG
> Oracle Certified Solution Partner
> Roonstrasse 1, D-26122 Oldenburg
> Germany
> Tel.: +49-441-983983
> Fax: +49-441-9839878
> http://www.tmp-office.de

yes there is:
create an index on waehrung.d_tag, eg. waehrung_tag

use the index_desc hint (check the syntax!)

select /*+ index_desc waehrung waehrung_tag */  eur_kurs
 from waehrung

 where c_waehrung='USD'
 and   i_firm_refid=:i_firm_refid
 and   d_tag<=:d_tag

 and rownum = 1;

if this is not performant enough, create the index on d_tag, c_waehrung and i_firm_refid.

Let me know if I'm right or wrong.

-- 

Gerard H. Pille

Received on Thu Aug 23 2001 - 15:08:06 CDT