count (1) and count (*)

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Geetha
    #1

    count (1) and count (*)

    I searched in the Oracle documents what count (1) meant and I could
    not find an answer. Can some one explain what Oracle does internally
    when use count (1) VS count (*). Thank you very much in advance! We
    use Oracle 9i.
    Tags: None
    • Jim Kennedy
      #2
      Re: count (1) and count (*)


      "Geetha" <gelangov@hotma il.comwrote in message
      news:4b40e20a.0 310290724.13e86 079@posting.goo gle.com...
      I searched in the Oracle documents what count (1) meant and I could
      not find an answer. Can some one explain what Oracle does internally
      when use count (1) VS count (*). Thank you very much in advance! We
      use Oracle 9i.
      There is a myth out there that count(1) is faster than count(*). It isn't
      true. Use count(*).(You can look it up on asktom.oracle.c om)
      Jim


        Comment

        • mcstock
          #3
          Re: count (1) and count (*)

          it used to be true prior to somewhere in v7 -- should not be true anymore,
          but it's always good to do an EXPLAIN PLAN or SET AUTOTRACE ON to make sure

          -- mcs

          "Jim Kennedy" <kennedy-downwithspammer sfamily@attbi.n etwrote in message
          news:H1Rnb.4010 8$mZ5.224243@at tbi_s54...
          >
          "Geetha" <gelangov@hotma il.comwrote in message
          news:4b40e20a.0 310290724.13e86 079@posting.goo gle.com...
          I searched in the Oracle documents what count (1) meant and I could
          not find an answer. Can some one explain what Oracle does internally
          when use count (1) VS count (*). Thank you very much in advance! We
          use Oracle 9i.
          There is a myth out there that count(1) is faster than count(*). It isn't
          true. Use count(*).(You can look it up on asktom.oracle.c om)
          Jim
          >
          >

            Comment

            • Frank
              #4
              Re: count (1) and count (*)

              Jim Kennedy wrote:
              "Geetha" <gelangov@hotma il.comwrote in message
              news:4b40e20a.0 310290724.13e86 079@posting.goo gle.com...
              >
              >>I searched in the Oracle documents what count (1) meant and I could
              >>not find an answer. Can some one explain what Oracle does internally
              >>when use count (1) VS count (*). Thank you very much in advance! We
              >>use Oracle 9i.
              >
              There is a myth out there that count(1) is faster than count(*). It isn't
              true. Use count(*).(You can look it up on asktom.oracle.c om)
              Jim
              >
              >
              Well, as it is a myth, you might just as well count(1) ;-)
              As long as you do not expect (or -even worse- explain!) it
              to be faster

              --
              Regards, Frank van Bortel

                Comment

                • Daniel Roy
                  #5
                  Re: count (1) and count (*)

                  I read somewhere (asktom?) that "select count(1) from table" is
                  internally rewritten as "select count(*) from (select 1 from table)",
                  and therefore just adds some overhead.

                  Daniel

                  gelangov@hotmai l.com (Geetha) wrote in message news:<4b40e20a. 0310290724.13e8 6079@posting.go ogle.com>...
                  I searched in the Oracle documents what count (1) meant and I could
                  not find an answer. Can some one explain what Oracle does internally
                  when use count (1) VS count (*). Thank you very much in advance! We
                  use Oracle 9i.

                    Comment

                    • Frank
                      #6
                      Re: count (1) and count (*)

                      Daniel Roy wrote:
                      I read somewhere (asktom?) that "select count(1) from table" is
                      internally rewritten as "select count(*) from (select 1 from table)",
                      and therefore just adds some overhead.
                      >
                      Daniel
                      >
                      gelangov@hotmai l.com (Geetha) wrote in message news:<4b40e20a. 0310290724.13e8 6079@posting.go ogle.com>...
                      >
                      >>I searched in the Oracle documents what count (1) meant and I could
                      >>not find an answer. Can some one explain what Oracle does internally
                      >>when use count (1) VS count (*). Thank you very much in advance! We
                      >>use Oracle 9i.
                      NO!
                      Actually Tom wrote:
                      "Prove it, give me that test case. You are wrong.
                      COUNT(*) and COUNT(1) are the *same* -- the same -- the same -- no
                      different, the same.
                      count(1) is internally optimized to count(*) "

                      And he posted the explain plans for count(*) and count(1).

                      --
                      Regards, Frank van Bortel

                        Comment

                        • Daniel Roy
                          #7
                          Re: count (1) and count (*)

                          Thanx Frank for the correction. I now remember that it was in a Google
                          thread that I saw this. But as Tom Kyte mentioned on the thread you
                          quote, it used to be that select count(*) and select count(1) were
                          treated differently, in versions 7 and lower. That's probably why I
                          saw that comment on an old Google thread about how differently they're
                          treated.

                          Daniel

                          Daniel Roy wrote:
                          I read somewhere (asktom?) that "select count(1) from table" is
                          internally rewritten as "select count(*) from (select 1 from table)",
                          and therefore just adds some overhead.

                          Daniel

                          gelangov@hotmai l.com (Geetha) wrote in message news:<4b40e20a. 0310290724.13e8 6079@posting.go ogle.com>...
                          >I searched in the Oracle documents what count (1) meant and I could
                          >not find an answer. Can some one explain what Oracle does internally
                          >when use count (1) VS count (*). Thank you very much in advance! We
                          >use Oracle 9i.
                          >
                          NO!
                          Actually Tom wrote:
                          "Prove it, give me that test case. You are wrong.
                          COUNT(*) and COUNT(1) are the *same* -- the same -- the same -- no
                          different, the same.
                          count(1) is internally optimized to count(*) "
                          >
                          And he posted the explain plans for count(*) and count(1).

                            Comment

                            Previous template Next
                            Working...