Re: Index to speed up "select count(*) from" queries...

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 20 May 1999 08:56:59 +0100
Message-ID: <927187206.3593.2.nnrp-04.9e984b29@news.demon.co.uk>

The following pseudo-error appears in 7.3+

10122, 00000, "CBO disable count(col) => count(*) transformation"

I would be inclined to think that

count(1), and count(rowid)
both fall into the category of the count(*) transformation. so all three options should be equally effective under more recent versions of Oracle.

The most significant impact is the number of physical read requests that have to be satisfied by actual physical reads. Then next is the number of logical I/Os that have to be catered for.

Often the best bet is to have a bitmap index on the table (if this is viable - which is not true for OLTP stuff).

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Doug Cowles wrote in message <37434001.479D20F6_at_bigfoot.com>...
>I second that, why is count(rowid) nonsense? I was going to suggest
>count(1). From my understanding a count(*) reads in the entire row when
>counting
>and a count(1) or count(rowid) only reads the rowid. Should be much
>faster..
>Why not try that?
Received on Thu May 20 1999 - 02:56:59 CDT