Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, fdgf.

Asked: February 01, 2013 - 10:46 pm UTC

Last updated: February 06, 2013 - 2:58 pm UTC

Version: 11.2.0

Viewed 1000+ times

You Asked

How can i get the row count of a cursor

and Tom said...

The only way to get a count of how many rows a cursor will return is to actually fetch them.

Most of the time, a result set is not generated until you actually fetch from it - and even then, it isn't copied somewhere to be fetched from

for example:

select * from one_billion_row_table;

will prepare immediately - no work is done. the first fetch will return instantly - we just get enough data to give back to you - we don't read all one billion rows. We don't know the row count until we get there ourselves!


you could add

count(*) over () cnt

to your select list, then you would have a count to fetch and would know on the first fetch - but i STRONGLY RECOMMEND NOT DOING THAT - it will make every query much slower - much much slower - as we will have to build the entire result set before the first row can be processed by you.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1137577300346084930

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Apex Interactive Reports

Tony, February 06, 2013 - 3:22 pm UTC

Interestingly enough the count(*) over () technique is added to queries in Application Express in the background. This is done so that the apex engine can return the message "this query returns more than X rows" where X is your defined max reporting rows limit (i.e. 10,000 rows).

It may not be an ideal situation, but it does provide some useful functionality.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.