-
%ROWCOUNT vs. COUNT(*)
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
-
SQL%ROWCOUNT returns only the number of rows affected.
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
but
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 ??
-
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
It is better to ask and appear ignorant, than to remain silent and remain ignorant.
Oracle OCP DBA 9i,
C++, Java developer
-
Difference
Its more efficient to use ROWCOUNT thats the difference.
add me to your messenger
perrohijueputa@hotmail.com
-
Re: Difference
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.
-
... 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.
-
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...
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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.
Last edited by slimdave; 08-22-2003 at 10:34 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|