count (1) and count (*)

Re: count (1) and count (*)

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

gelangov@hotmai l.com (Geetha) wrote in message news:<4b40e20a. 0310290724.13e8 6079@posting.go ogle.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).

Re: count (1) and count (*)

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
>
gelangov@hotmai l.com (Geetha) wrote in message news:<4b40e20a. 0310290724.13e8 6079@posting.go ogle.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

Re: count (1) and count (*)

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

gelangov@hotmai l.com (Geetha) wrote in message news:<4b40e20a. 0310290724.13e8 6079@posting.go ogle.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.

Re: count (1) and count (*)

Jim Kennedy wrote:

"Geetha" <gelangov@hotma il.comwrote in message
news:4b40e20a.0 310290724.13e86 079@posting.goo gle.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.

>
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.c om)
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

Re: count (1) and count (*)

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" <kennedy-downwithspammer sfamily@attbi.n etwrote in message
news:H1Rnb.4010 8$mZ5.224243@at tbi_s54...

>
"Geetha" <gelangov@hotma il.comwrote in message
news:4b40e20a.0 310290724.13e86 079@posting.goo gle.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.

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.c om)
Jim
>
>

Re: count (1) and count (*)


"Geetha" <gelangov@hotma il.comwrote in message
news:4b40e20a.0 310290724.13e86 079@posting.goo gle.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.

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.c om)
Jim

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.