Re: Dead slow count(*)

From: Jan Pruner <JPruner_at_email.cz>
Date: Fri, 30 Jan 2004 11:43:16 +0100
Message-ID: <401A3544.3060509@email.cz>

Yes, that's right.
Thank You.

Jared.Still_at_radisys.com wrote:
> 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.
>
> JP
>
> 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
-----------------------------------------------------------------

Received on Fri Jan 30 2004 - 04:43:16 CST