DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: %ROWCOUNT vs. COUNT(*)

  1. #1
    Join Date
    Feb 2003
    Location
    Slovakia, Europe
    Posts
    72

    %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

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    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!

  3. #3
    Join Date
    Feb 2003
    Location
    Slovakia, Europe
    Posts
    72

    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 ??

  4. #4
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    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

  5. #5
    Join Date
    Aug 2003
    Posts
    9

    Difference

    Its more efficient to use ROWCOUNT thats the difference.
    add me to your messenger

    perrohijueputa@hotmail.com

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    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.

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    ... 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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width