Re: select count(*)

If you need this count often you could create a table to hold the value of the count. This table would be updated through an insert or delete trigger on table1 to increment or decrement the count.

As an example, a derived count table might look like

SQL> desc c_count

 Name                     Null?    Type
 ----------------------- -------- ---------------
 WHAT                             VARCHAR2(2)
 NUM                                 NUMBER

with data

WHAT        NUM
--------          ----------
c                   0

and the trigger on table1 might look like

create or replace trigger c_count_tg
before insert or delete on table1
for each row
begin

   if dbms_standard.inserting then

      update c_count set num = num + 1 where what = 'c';    else

      update c_count set num = num - 1 where what = 'c';    end if;
end;

Since there are 10 million rows in the table, and your memory is limited to 524 megabytes, even the index used by the count could be soon aged out of memory and have to be reloaded. See Dave Wotton's comments on trying to force the index to be used.

Frank Hubeny

dhruba wrote:

> Hi,
>
> I have a table of 10 million rows with 20 columns
>
> analyze table1 shows this:-----
>
> NUM_ROWS AVG_SPACE AVG_ROW_LEN SAMPLE_SIZE BUFFER_
> --------- --------- ----------- ----------- -------
> 9342920 227 61 1064 DEFAULT
>
> on my machine NT4 with SP6 524MB RAM ,
> select count(*) from table1 takes around about 1 mt 40 secs;
> select count(1) from table1 also takes 1mt 20 secs.
>
> I have a primary key on this table .
> and some column indexes.
>
> .. The database size is approx 4GB
> .. Index tablespace are different from data tablespace.
>
> table & index storage parameter not taken care.
>
> In this is this performence OK?
> or it can be improved & how ?
>
> any advice will be appreciated
> Thanks in advance
>
> Tcy
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Jul 28 2000 - 00:00:00 CDT