Re: Performance off "count(*)"

I suspect that what you are remembering is a rebuttal of an old myth (well it *might* have been true once, but I doubt it) that using select count(1) from <tab>;
was more efficient than
select count(*) from <tab>;
in determining rowcounts. This hasn't been true, if it ever was, for at least a decade. For normal tables there's no difference - obviously those who indulge in a bit of xml may experience odd results :)

Niall

On Fri, Jul 18, 2008 at 6:28 PM, Marco Gralike <Marco.Gralike_at_amis.nl> wrote:

>
> Am I correct in my "small understanding" of Oracle that "count(*)" has
> been optimized.
>
> I think I once heard or read this from Tom Kyte, but then again I am
> also becoming old and could be mistaken (hearing not good and all)
>
> ;-)
>
>
> The "issue" for me is that if "count(*)" is optimized, then I have found
> a small feature that the XMLDB Development Team can use in its
> advantage.
>
> Regards
>
>
> Marco
>
>
>
> -----Original Message-----
> From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk]
> Sent: vrijdag 18 juli 2008 16:20
> To: oracle-l_at_freelists.org
> Subject: Re: Performance off "count(*)"
>
>
> In principle the optimizer knows that count(*) means
> "how many rows in the table" and will choose the
> smallest object that could return the result to do the
> scan.
>
> This means it may choose to do a fast-full scan of an
> index if at least one column of the index is declared
> as not null.
>
>
> Depending on version, the choice between indexes of
> identical cost may simply be based on alphabetical ordering
> of the names - although number of distinct keys is supposed
> to have an effect as well in recent versions
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentals
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> ----- Original Message -----
> From: "Marco Gralike" <Marco.Gralike_at_AMIS.nl>
> To: "Gints Plivna" <gints.plivna_at_gmail.com>
> Cc: <oracle-l_at_freelists.org>
> Sent: Friday, July 18, 2008 2:11 PM
> Subject: RE: Performance off "count(*)"
>
>
>
> That's not really what I ment.
>
>
> While using "count(*)", Oracle will performance optimize (as far as I
> know) the "count(*)" so it will do a smarter job then "only" a full
> table scan.
>
> I am interested in the how and what regarding the mechanics / methods
> behind it. In my "not so relational XMLDB" environment, I see a full
> table scan were I would have hoped for the smarter way to go for an
> index. The count via the index will last for 3 minutes. The count via
> the full table scan will take more than 1 day (17 Gb of XML data,
> approx. 7 milion records).
>
>
>
> I have an small example of this smarter behavior below (I thought it was
> somehow hardcoded):
>
>
>
>
> Also see the differences (I have to force it to go via the PK unique
> index via a hint)
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l