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