Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: select count(*)

Re: select count(*)

From: <oratune_at_aol.com>
Date: 2000/07/27
Message-ID: <8lqdvo$abi$1@nnrp1.deja.com>#1/1

In article <8lqa61$7l9$1_at_nnrp1.deja.com>,   dhruba <tapashc_at_my-deja.com> 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.
>

This is one of those interesting aspects of Oracle that many users are not aware of -- count(), max(), min(), avg(), sum() all circumvent the indexes and perform full table scans. Let me repeat that:

The functions count(), max(), min(), avg(), sum() ALL circumvent the indexes and perform FULL TABLE SCANS.

For 10 million rows a count(*) that takes 1 minute 30 seconds (on average) is not bad performance. Scanning 10 million rows takes time and you shouldn't expect blazing speed when no index is involved.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Jul 27 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US