%ROWCOUNT vs. COUNT(*)

[janjacina]

Hi,

What is the diference between
1) select count(*) into cnt from bigtable;

2) select col1,col2,col3... from bigtable;
cnt := sql%rowcount;

Are these variants equals or not ?

is it true, that sql%rowcount returns only amount of fetched dataset and not of whole result dataset ?


Thanks a lot

[jovery]

SQL%ROWCOUNT returns only the number of rows affected.

[janjacina]

Yes its true... by insert/update/delete but


when I have table with 100 rows of data
fetch size is set - 10 rows
select returns - 50 rows
and the question is what returns sql%rowcount ?
it is 50 or 10 ??

[rotem_fo]

ROWCOUNT returns the number of affected rows not only for DML, but also for SELECT statements.

--- code starts here ---
set serverotuput on;

declare
i number;
j number;
cursor c1 is select col1 from tab1;
begin
-- count the rows
select count(col1) into i from tab1;

-- loop over table with cursor
open c1;
loop
fetch c1 into j;
exit when c1%notfound;
end loop;
j := c1%ROWCOUNT;
close c1;

dbms_output.put_line('COUNT(*): ' || i);
dbms_output.put_line('ROWCOUNT: ' || j);
end;
/
--- code ends here ---
output:
COUNT(*): 4
ROWCOUNT: 4

[djhex]

Its more efficient to use ROWCOUNT thats the difference.

[DaPi]

Originally posted by djhex
Its more efficient to use ROWCOUNT thats the difference.

Really ? ? ?

Select count(*) . . . in the original posting can use an Index scan. I would have guessed that would normally be more efficient.

[slimdave]

... and if the only purpose is to populate the "cnt" variable with the number of records in "bigtable", then the Count(*) methodology is more logical, and easy to follow -- even if the ROWCOUNT method were a little faster (which i really really doubt) I still wouldn't use it.

[abhaysk]

Originally posted by slimdave
(which i really really doubt)

Dave :

You dont have to doubt(really really) it, it will not be faster than count method for sure...

[slimdave]

Originally posted by abhaysk
Dave :

You dont have to doubt(really really) it, it will not be faster than count method for sure...

I was being polite, but also trying to make the general point that there is a "logically correct" methodology to doing most things, especially simple things like this.

Even if there is a tricky method to get a slightly faster result, if the methodology is non-intuitive then I would probably not adopt it, just for the sake of future maintainers of the system.