RE: Performance off "count(*)"
- From: "Marco Gralike" <Marco.Gralike@xxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 18 Jul 2008 19:28:25 +0200
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@xxxxxxxxxxxxxxxxxx]
Sent: vrijdag 18 juli 2008 16:20
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxx>
To: "Gints Plivna" <gints.plivna@xxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
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)
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
