RE: Count(*) Vs Count(1)

From: Kimberly Smith <kimberly.smith_at_gmd.fujitsu.com>
Date: Mon, 18 Dec 2000 09:24:52 -0800
Message-Id: <10714.124851@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C06917.6E668270
Content-Type: text/plain;

charset="iso-8859-1"

I went though a whole discussion on this a while back and between myself and the list it came down to it depends on which version of Oracle you are using but they have indeed optimized for count(*). This was determined by many of us actually running tests with timing. -----Original Message-----
From: Abdul Aleem [mailto:abchaudhary-ho_at_beaconhouse.edu.pk] Sent: Sunday, December 17, 2000 11:46 PM To: Multiple recipients of list ORACLE-L Subject: RE: Count(*) Vs Count(1)

Some where in Oracle Documentation I remember reading that Oracle encouraged using Count(1) over Count(*).
HTH!
Aleem

-----Original Message-----
From: Sanjay Kumar [mailto:ora_user_at_hotmail.com] Sent: Saturday, December 16, 2000 12:51 AM To: Multiple recipients of list ORACLE-L Subject: Count(*) Vs Count(1)

Hi,

I issed the following two statements and generated a plan using Explain Plan. The table on which I tried this query was v$cache.

Select Count(*) from v$cache

Select Count(1) from v$cache

I found that the explain plan generated the same plan for both the statements. Does that mean that both the statements are equally efficient.

In my opinion, I feel that Count(1) is better than Count(*).

Kindly explain.

Thanks
Rgds
Sanjay

------_=_NextPart_001_01C06917.6E668270
Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML xmlns=3D"http://www.w3.org/TR/REC-html40" xmlns:v =3D=20

"urn:schemas-microsoft-com:vml" xmlns:o =3D=20
"urn:schemas-microsoft-com:office:office" xmlns:w =3D=20
"urn:schemas-microsoft-com:office:word"><HEAD>

<META content=3DWord.Document name=3DProgId>
<META content=3D"MSHTML 5.00.2919.6307" name=3DGENERATOR>
<META content=3D"Microsoft Word 9" name=3DOriginator><LINK=20

href=3D"cid:filelist.xml_at_01C068C9.C60B04E0" rel=3DFile-List>
<STYLE>@font-face {

font-family: Tahoma;
}
P.MsoNormal {

FONT-FAMILY: "Times New Roman"; FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; = mso-style-parent: ""; mso-pagination: widow-orphan; = mso-fareast-font-family: "Times New Roman" }
LI.MsoNormal {

FONT-FAMILY: "Times New Roman"; FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; = mso-style-parent: ""; mso-pagination: widow-orphan; = mso-fareast-font-family: "Times New Roman" }
DIV.MsoNormal {

FONT-FAMILY: "Times New Roman"; FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; = mso-style-parent: ""; mso-pagination: widow-orphan; = mso-fareast-font-family: "Times New Roman" }
P.MsoAutoSig {

FONT-FAMILY: "Times New Roman"; FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; = mso-pagination: widow-orphan; mso-fareast-font-family: "Times New = Roman"
}
LI.MsoAutoSig {

FONT-FAMILY: "Times New Roman"; FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; = mso-pagination: widow-orphan; mso-fareast-font-family: "Times New = Roman"
}
DIV.MsoAutoSig {

FONT-FAMILY: "Times New Roman"; FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; = mso-pagination: widow-orphan; mso-fareast-font-family: "Times New = Roman"
}
SPAN.EmailStyle15 {

COLOR: navy; mso-style-type: personal-reply; mso-ansi-font-size: = 10.0pt; mso-ascii-font-family: Arial; mso-hansi-font-family: Arial; = mso-bidi-font-family: Arial
}
DIV.Section1 {

page: Section1
}
</STYLE>
</HEAD> <BODY bgColor=3Dwhite lang=3DEN-US style=3D"tab-interval: .5in"> <DIV>I went=20
though a whole discussion on this a while back and between=20 myself</DIV>
<DIV>and=20
the list it came down to it depends on which version of Oracle you=20 </DIV>
<DIV>are=20
using but they have indeed optimized for count(*).  This was=20 determined</DIV>
<DIV>by=20
many of us actually running tests with timing.</DIV> <BLOCKQUOTE style=3D"MARGIN-RIGHT: 0px">
<DIV align=3Dleft class=3DOutlookMessageHeader dir=3Dltr>-----Original Message----- From: Abdul Aleem=20 [mailto:abchaudhary-ho_at_beaconhouse.edu.pk] Sent: Sunday, = December=20
 17, 2000 11:46 PM To: Multiple recipients of list=20 ORACLE-L Subject: RE: Count(*) Vs = Count(1) </DIV>
<DIV class=3DSection1>
<SPAN=20
 style=3D"FONT-FAMILY: Arial; FONT-SIZE: 10pt; mso-bidi-font-size: = 12.0pt">Some=20
 where in Oracle Documentation I remember reading that Oracle = encouraged using=20
 Count(1) over Count(*).<o:p></o:p>
<SPAN=20
 style=3D"FONT-FAMILY: Arial; FONT-SIZE: 10pt; mso-bidi-font-size: = 12.0pt">HTH!<o:p></o:p>
<SPAN=20
 style=3D"FONT-FAMILY: Arial; FONT-SIZE: 10pt; mso-bidi-font-size: = 12.0pt">Aleem<o:p></o:p>
<SPAN=20
 style=3D"FONT-FAMILY: Arial; FONT-SIZE: 10pt; mso-bidi-font-size: = 12.0pt"><![if =
!supportEmptyParas]> <![endif]><o:p></o:p>=

<SPAN=20
 style=3D"COLOR: black; FONT-FAMILY: Tahoma; FONT-SIZE: = 10pt">-----Original=20
 Message----- From: = Sanjay=20
 Kumar [mailto:ora_user_at_hotmail.com] <SPAN=20 style=3D"FONT-WEIGHT: bold">Sent: Saturday, December 16, = 2000 12:51=20
 AM To: Multiple = recipients of=20
 list ORACLE-L Subject:=20
 Count(*) Vs Count(1)
<![if = !supportEmptyParas]> <![endif]><o:p></o:p>
<SPAN=20
 style=3D"COLOR: black; FONT-FAMILY: Arial; FONT-SIZE: = 10pt">Hi,<FONT=20
 color=3Dblack><SPAN=20
 style=3D"COLOR: black; mso-color-alt: = windowtext"><o:p></o:p>
<FONT color=3Dblack =