Re: Dead slow count(*)

From: <Jared.Still_at_radisys.com>
Date: Thu, 29 Jan 2004 17:20:52 -0800
Message-ID: <OF9E0679FF.257D71AD-ON88256E2B.0006B289-88256E2B.00075587@radisys.com>

Jan, try the following test:

17:19:19 rsysdevdb.radisys.com - jkstill_at_dv01 SQL> 17:19:19 rsysdevdb.radisys.com - jkstill_at_dv01 SQL> drop table count_test;

Table dropped.

17:19:19 rsysdevdb.radisys.com - jkstill_at_dv01 SQL>
17:19:19 rsysdevdb.radisys.com - jkstill_at_dv01 SQL> create table count_test
17:19:19   2  as
17:19:19   3  select rownum as row_number, table_name, owner
17:19:19   4  from dba_tables
17:19:19   5  /

Table created.

17:19:19 rsysdevdb.radisys.com - jkstill_at_dv01 SQL> 17:19:19 rsysdevdb.radisys.com - jkstill_at_dv01 SQL> alter table count_test modify( row_number not null)
17:19:19 2
17:19:19 rsysdevdb.radisys.com - jkstill_at_dv01 SQL> analyze table count_test compute statistics;

Table analyzed.

17:19:19 rsysdevdb.radisys.com - jkstill_at_dv01 SQL>
17:19:19 rsysdevdb.radisys.com - jkstill_at_dv01 SQL> set autotrace on
17:19:19 rsysdevdb.radisys.com - jkstill_at_dv01 SQL>
17:19:19 rsysdevdb.radisys.com - jkstill_at_dv01 SQL> select count(*) from

count_test;

COUNT(*)

596

1 row selected.

Execution Plan

0
SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)

          1                  0
  SORT (AGGREGATE)
          2                  1

TABLE ACCESS (FULL) OF 'COUNT_TEST' (Cost=1 Card=596)

Statistics

          5  recursive calls
          1  rows processed

17:19:19 rsysdevdb.radisys.com - jkstill_at_dv01 SQL>
17:19:19 rsysdevdb.radisys.com - jkstill_at_dv01 SQL> set autotrace off

17:19:19 rsysdevdb.radisys.com - jkstill_at_dv01 SQL> 17:19:19 rsysdevdb.radisys.com - jkstill_at_dv01 SQL> create index ct_idx_1 on count_test(row_number);

Index created.

17:19:19 rsysdevdb.radisys.com - jkstill_at_dv01 SQL> 17:19:19 rsysdevdb.radisys.com - jkstill_at_dv01 SQL> analyze index ct_idx_1 compute statistics;

Index analyzed.

17:19:19 rsysdevdb.radisys.com - jkstill_at_dv01 SQL> 17:19:19 rsysdevdb.radisys.com - jkstill_at_dv01 SQL> alter table count_test add constraint cc_pk
17:19:19 2 primary key(row_number)
17:19:19 3 /

Table altered.

17:19:19 rsysdevdb.radisys.com - jkstill_at_dv01 SQL>
17:19:19 rsysdevdb.radisys.com - jkstill_at_dv01 SQL> set autotrace on
17:19:19 rsysdevdb.radisys.com - jkstill_at_dv01 SQL>
17:19:19 rsysdevdb.radisys.com - jkstill_at_dv01 SQL> select count(*) from

count_test;

COUNT(*)

596

1 row selected.

Execution Plan

0
SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)

          1                  0
  SORT (AGGREGATE)
          2                  1

INDEX (FAST FULL SCAN) OF 'CT_IDX_1' (NON-UNIQUE) (Cost=1 Card=596)

Statistics

        174  recursive calls
          1  rows processed

17:19:19 rsysdevdb.radisys.com - jkstill_at_dv01 SQL>

Jan Pruner <JPruner_at_email.cz>
Sent by: oracle-l-bounce_at_freelists.org
01/29/2004 05:07 PM
Please respond to oracle-l

        To:     oracle-l_at_freelists.org
        cc: 
        Subject:        Re: Dead slow count(*)

I think it will always use fullscan if you use COUNT. But I'm using Oracle 8i or maybe I'm wrong.

Wolfgang Breitling wrote:
> Not always. If Oracle CAN use an index to return the correct value it
will.
>
> At 05:09 PM 1/29/2004, you wrote:
>

>>If You use function COUNT, Oracle will always do fullscan!!!
>>So, first question should be - Do I really need to use COUNT?
>>++++++++++++++++++++++++

>
>
> Wolfgang Breitling
> Oracle7, 8, 8i, 9i OCP DBA
> Centrex Consulting Corporation
> http://www.centrexcc.com
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------




----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Jan 29 2004 - 19:20:52 CST