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@xxxxxxx> 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@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 > > > -- Niall Litchfield Oracle DBA http://www.orawin.info