Re: Way to count cursor rows

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 12 Feb 1999 20:57:16 GMT
Message-ID: <36ca94c9.16445247@192.86.155.100>

A copy of this was sent to tobrien_at_reveregroup.com (if that email address didn't require changing) On Fri, 12 Feb 1999 20:30:05 GMT, you wrote:

>Is there a way to count the number of rows in a cursor result set before
>beginning processing? I know that %rowcount will give the number of rows
>already processed but that won't help me here. The only thing I can think of
>is doing a separate count (maybe in a function) before I even open the
>cursor. Any ideas are appreciated.
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

I would avoid having to have such a constraint (needing to know the number of rows before fetching them).

reasons are:

• many queries don't know how many rows they will return until they return the last one. For example: "select * from emp where empno > 10" will typically use an index to scan and fetch data -- it will not 'preanswer' the query into a temporary result set and then give it back to you. It reads the real data as it needs it and doesn't know the answer until after you are done. No query will tell you how many rows it will return
• if you try to:

select count(*) from emp where empno > 10 select * from emp where empno > 10

you may very well find in many cases that the count(*) doesn't match the select *. reasons will be inserts, updates, deletes that commit after the select count(*) starts but before the select * starts. So the count(*) is an approximation at best (and a very very expensive one at that)  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Feb 12 1999 - 14:57:16 CST