Re: Can you get COUNT and RESULTS at same time?

> tkyte_at_us.oracle.com (Thomas J. Kyte) writes:
> Bear in mind, to find the total number of rows in a result set you have to......
> get the entire result set!
>
> For example:
>
> select * from a_really_big_table;
>
> To find out how many rows would be returned, you would have to actually go out
> and count them. Oracle does not do this. Instead when you issue the above
> query, it will go out and get the first couple and return them right away, when
> you fetch again, it will get some more. The size of the result set is not known
> until the last row is actually fetched. It will really slow down your query if
> you have to know how many rows will be returned, especially if an end user is
> waiting for the answer. Instead of getting the first rows fast.... they will
> have to wait for the last row to be found and then get the first row.

Tom is right. However, you could maintain the total in a separate real table that gets updated by threads that update the actual data by means of a trigger. Then the updaters would take the performance hit instead of the retrievers. Who >>should<< get the performance hit is open to negotiation.

• Paul de Anguera, City of Seattle / HRIS