How to Avoid count(*) on web base applictions

From: Elir <elire_at_amdocs.com>
Date: 25 Jun 2001 09:54:37 -0700
Message-ID: <e7cbfa45.0106250854.2778435f@posting.google.com>

Hi
As in most web applications I am presenting data like first 10 rows from total of 200 rows.
In order to get this total, I must run each query TWICE. First for count ,and then for actual data. I believe that solution below can solve the problem
( not to run the "count" query ), but only for queries with order by.
( with no index on "order by" columns )

I want you opinion about my solution:
The below are the steps i issue:
0) decler the currsor for the main query (the query will include order by)
1) Before opening the currsor i am Runing the below select VALUE INTO SORT_B from V$MYSTAT

        where STATISTIC#=( 
                          select STATISTIC# 
                              from sys.V_$SYSSTAT 
                           where NAME='sorts (rows)'
                          ) ;

2)OPEN the currsor .
3) After the currsor was open run the below select VALUE INTO SORT_A from V$MYSTAT

        where STATISTIC#=( 
                          select STATISTIC# 
                              from sys.V_$SYSSTAT 
                           where NAME='sorts (rows)'
                          ) ;

4)MY_COUNT=SORT_A - SORT_B.
5)feach records .

MY_COUNT will include the some of the order record which are the count of my population

Tnx Received on Mon Jun 25 2001 - 11:54:37 CDT