RE: More efficient ways to do a select count(*)
Date: Fri, 20 Mar 2009 07:27:57 -0400
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,
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).
http://www.freelists.org/webpage/oracle-l Received on Fri Mar 20 2009 - 06:27:57 CDT