RE: Optimizing a Simple 'select count(*)'

From: Mark W. Farnham <>
Date: Sun, 9 Mar 2014 18:51:54 -0400
Message-ID: <0a3201cf3bea$2bc28c70$8347a550$>

Unless something odd is going on, an index on that column should be smaller than the table. So even if it is for all the index entries, a fast full scan should be cheaper, and the usual trade-off of having to fetch any columns from the table does not apply to a count. (And only the leaf nodes really count for a fast full scan.) If the original was for a range small enough to benefit from an index range scan, that might be sub-optimal for a very wide range, but still should beat any FTS.

If you keep that in mind and re-read JL's most recent on this thread, I think you'll get the picture.


-----Original Message-----

From: [] On Behalf Of McPeak, Matt
Sent: Sunday, March 09, 2014 6:35 PM
To:; Subject: RE: Optimizing a Simple 'select count(*)'

Just throwing this out there...

It wouldn't have anything to do with the fact that the from and to dates are bind variables for which we don't know the values? If the initial values given were broad enough, wouldn't Oracle (rightly) choose a FTS over an index?


From: [] on behalf of Jonathan Lewis [] Sent: Sunday, March 09, 2014 11:25 AM
Subject: RE: Optimizing a Simple 'select count(*)'

Even without seeing the code, but assuming there is an index that includes the required data column referenced in the predicate, it does seem a little odd that Oracle has chosen a full tablescan rather than (worst case) an index fast full scan.

The only obvious reason I can think of for this choice is that either the index has grown to a much larger size than it should be, or the only available index includes so many of the table columns that it actually HAS to be larger than the table.

select blocks from user_tables where table_name = ... select leaf_blocks from user_indexes where index_name = ...

Jonathan Lewis

From: [] on behalf of Jared Still [] Sent: 09 March 2014 13:14
Subject: Re: Optimizing a Simple 'select count(*)'

On Wed, Mar 5, 2014 at 11:56 AM, Fred Habash <<>> wrote: So, why should we live with such costly resource consumptions for such a simple query?

We don't know enough about the tables and/or indexes.

If you include complete code for a reproducible test case, you will likely get more response.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Sr Oracle DBA at Pythian Pythian Blog Oracle Blog: Home Page:


-- Received on Sun Mar 09 2014 - 23:51:54 CET

Original text of this message