Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, fdgf.

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

Answered by: Tom Kyte - Last updated: February 06, 2013 - 2:58 pm UTC

Category: Developer - Version: 11.2.0

Viewed 1000+ times

You Asked

How can i get the row count of a cursor

and we 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

and you rated our response

  (1 rating)

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

Reviews

Apex Interactive Reports

February 06, 2013 - 3:22 pm UTC

Reviewer: Tony from WA

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.