RE: Performance off "count(*)"

  • From: "Marco Gralike" <Marco.Gralike@xxxxxxx>
  • To: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
  • Date: Fri, 18 Jul 2008 20:57:36 +0200

OK, Rub it in, Yeah I am Old !
 
;-) 
 
Nope, Niall I wasn't referring to the "count(1)" myth.
 
Let's rephrase the question.
 
Given that all statistics are in order and up to dat and given the basics 
Jonathan pointed out and given there is and index ( and not containing a NULL 
value in its column), 
then while doing a "select count(*) on "tab", it should use the index or not...?
 
I am on 11gR1
The "table" is 40 Gb big and contains roundabout 7 million records 
 
(Yep, the stupid, not so relational, XML stuff ;-)
 
 
 
 
 

________________________________

Van: Niall Litchfield [mailto:niall.litchfield@xxxxxxxxx]
Verzonden: vr 18-7-2008 20:29
Aan: Marco Gralike
CC: jonathan@xxxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Onderwerp: 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@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 
<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 <http://www.orawin.info/>  

Other related posts: