DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 26

Thread: Select COUNT(*)

  1. 04-03-2003, 10:22 AM #11
    chrisrlong's Avatar
    chrisrlong
    chrisrlong is offline
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Ah, now you've touched upon the crux of the matter - set theory. Databases are set-oriented beasts. If you don't eat, sleep and breathe sets, then yer in the wrong line o' work . Intuitive solutions are best implemented in a nice, slow procedural language. Writing SQL is the exercise of taking all those nice, slow, intuitive steps and cramming them all together into a single statement that will run efficiently for millions of records.

    Set theory rulez!

    - Chris

    (but I digress, of course)
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong
    Reply With Quote Reply With Quote
  2. 04-03-2003, 10:26 AM #12
    efrijters's Avatar
    efrijters
    efrijters is offline humble grasshopper
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    The local Oracle-guru at my work told me that "SELECT COUNT(1) FROM t1"
    is much faster than "SELECT COUNT(*) FROM t2"

    Greetingzzz
    Erik
    Reply With Quote Reply With Quote
  3. 04-03-2003, 10:30 AM #13
    DaPi's Avatar
    DaPi
    DaPi is offline Old Cranky Philosopher
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    S{0} up to you Chris!
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
    Reply With Quote Reply With Quote
  4. 04-03-2003, 10:41 AM #14
    DaPi's Avatar
    DaPi
    DaPi is offline Old Cranky Philosopher
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by efrijters
    The local Oracle-guru at my work told me that "SELECT COUNT(1) FROM t1"
    is much faster than "SELECT COUNT(*) FROM t2"
    Urban legend?

    (Yes, of course, if t1 is much smaller than t2 but I think that was a typo.)
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
    Reply With Quote Reply With Quote
  5. 04-03-2003, 10:47 AM #15
    efrijters's Avatar
    efrijters
    efrijters is offline humble grasshopper
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Sorry, I meant: use COUNT(1) instead of COUNT(*)

    I'm going to test this - as soon as I solved my replication-problem. (This was one hell of a day: poor performance, non functional snapshot replication (cause: bug in 8i)...
    Sometimes I wonder why I work so hard to get certified for Oracle...)
    Reply With Quote Reply With Quote
  6. 04-03-2003, 11:10 AM #16
    DaPi's Avatar
    DaPi
    DaPi is offline Old Cranky Philosopher
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by efrijters
    Sorry, I meant: use COUNT(1) instead of COUNT(*)
    Now you've lost me . . .


    AFAIK and have tested in 8.1.7 CBO:
    - count(1)
    - count(*)
    - count('the rich meaning of life in all its mystery and glory')
    - count(primary_key)
    all run as fast as each other. Oracle is smart enough to know that it does NOT need the full row just to count it.
    count(distinct primary_key) is slower - but I don't know of anyone (other than me) who would dream of trying that!
    (count(NULL) of course gives the "wrong" answer.)
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
    Reply With Quote Reply With Quote
  7. 04-03-2003, 11:26 AM #17
    chrisrlong's Avatar
    chrisrlong
    chrisrlong is offline
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    DaPi's right - that old misconception has been disproved a million times but it refuses to die. Do us all a favor and go smack that 'guru' upside the head.

    - Chris

    (just kidding about the smacking part...mostly)
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong
    Reply With Quote Reply With Quote
  8. 04-03-2003, 11:36 AM #18
    efrijters's Avatar
    efrijters
    efrijters is offline humble grasshopper
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Sorry guys, next time I'll test something before I open my mouth...
    Last edited by efrijters; 04-03-2003 at 11:39 AM.
    Reply With Quote Reply With Quote
  9. 04-03-2003, 11:42 AM #19
    DaPi's Avatar
    DaPi
    DaPi is offline Old Cranky Philosopher
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by efrijters
    Sorry guys, next time I'll test something before I open my mouth...
    Oh, don't do that - it gives us a chance to beat up on people. Anyway, if God had wanted us to test, He wouldn't have given us end-users.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
    Reply With Quote Reply With Quote
  10. 04-03-2003, 11:48 AM #20
    chrisrlong's Avatar
    chrisrlong
    chrisrlong is offline
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Amen!
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules

Click Here to Expand Forum to Full Width