Re: Diff Between Count( * ) and Count( 1 )

Re: Diff Between Count( * ) and Count( 1 )

 

  

Jay knows what's what!

I wasn't expressing an opinion, I was stating the facts.

The best way to demonstrate that all three perform block-wise full table
scans to the high watermark is to:

1) make sure you aren't using a unique index with a FULL hint or a function
in the where clause that would prevent the index being used.
2) insert a million rows into a table, commit, delete (not truncate) all
million rows, commit, and then run all 3 versions of the count: *, 1, and a
nullable column.

All 3 will take the same amount of time (unless the table is small enough to
be cached), and all 3 will return 0. That's because they are all doing the
same thing. If there were rows, and some of the nullable columns were null,
then the result would be different, but it would STILL do a block-wise full
table scan to find all the rows.

It isn't rocket science, and it's been this way a very long time.


<<MrO>>

----- Original Message -----
From: "Chelur Jayadas "
<oracledba-ezmlmshield-x31714411.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Monday, July 02, 2007 1:41 PM
Subject: RE: Diff Between Count( * ) and Count( 1 )


> count(1) **DOES NOT** count the first column. it does not matter whether
> there are NULLS in the first column, count(1) AND count(*) return the
> same result ... definitely in Oracle 9.2.0.7
>
> -----Original Message-----
> From: Joel
> [mailto:oracledba-ezmlmshield-x70271549.[Email address protected]
> Sent: Monday, July 02, 2007 1:35 PM
> To: LazyDBA Discussion
> Subject: RE: Diff Between Count( * ) and Count( 1 )
>
> Count doesn't count nulls. Count(*) counts rows... I believe rowid, so
> you will get a full table scan and a count of the rows. Count(1)
> counts the first column, so if there are nulls they are not counted.
>
> Joel Patterson
> Database Administrator
> joel.[Email address protected]
> x72546
> 904 727-2546
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>


Oracle LazyDBA home page