Re: How to Avoid count(*) on web base applictions

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 25 Jun 2001 11:49:12 -0700
Message-ID: <9h813802a5p@drn.newsguy.com>

In article <e7cbfa45.0106250854.2778435f_at_posting.google.com>, elire_at_amdocs.com says...
>
>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 .

won't work, we don't do any work until you fetch the data.

On my site, where I have a search like this, I use interMedia text. It'll return an approximate count of hits quickly -- I do this once and then paginate through the results.

If this is pure "relational" data -- I just typically print the first 10, see if there is an 11'th and then put a "more data" button. I don't say "record X or 200" in those cases.

Until you get the last row, we don't know how many rows there are.

ops$tkyte_at_ORA817.US.ORACLE.COM> variable X refcursor;

ops$tkyte_at_ORA817.US.ORACLE.COM> select VALUE from V$MYSTAT

  2          where STATISTIC#=(
  3                    select STATISTIC#
  4                    from sys.V_$SYSSTAT
  5                 where NAME='sorts (rows)'
  6                    ) ;

     VALUE
----------
      1136

ops$tkyte_at_ORA817.US.ORACLE.COM> begin
2          open :x for select object_name from all_objects where rownum < 100

order by object_name;
  3 end;
  4 /

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817.US.ORACLE.COM> select VALUE from V$MYSTAT

  2          where STATISTIC#=(
  3                    select STATISTIC#
  4                    from sys.V_$SYSSTAT
  5                 where NAME='sorts (rows)'
  6                    ) ;

     VALUE
----------
      1136

ops$tkyte_at_ORA817.US.ORACLE.COM> print :x

OBJECT_NAME

---

/1001a851_ConstantDefImpl
...
/1362d1c5_RemoteToCorbaToAttrs

99 rows selected.

ops$tkyte_at_ORA817.US.ORACLE.COM> select VALUE from V$MYSTAT

  2          where STATISTIC#=(
  3                    select STATISTIC#
  4                    from sys.V_$SYSSTAT
  5                 where NAME='sorts (rows)'
  6                    ) ;

     VALUE
----------
      1235

>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

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Mon Jun 25 2001 - 13:49:12 CDT