Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Trigger error (implicit vs explicit cursors)
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 )
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