RE: More efficient ways to do a select count(*)

From: Bobak, Mark <Mark.Bobak_at_proquest.com>
Date: Fri, 20 Mar 2009 07:27:57 -0400
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F135C5D48_at_AAPQMAILBX02V.proque.st>

Hi,

Oracle is pretty well optimized in this area. There's no difference between count(*) and count(pk_column), for example.

If you do a count(*), Oracle can do a fast full index scan or an index full scan, if there's a pk index (or a uk index w/ a not null constraint). It will do a fast full index scan or index full scan, if there's a bitmap index on any column in the table. (Bitmap indexes have nulls indexed, so no need for not null constraint here.) Only other option is a full table scan.

Hope that helps,

-Mark

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On Behalf Of FmHabash [fmhabash_at_gmail.com] Sent: Friday, March 20, 2009 6:58 AM
To: Oracle-L Group
Subject: More efficient ways to do a select count(*)

I see often times applications run such query on a high frequency basis and this seemingly harmless query becomes a top cpu consumer. For whatever reason applications need to do this, how else such query can written to avoid the FTS it often does. In a quick test, I saw doing count(primary key) is much faster and xplan shows index vs. FTS access path. For this particular issue, there is no PK on the table and using a UK does guarantee a not-null value. Any other ideas (parallel exec is not an option).

Thanks
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Mar 20 2009 - 06:27:57 CDT