More efficient ways to do a select count(*)
From: FmHabash <fmhabash_at_gmail.com>
Date: Fri, 20 Mar 2009 06:58:57 -0400
Message-ID: <49c376f0.0807c00a.6687.ffffe651_at_mx.google.com>
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).
Date: Fri, 20 Mar 2009 06:58:57 -0400
Message-ID: <49c376f0.0807c00a.6687.ffffe651_at_mx.google.com>
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-lReceived on Fri Mar 20 2009 - 05:58:57 CDT