Re: Difference between count(1) and count(*)

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 9 Jul 2004 22:14:36 +0100
Message-ID: <006501c465f9$bd2f1ce0$7102a8c0@Primary>

And that is, of course, a rather important point; because Oracle almost invariably recognises a call to 'count the rows in the table' however you disguise it, and does whatever the CBO thinks is the fastest row count.

Even to the extent where you can do
something like the following:

create table t1(col1 number primary key, col2 number not null, col3 number);

insert lots of data.
create bitmap index t1_b3 on t1(col3);

select count(col1) from t1;

and get an execution plan which is:

index fast full scan on t1_b3.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st

Original Message ----- From: "Powell, Mark D" <mark.powell_at_eds.com> To: <oracle-l_at_freelists.org> Sent: Friday, July 09, 2004 3:48 PM Subject: RE: Difference between count(1) and count(*)

At last someone who bothered to post explain plans to show that the comparisons were valid tests!

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 Jul 09 2004 - 16:11:14 CDT