count (1) and count (*)

[Geetha]

I searched in the Oracle documents what count (1) meant and I could
not find an answer. Can some one explain what Oracle does internally
when use count (1) VS count (*). Thank you very much in advance! We
use Oracle 9i.

[Jim Kennedy]

"Geetha" <ge******@hotmail.com> wrote in message
news:4b**************************@posting.google.c om...

I searched in the Oracle documents what count (1) meant and I could
not find an answer. Can some one explain what Oracle does internally
when use count (1) VS count (*). Thank you very much in advance! We
use Oracle 9i.

There is a myth out there that count(1) is faster than count(*). It isn't
true. Use count(*).(You can look it up on asktom.oracle.com)
Jim

[mcstock]

it used to be true prior to somewhere in v7 -- should not be true anymore,
but it's always good to do an EXPLAIN PLAN or SET AUTOTRACE ON to make sure

-- mcs

"Jim Kennedy" <ke****************************@attbi.net> wrote in message
news:H1Rnb.40108$mZ5.224243@attbi_s54...

"Geetha" <ge******@hotmail.com> wrote in message
news:4b**************************@posting.google.c om...

I searched in the Oracle documents what count (1) meant and I could
not find an answer. Can some one explain what Oracle does internally
when use count (1) VS count (*). Thank you very much in advance! We
use Oracle 9i.

There is a myth out there that count(1) is faster than count(*). It isn't
true. Use count(*).(You can look it up on asktom.oracle.com)
Jim

[Frank]

Jim Kennedy wrote:

"Geetha" <ge******@hotmail.com> wrote in message
news:4b**************************@posting.google.c om...

I searched in the Oracle documents what count (1) meant and I could
not find an answer. Can some one explain what Oracle does internally
when use count (1) VS count (*). Thank you very much in advance! We
use Oracle 9i.

There is a myth out there that count(1) is faster than count(*). It isn't
true. Use count(*).(You can look it up on asktom.oracle.com)
Jim

Well, as it is a myth, you might just as well count(1) ;-)
As long as you do not expect (or -even worse- explain!) it
to be faster

--
Regards, Frank van Bortel

[Daniel Roy]

I read somewhere (asktom?) that "select count(1) from table" is
internally rewritten as "select count(*) from (select 1 from table)",
and therefore just adds some overhead.

Daniel

ge******@hotmail.com (Geetha) wrote in message news:<4b**************************@posting.google. com>...

I searched in the Oracle documents what count (1) meant and I could
not find an answer. Can some one explain what Oracle does internally
when use count (1) VS count (*). Thank you very much in advance! We
use Oracle 9i.

[Frank]

Daniel Roy wrote:

I read somewhere (asktom?) that "select count(1) from table" is
internally rewritten as "select count(*) from (select 1 from table)",
and therefore just adds some overhead.

Daniel

ge******@hotmail.com (Geetha) wrote in message news:<4b**************************@posting.google. com>...

I searched in the Oracle documents what count (1) meant and I could
not find an answer. Can some one explain what Oracle does internally
when use count (1) VS count (*). Thank you very much in advance! We
use Oracle 9i.

NO!
Actually Tom wrote:
"Prove it, give me that test case. You are wrong.
COUNT(*) and COUNT(1) are the *same* -- the same -- the same -- no
different, the same.
count(1) is internally optimized to count(*) "

And he posted the explain plans for count(*) and count(1).

--
Regards, Frank van Bortel

[Daniel Roy]

Thanx Frank for the correction. I now remember that it was in a Google
thread that I saw this. But as Tom Kyte mentioned on the thread you
quote, it used to be that select count(*) and select count(1) were
treated differently, in versions 7 and lower. That's probably why I
saw that comment on an old Google thread about how differently they're
treated.

Daniel

Daniel Roy wrote:

I read somewhere (asktom?) that "select count(1) from table" is
internally rewritten as "select count(*) from (select 1 from table)",
and therefore just adds some overhead.

Daniel

ge******@hotmail.com (Geetha) wrote in message news:<4b**************************@posting.google. com>...

I searched in the Oracle documents what count (1) meant and I could
not find an answer. Can some one explain what Oracle does internally
when use count (1) VS count (*). Thank you very much in advance! We
use Oracle 9i.

NO!
Actually Tom wrote:
"Prove it, give me that test case. You are wrong.
COUNT(*) and COUNT(1) are the *same* -- the same -- the same -- no
different, the same.
count(1) is internally optimized to count(*) "

And he posted the explain plans for count(*) and count(1).