Re: Dead slow count(*)

Yes, that's right.
Thank You.

JP

Jared.Still@xxxxxxxxxxx wrote:
> Jan, try the following test:
> 
> 17:19:19 rsysdevdb.radisys.com - jkstill@dv01 SQL>
> 17:19:19 rsysdevdb.radisys.com - jkstill@dv01 SQL> drop table count_test;
> 
> Table dropped.
> 
> 17:19:19 rsysdevdb.radisys.com - jkstill@dv01 SQL>
> 17:19:19 rsysdevdb.radisys.com - jkstill@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@dv01 SQL>
> 17:19:19 rsysdevdb.radisys.com - jkstill@dv01 SQL> alter table count_test 
> modify( row_number not null)
> 17:19:19   2
> 17:19:19 rsysdevdb.radisys.com - jkstill@dv01 SQL> analyze table 
> count_test compute statistics;
> 
> Table analyzed.
> 
> 17:19:19 rsysdevdb.radisys.com - jkstill@dv01 SQL>
> 17:19:19 rsysdevdb.radisys.com - jkstill@dv01 SQL> set autotrace on
> 17:19:19 rsysdevdb.radisys.com - jkstill@dv01 SQL>
> 17:19:19 rsysdevdb.radisys.com - jkstill@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@dv01 SQL>
> 17:19:19 rsysdevdb.radisys.com - jkstill@dv01 SQL> set autotrace off
> 17:19:19 rsysdevdb.radisys.com - jkstill@dv01 SQL>
> 17:19:19 rsysdevdb.radisys.com - jkstill@dv01 SQL> create index ct_idx_1 
> on count_test(row_number);
> 
> Index created.
> 
> 17:19:19 rsysdevdb.radisys.com - jkstill@dv01 SQL>
> 17:19:19 rsysdevdb.radisys.com - jkstill@dv01 SQL> analyze index ct_idx_1 
> compute statistics;
> 
> Index analyzed.
> 
> 17:19:19 rsysdevdb.radisys.com - jkstill@dv01 SQL>
> 17:19:19 rsysdevdb.radisys.com - jkstill@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@dv01 SQL>
> 17:19:19 rsysdevdb.radisys.com - jkstill@dv01 SQL> set autotrace on
> 17:19:19 rsysdevdb.radisys.com - jkstill@dv01 SQL>
> 17:19:19 rsysdevdb.radisys.com - jkstill@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@dv01 SQL>
> 
> 
> 
> 
> 
> 
> Jan Pruner <JPruner@xxxxxxxx>
> Sent by: oracle-l-bounce@xxxxxxxxxxxxx
>  01/29/2004 05:07 PM
>  Please respond to oracle-l
> 
>  
>         To:     oracle-l@xxxxxxxxxxxxx
>         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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: