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 //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------