Re: Can you get COUNT and RESULTS at same time?
Date: 1996/08/03
Message-ID: <32038d81.1075176_at_dcsun4>#1/1
select * from a_really_big_table;
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 ....... )
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