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

From: <bialik_at_wis.weizmann.ac.il>
Date: Tue, 28 Apr 1998 14:13:42 -0600
Message-ID: <6i59p6$4io$1@nnrp1.dejanews.com>

Hi.

I run a number of test concerning the performance of COUNT(*) and a number of different options.

The bottom line : COUNT(*) is the best way to get the number of rows in the table.
It is possible to force INDEX SCAN by using PK-fld > 0 in WHERE clause, but the performance suffers. I think that the main reason for bad performance is an execution of SORT(AGGREGATE).

Following the results of test ( starting with worst case and going to the best one ).

select count(*) from XXX_TAB where ID_NO > 0 There is a NON-UNIQUE index defined for a table ( no PK ). In my opinion there is no different from performance point of view.

call count cpu elapsed disk query current rows ------ ----- -------- --------- --------- --------- --------- ---------

Parse       1     0.00      0.00         0         0         0          0
Execute     1     0.00      0.00         0         0         0          0
Fetch       1    10.89     20.94     41975     41975         0          1
------ ------ -------- --------- --------- --------- ---------  ---------
total       3    10.89     20.94     41975     41975         0          1

Rows     Execution Plan


-------  ---------------------------------------------------


      0  SELECT STATEMENT   GOAL: RULE
      0   SORT (AGGREGATE)

838962 INDEX (RANGE SCAN) OF 'XXX_TAB$ID_NO' (NON-UNIQUE)

B. select /*+ INDEX_FFS(XXX_TAB XXX_TAB$ID_NO ) */ count(ID_NO)

from XXX_TAB where ID_NO > 0
I tried to force FAST INDEX SCAN ( without success ).

call count cpu elapsed disk query current rows ------ ------ -------- --------- --------- --------- --------- ---------

Parse       1     0.00      0.00         0         0         0          0
Execute     1     0.00      0.00         0         0         0          0
Fetch       1    12.37     12.41     41919     41975         0          1
------- -----  ------- --------- --------- --------- ---------  ---------
total       3    12.37     12.41     41919     41975         0          1

Rows     Execution Plan


-------  ---------------------------------------------------


      0  SELECT STATEMENT   GOAL: RULE

838961 SORT (AGGREGATE)
838962 INDEX (RANGE SCAN) OF 'XXX_TAB$ID_NO' (NON-UNIQUE)

C. select /*+ INDEX_FFS(XXX_TAB XXX_TAB$ID_NO ) */

count(*) from XXX_TAB XXX_TAB where ID_NO > 0

call count cpu elapsed disk query current rows ------ ------ -------- --------- --------- --------- --------- ---------

Parse       1     0.00      0.00         0         0         0          0
Execute     1     0.00      0.00         0         0         0          0
Fetch       1    11.19     12.34     41975     41975         0          1
------- -----  ------- --------- --------- --------- ---------  ---------
total       3    11.19     12.34     41975     41975         0          1

Rows     Execution Plan


-------  ---------------------------------------------------


      0  SELECT STATEMENT   GOAL: RULE
      0   SORT (AGGREGATE)

838962 INDEX (RANGE SCAN) OF 'XXX_TAB$ID_NO' (NON-UNIQUE)

D. select count(ID_NO) from XXX_TAB

call count cpu elapsed disk query current rows ------ ------ -------- --------- --------- --------- --------- ---------

Parse       1     0.00      0.01         0         0         0          0
Execute     1     0.00      0.00         0         0         0          0
Fetch       1     6.45      6.45     39964     39965         2          1
------- ----- -------- --------- --------- --------- ---------  ---------
total       3     6.45      6.46     39964     39965         2          1

Rows     Execution Plan


-------  ---------------------------------------------------


      0  SELECT STATEMENT   GOAL: RULE

838961 SORT (AGGREGATE)
838961 TABLE ACCESS (FULL) OF 'XXX_TAB'

E. select count(rowid) from XXX_TAB

call count cpu elapsed disk query current rows ------ ------ -------- --------- --------- --------- --------- ---------

Parse       1     0.00      0.00         0         0         0          0
Execute     1     0.00      0.00         0         0         0          0
Fetch       1     5.43      5.44     39964     39965         2          1
------- -----  ------- --------- --------- --------- ---------  ---------
total       3     5.43      5.44     39964     39965         2          1

Rows     Execution Plan


-------  ---------------------------------------------------


      0  SELECT STATEMENT   GOAL: RULE

838961 SORT (AGGREGATE)
838961 TABLE ACCESS (FULL) OF 'XXX_TAB'

F. select count(1) from XXX_TAB

call count cpu elapsed disk query current rows ------- ----- -------- --------- --------- --------- --------- ---------

Parse       1     0.00      0.00         0         0         0          0
Execute     1     0.01      0.00         0         0         0          0
Fetch       1     5.41      5.42     39965     39965         2          1
------- ----- -------- --------- --------- --------- ---------  ---------
total       3     5.42      5.42     39965     39965         2          1

Rows     Execution Plan


-------  ---------------------------------------------------


      0  SELECT STATEMENT   GOAL: RULE

838961 SORT (AGGREGATE)
838961 TABLE ACCESS (FULL) OF 'XXX_TAB'

G. select count(*) from XXX_TAB

call count cpu elapsed disk query current rows ------ ----- ------- --------- --------- --------- --------- ----------

Parse       1     0.00      0.00        0         0         0          0
Execute     1     0.00      0.00        0         0         0          0
Fetch       1     4.29      4.29    39964     39965         2          1
------- ------  ------- --------- --------- --------- --------- ----------
total       3     4.29      4.29    39964     39965         2          1

Rows     Execution Plan


-------  ---------------------------------------------------


      0  SELECT STATEMENT   GOAL: RULE
      0   SORT (AGGREGATE)

838961 TABLE ACCESS (FULL) OF 'XXX_TAB'

In article <6i43ol$jec$2_at_news00.btx.dtag.de>, GreMa_at_t-online.de (Matthias Gresz) wrote:

>
> Venkat schrieb:
> >
> > >1) Is this in fact true (that a count(*) always does a full table scan)?
> > **** Yes count(*) always does a FTS
> No, using CBO might lead to an index scan. That's propably why Brendan's
> testing resulted in same performance.
>
> >
> > >2) Isn't counting on the primary key an impossibility if the key is
> > comprised
> > >of multiple columns?
> >
> > **** No.  You can do SELECT COUNT(ename||empno) from emp;
> >
> > >3) Can anyone suggest any other method for quickly retieving total rows
> > from
> > >large, multi-column-PK tables?
> >
> > **** If you keep your statistics upto-date a simple solution would be

query

> > the dba_tables to see the number of rows.  Analyze your tables and then
> > query the num_rows column of dba_tables.
> >
> > HTH
> > Venkat
>
> --
>
> Regards
>
> Matthias Gresz    :-)
>
> GreMa_at_t-online.de
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Tue Apr 28 1998 - 15:13:42 CDT