RE: Count(*) Vs Count(1)

From: Richard Ji <richard_at_letsplay.com>
Date: Fri, 15 Dec 2000 16:49:23 -0500
Message-Id: <10711.124757@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_0020_01C066B8.D40DBE60 Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Oracle has optimized the count(*) for performance.

Richard Ji

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Sanjay Kumar Sent: Friday, December 15, 2000 2:51 PM
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_000_0020_01C066B8.D40DBE60 Content-Type: text/html;

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

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =

charset=3Diso-8859-1">

<META content=3D"MSHTML 5.00.2314.1000" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =

class=3D984000222-15122000>Oracle=20
has optimized the count(*) for performance.</SPAN></FONT></DIV> <DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20 class=3D984000222-15122000></SPAN></FONT> </DIV> <DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20 class=3D984000222-15122000>Richard Ji</SPAN></FONT></DIV> <BLOCKQUOTE style=3D"MARGIN-RIGHT: 0px">
<DIV align=3Dleft class=3DOutlookMessageHeader dir=3Dltr><FONT =
face=3DTahoma=20
  size=3D2>-----Original Message-----<BR><B>From:</B> root_at_fatcity.com=20   [mailto:root_at_fatcity.com]<B>On Behalf Of </B>Sanjay = Kumar<BR><B>Sent:</B>=20
  Friday, December 15, 2000 2:51 PM<BR><B>To:</B> Multiple recipients of = list=20
  ORACLE-L<BR><B>Subject:</B> Count(*) Vs Count(1)<BR><BR></DIV></FONT>
<DIV><FONT face=3DArial size=3D2>Hi,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I issed the following two statements =
and=20
  generated a plan using Explain Plan. The table on which I tried this = query was=20
  v$cache.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Select Count(*) from =

v$cache</FONT></DIV>


  <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>

  <DIV><FONT face=3DArial size=3D2>Select Count(1) from =

v$cache</FONT></DIV>

<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I found that the explain plan =
generated the same=20
  plan for both the statements. Does that mean that both the statements = are=20
  equally efficient.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>In my opinion, I feel that Count(1) =
is better=20
  than Count(*).</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Kindly explain.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Thanks</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Rgds</FONT></DIV>
<DIV><FONT face=3DArial =

size=3D2>Sanjay</FONT></DIV></BLOCKQUOTE></BODY></HTML> Received on Fri Dec 15 2000 - 15:49:23 CST