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

Home -> Community -> Usenet -> c.d.o.server -> Re: Trigger error (implicit vs explicit cursors)

Re: Trigger error (implicit vs explicit cursors)

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Tue, 04 Nov 2003 19:18:37 +0800
Message-ID: <3FA78B0D.6163@yahoo.com>


mcstock wrote:
>
> rownum is the pre-sort order of the rows -- rownum = 1 is not always the
> first row returned:
>
> SQL> select dname, deptno
> 2 from dept
> 3 order by dname desc;
> ...
>
> DNAME DEPTNO
> -------------- ----------
> SALES 30
> RESEARCH 20
> OPERATIONS 44
> ACCOUNTING 10
>
> SQL> select dname, deptno
> 2 from dept
> 3 where rownum =1
> 4 order by dname desc;
> ...
>
> DNAME DEPTNO
> -------------- ----------
> ACCOUNTING 10
>
> however, isn't there another technique or function added in 8i for stuff
> like first_rows() for top_rows?
>
> -- mcs
>
> "Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
> news:3FA6342C.7E0D_at_yahoo.com...
> >
> > The too_many_rows is a dead issue.
> >
> > If you have a cursor that may return more than 1 row and you code
> > open/fetch/close, then you are basically seeing that you are interested
> > in just the first. In which case, the implicit cursor can be
> >
> > select .. into ...
> > where rownum = 1
> >
> > and you'll never ever get the too_many_rows.

select .. into ..
from ( select x

       from   y
       order by z )

where rownum = 1

and you'll find that runs a whole lot faster than

cursor C is select x from y order by x;

open C; fetch C; close C;

because the former can use an Oracle optimization that the latter cannot.

(You'll be able to read all about it in my upcoming book - Stored Procedures Applied - late 2003 with Apress)

hth
connor Received on Tue Nov 04 2003 - 05:18:37 CST

Original text of this message

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