RE: More efficient ways to do a select count(*)
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "fmhabash@xxxxxxxxx" <fmhabash@xxxxxxxxx>, Oracle-L Group <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 20 Mar 2009 07:27:57 -0400
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@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On Behalf
Of FmHabash [fmhabash@xxxxxxxxx]
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
