you wont. count(1) and count(*) are exactly the same thing. its a common myth. guy needs to run the explain plan and see what happens. remember to analyze the table with cascade=>true after adding the index. fastest way to do a count is with a bitmap index. It runs in a fraction of the time of a btree index. cant do this if its a transactional table. > > From: "Cary Millsap" <cary.millsap@xxxxxxxxxx> > Date: 2004/02/09 Mon PM 01:52:11 EST > To: <oracle-l@xxxxxxxxxxxxx> > Subject: RE: Dead slow count(*) > > I think Oracle will use an index to perform count(*) as well. ...If > there exists an index with at least one NOT NULL column in its key. > > In other words, I don't think you'll find a difference between the > performance of count(*) and count(1). But try it. :) > > (Thanks, Connor!) > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > * Nullius in verba * > > Upcoming events: > - Performance Diagnosis 101: 2/24 San Diego, 3/23 Park City, 4/6 Seattle > - SQL Optimization 101: 2/16 Dallas > - Hotsos Symposium 2004: March 7-10 Dallas > - Visit www.hotsos.com for schedule details... > > > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jonathan Redshaw > Sent: Monday, February 09, 2004 12:16 PM > To: oracle-l@xxxxxxxxxxxxx > Subject: RE: Dead slow count(*) > > I received this tip in a PL/SQL class. On large table you can use > count(1) > to force use of the index. I have not verified this but it may help. > > > >From: Jordi Adame V <jordi@xxxxxxxxxxxx> > >Reply-To: oracle-l@xxxxxxxxxxxxx > >To: Oracle-L@xxxxxxxxxxxxx > >Subject: Dead slow count(*) > >Date: Thu, 29 Jan 2004 17:54:46 -0600 > > > >Hi! > > > >I have this table with ~4,000,000 entries > > > >When i do a SELECT COUNT(*) FROM TABLE_NAME > > > >it takes 4 hours to give me the result, this is way too much > > > >Im running Oracle 9.2.0.4.0 on RedHat AS 3 > > > >These are my first experiences with Oracle, so I have no clue how to > fix > >this > >query time... Any help would be nice > > > >regards > > > >Jordi > > > >---------------------------------------------------------------- > >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 > >----------------------------------------------------------------- > > _________________________________________________________________ > Check out the great features of the new MSN 9 Dial-up, with the MSN > Dial-up > Accelerator. http://click.atdmt.com/AVE/go/onm00200361ave/direct/01/ > > ---------------------------------------------------------------- > 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 > ----------------------------------------------------------------- > > ---------------------------------------------------------------- > 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 > ----------------------------------------------------------------- > ---------------------------------------------------------------- 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 -----------------------------------------------------------------