Re: number of rows in cursor?

"Frank Piron" <empty_at_zero.nil> wrote in message news:opsispueqrm0et4w_at_news.online.de...
> Am Fri, 10 Dec 2004 13:11:32 GMT schrieb Jim Kennedy
> <kennedy-downwithspammersfamily_at_attbi.net>:
>
> >
> > "Frank Piron" <empty_at_zero.nil> wrote in message
> > news:opsisl5wh3m0et4w_at_news.online.de...
> >> Am Fri, 10 Dec 2004 09:45:50 +0100 schrieb eudorica
> >> <snjezana.katusic_at_htnet.hr>:
> >>
> >> > Is there way to count a number of rows in opened cursor before
> >> fetching
> >> > it
> >> > (%rowcount works only after fetching)? I need to know the number of
> >> rows
> >> > before enter loop. Thanks.
> >> >
> >> >
> >>
> >> You may count the rows explicitly before entering the loop.
> >>
> >> if the cursor statement is: select <projection-clause>
> >> from ....
> >>
> >> you may count with: select count(rowid) into <counter_var>
> >> from ....
> >>
> >> where <counter_var> is some local variable into which you may
> >> store the number of rows the cursor will retrieve.
> >>
> >> --
> >> Frank Piron,
> >> defrankatkonaddot
> >> (leftrotate two)
> > Except the number could change from count to the fetch of the data.
>
> Sure. But this is inevitable.
>

An approximate count of the number of rows that will be returned can also found by using CBO, analyzing and doing an explain plan. Not a very precise method .. but definitely worth looking into if performance is critical.

Tom Kyte mentions this in his site: search for "estimated cardinality" or "pagination" on http://asktom.oracle.com

Anurag Received on Fri Dec 10 2004 - 17:30:36 CST