Re: how to speed up a "select count(*) from ..."

> I have a table with 40.000 records. There was no primary key on the
 table
> (!) and a "select count(*) from mytable" took about 15 sec.
> So, now, I've created a primary index on mytable.id field...

It looks like you have a high watermark on this table (that, of course, if you it should be less than 15 seconds).

Try this:
analyze table mytable compute statistics; select avg_row_len, num_rows, blocks, empty_blocks from user_tables where table_name = 'MYTABLE';
select value from v$parameter where name = 'db_block_size';

total space scanned by FULL TABLE SCAN (space below HWM) is (blocks - empty_blocks) * db_block_size

total space that is really used by your 40.000 rows is avg_row_len * num_rows

Now, if your scanned space is way higher than your really used space, you have the watermark that is too high. (You probably deleted many rows from this table ?)

To reset watermark you have to truncate the table. For example:

create table temp as select * from mytable; truncate table mytable; (you may have to disable foreign keys if any) insert into mytable select * from temp;

now the count as fast as it gets (as the previous posts suggest, use count (1) instead of count (*) -- Oracle "recommends" (that is, they don't bother to explain why it is better than count (*))

Kirill Richine

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri May 05 2000 - 00:00:00 CDT