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

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.

One way to do this in general is:

select c1, c2, c3, ....., TOTAL_CNT
from T1, T2, T3, ( select count(*) TOTAL_CNT

                     from T1, T2, T3
                    where c1 = c2
                      and c2 = c3
                      and c3 = ...
	              and ....... )

Basically, you have to run the count(*) at the SAME EXACT TIME under normal circumstances as the regular query. This is to prevent rows being added/deleted to T1, T2, T3 between the time of the count(*) and the actual query to change the count(*) result.

I do not recommend this approach, it will really slow things down.

On Thu, 01 Aug 1996 21:16:06 -0400, David Diano <dave_at_diano.com> wrote:

>To all gurus-
> I am writing a database application for the web using PLSQL and
>Oracle. When I do a search, I would like the count of total records
>found as well as the search results. The ROWCOUNT tells you only the
>count of rows read so far. The only two approaches I have:
> 1) Do two queries, one with count, the second with results.
> 2) After I display me first 20 records, continue looping through
>the results cursor until the end and getting the count.
>
> Clearly, both these approaches are not optimal. There SHOULD be a
>way to do this: some function or hidden variable, but I can't find it.
>
> Please help.
>
> Thanks in advance,
> David Diano
>--
>************************************************
>* Diano Consulting *
>* http://www.diano.com ** dave_at_diano.com *
>* "When it has to be right the first time." *
>************************************************

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Sat Aug 03 1996 - 00:00:00 CEST