Re: Slow select count(*)

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Tue, 02 Nov 1999 20:22:18 +0800
Message-ID: <381ED77A.BF8@yahoo.com>

kasthuri_at_viisage.com wrote:
>
> Hi,
>
> I am running Oracle 7.2.3 on Dec OSF 4.0d. There is a table t1 (pan
> varchar(18), image long raw, len int)
> A select count(*) on this tables takes a long time to return. This table
> is being updated constantly and is a production database.
>
> there is a primary key constraint on the PAN on a index_tablespace.
>
> Any ways to improve, Please suggest
>
> Thanks & Regards,
> --
> Kasthuri Jegatheesan
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

Probably because of the size of each row (ie that long raw is large)...

You could partition the table into two bits - one with the varchar2 and the len, the other with the long raw joined with some surrograte key and then count on the one without the long raw.

In later releases of Oracle, you can also perform a index scan when performing a count (as opposed to table scan) which means only the index will be read not the table which will have the same effect...

HTH
--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Tue Nov 02 1999 - 06:22:18 CST