Re: count(*) v. count(pk)

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: Mon, 27 Apr 1998 21:28:22 GMT
Message-ID: <3544f1ea.4829153@www.sigov.si>

On Mon, 27 Apr 1998 11:45:19 -0600, brendan_o'brien_at_wrightexpress.com wrote:

>Hi folks.
>
>Saw a post recently where someone recommended doing a count(pk) instead of a
>count(*) as count(*) always does a full table scan.
>
>Questions:
>
>1) Is this in fact true (that a count(*) always does a full table scan)?

No, it is not true. If there is no WHERE clause in the query then:

1. if rule based optimizer is used, in both cases it will perform FULL TABLE SCAN.
2. if cost based optimizer is used and you have statistics gathered for this table ind its indexes, in both cases it will (most probably) perform FULL INDEX SCAN.

>2) Isn't counting on the primary key an impossibility if the key is comprised
>of multiple columns?

There is no reason that composite indexes could not be scaned.

>3) Can anyone suggest any other method for quickly retieving total rows from
>large, multi-column-PK tables?

Without a limiting condition in the WHERE clause there are only two ways of counting all the rows in a table - a full table scan or a full index scan (if index is PK or if at least one of the indexed columns has NOT NULL constraint). In most (but not all) cases, index scan will be faster.

>I tested this theory on a table with 250,000 rows and a one-column PK using
>both queries, and got no difference in response time. I can think of no way
>to test this on a table with a 2+ column PK as count() accepts only one
>argument.

You should examine execution plans of those two queries (I bet they were the same for both queries). Then you should experiment with your optimizer modes (cost versus rule, collect statistics etc). When different execution paths are used to get the same result the response times might differ enormously. The EXPLAIN PLAN command is the basic tool to determine what will Oracle do to get the result.

>
>This isn't of prime importance, more to settle an internal debate here.
>
>Thanks,
>-Brendan

Regards

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Mon Apr 27 1998 - 16:28:22 CDT