number of rows in cursor?

#1

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.

#2

"eudorica" <snjezana.katusic (AT) htnet (DOT) hr> wrote

Quote:

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.

No, you have to fetch the data.

Jim

#3

Am Fri, 10 Dec 2004 09:45:50 +0100 schrieb eudorica
<snjezana.katusic (AT) htnet (DOT) hr>:

Quote:

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)

#4

"Frank Piron" <empty (AT) zero (DOT) nil> wrote

Quote:

Am Fri, 10 Dec 2004 09:45:50 +0100 schrieb eudorica
snjezana.katusic (AT) htnet (DOT) 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.

Jim

#5

Am Fri, 10 Dec 2004 13:11:32 GMT schrieb Jim Kennedy
<kennedy-downwithspammersfamily (AT) attbi (DOT) net>:

Quote:

"Frank Piron" <empty (AT) zero (DOT) nil> wrote in message
news

psisl5wh3m0et4w (AT) news (DOT) online.de...
Am Fri, 10 Dec 2004 09:45:50 +0100 schrieb eudorica
snjezana.katusic (AT) htnet (DOT) 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.

--
Frank Piron,
defrankatkonaddot
(leftrotate two)

#6

"Frank Piron" <empty (AT) zero (DOT) nil> wrote

Quote:

Am Fri, 10 Dec 2004 13:11:32 GMT schrieb Jim Kennedy
kennedy-downwithspammersfamily (AT) attbi (DOT) net>:

"Frank Piron" <empty (AT) zero (DOT) nil> wrote in message
news

psisl5wh3m0et4w (AT) news (DOT) online.de...
Am Fri, 10 Dec 2004 09:45:50 +0100 schrieb eudorica
snjezana.katusic (AT) htnet (DOT) 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

#7

On Fri, 10 Dec 2004 15:14:28 +0100, Frank Piron <empty (AT) zero (DOT) nil>
wrote:

Quote:

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.

--
Frank Piron,
defrankatkonaddot
(leftrotate two)

As your advice also effectively selects the data twice, and
consequently consumes twice as much resources, I wouldn't recommend
this 'workaround' to anyone.

--
Sybrand Bakker, Senior Oracle DBA

#8

there is no efficient way, you need to run a COUNT(*) query before.
What is your problem? Maybe there is another solution other than SELECT
COUNT(*) ...

#9

ak_tiredofspam (AT) yahoo (DOT) com wrote:

Quote:

there is no efficient way, you need to run a COUNT(*) query before.
What is your problem? Maybe there is another solution other than SELECT
COUNT(*) ...

I need to send number of steps to outside code. Select count(*) is no

good for performance. Thanks anyway!

#10

"Cile" <cile (AT) hi (DOT) htnet.hr> wrote

Quote:

ak_tiredofspam (AT) yahoo (DOT) com wrote:
there is no efficient way, you need to run a COUNT(*) query before.
What is your problem? Maybe there is another solution other than SELECT
COUNT(*) ...

I need to send number of steps to outside code. Select count(*) is no
good for performance. Thanks anyway!

do this:

select just the rowid's in to a pl/sql table variable (bulk collect for
performance)

examine the count to determine how to proceed

fetch the actual data using rowids in the pl/sql table variable (bulk bind
for performance)

depending, however, on what you're fetching (number and size of rows) it
might be cheaper to just bulk collect the rows into a pl/sql table variable,
examine the count, then loop thru the pl/sql table variable

++ mcs