9 Replies Latest reply on Oct 15, 2010 5:31 AM by 733880

    COUNT(*) --> COUNT(1)

    733880
      Hello All,

      I have the following procedure; when i run with the TOAD expert it says the following warning message.

      "Ensure SELECT COUNT(*) is being used to check the number of rows and not merely the existence of rows."


      So will i replace the COUNT(1) with COUNT(*)??

      Is this make a difference???

      Plz help...
       
       PROCEDURE proc_pr
       IS
       BEGIN
      
           FOR l_rec IN c_rec
           LOOP
        -- 
                IF l_rec = 'Y' 
                THEN
                     .....
                ELSE 
                     IF (l_last_loc_nbr = -1 OR (l_last_loc_nbr <> l_lui_rec.loc_nbr OR
           
                     THEN
                          --l_cnt will return only one value since i am fetching using PRIMARY key.
                          SELECT COUNT(1)
      
                          INTO l_cnt
      
                          FROM TABLE_1
      
                          WHERE --
      
                          IF l_cnt = 0 THEN
      
                               l_error_msg := 'Invalid value';                      
                  
                          END IF;
      
                     END IF;
                
                END IF;
           END LOOP;
      EXCEPTION
       --
       --
      END proc_pr;
      Thanks for your comments....

      Edited by: Linus on Oct 10, 2010 10:42 PM
        • 1. Re: COUNT(*) --> COUNT(1)
          hm
          http://asktom.oracle.com/pls/asktom/f?p=100:11:3376264199595006::::P11_QUESTION_ID:1156159920245
          1 person found this helpful
          • 2. Re: COUNT(*) --> COUNT(1)
            It won't make any difference.
            The optimizer knows count(*) is a special case and treats it as count(1)
            Now, whether it is better to follow convention and to use count(*) that is a different issue.

            ---------------
            Sybrand Bakker
            Senior Oracle DBA
            • 3. Re: COUNT(*) --> COUNT(1)
              652398
              "Ensure SELECT COUNT(*) is being used to check the number of rows and not merely the existence of rows."

              In my mind, function count (including count(1) and count(*)) will count the number of rows regardless of empty rows. Therefore, their results will be the same, right?
              • 4. Re: COUNT(*) --> COUNT(1)
                733880
                Thanks hm and Sybrand for your prompt reply.

                Sybrand,

                If both are same; why does Oracle Expert throw that warning.

                So better i'll modify the code COUNT(1) with COUNT(*).
                • 5. Re: COUNT(*) --> COUNT(1)
                  650063
                  you can also use string-constant as parameter/argument for count-function:
                  SELECT COUNT('fanta') FROM Dual
                  • 6. Re: COUNT(*) --> COUNT(1)
                    189821
                    Read the message once more!
                    If you want to know if a table has records, you don't have to count them all.
                    Just try an get one.
                    In your case, as you are using the primary key, this is no problem, Toad just doesn't know it.
                    But consider this:
                    SQL>SET AUTOTRACE ON
                    SQL>SET TIMING ON
                    SQL>SELECT COUNT(*) FROM t;
                    
                      COUNT(*)
                    ----------
                       6270464
                    
                    Elapsed: 00:00:00.61
                    
                    Execution Plan
                    ----------------------------------------------------------
                       0      SELECT STATEMENT Optimizer=CHOOSE
                       1    0   SORT (AGGREGATE)
                       2    1     TABLE ACCESS (FULL) OF 'T'
                    
                    
                    
                    
                    Statistics
                    ----------------------------------------------------------
                              0  recursive calls
                              0  db block gets
                           4801  consistent gets
                              0  physical reads
                              0  redo size
                            213  bytes sent via SQL*Net to client
                            234  bytes received via SQL*Net from client
                              2  SQL*Net roundtrips to/from client
                              0  sorts (memory)
                              0  sorts (disk)
                              1  rows processed
                    
                    SQL>SELECT COUNT(*) FROM t WHERE ROWNUM = 1;
                    
                      COUNT(*)
                    ----------
                             1
                    
                    Elapsed: 00:00:00.01
                    
                    Execution Plan
                    ----------------------------------------------------------
                       0      SELECT STATEMENT Optimizer=CHOOSE
                       1    0   SORT (AGGREGATE)
                       2    1     COUNT (STOPKEY)
                       3    2       TABLE ACCESS (FULL) OF 'T'
                    
                    
                    
                    
                    Statistics
                    ----------------------------------------------------------
                              0  recursive calls
                              0  db block gets
                              4  consistent gets
                              0  physical reads
                              0  redo size
                            210  bytes sent via SQL*Net to client
                            234  bytes received via SQL*Net from client
                              2  SQL*Net roundtrips to/from client
                              0  sorts (memory)
                              0  sorts (disk)
                              1  rows processed
                    
                    SQL>SELECT 1 FROM DUAL WHERE EXISTS(SELECT NULL FROM t);
                    
                             1
                    ----------
                             1
                    
                    Elapsed: 00:00:00.01
                    
                    Execution Plan
                    ----------------------------------------------------------
                       0      SELECT STATEMENT Optimizer=CHOOSE
                       1    0   FILTER
                       2    1     TABLE ACCESS (FULL) OF 'DUAL'
                       3    1     TABLE ACCESS (FULL) OF 'T'
                    
                    
                    
                    
                    Statistics
                    ----------------------------------------------------------
                              0  recursive calls
                              0  db block gets
                              7  consistent gets
                              0  physical reads
                              0  redo size
                            203  bytes sent via SQL*Net to client
                            234  bytes received via SQL*Net from client
                              2  SQL*Net roundtrips to/from client
                              0  sorts (memory)
                              0  sorts (disk)
                              1  rows processed
                    4801 vs. 4 consistents gets: "WHERE ROWNUM = 1" makes a huge difference - this is what Toad is trying to tell you.

                    Urs
                    1 person found this helpful
                    • 7. Re: COUNT(*) --> COUNT(1)
                      635471
                      I think that what Toad is warning you of here is not related to the difference between count(*) and count(1) (and you should use count(*) as others say), but that people sometimes check to see if there is a row that matches a condition in a table by coding something like:
                      select count(*) into row_count
                      from my-table
                      where ...
                      This could be inefficient because there might be 1,000,000 rows that meet the condition and which therefore get accessed during the SQL execution.

                      If that's the case, and it appears not to be in your case because you access the table by the primary key, then you can use a construct like:
                      select count(*) into row_count
                      from my-table
                      where rownum = 1 and ...
                      or
                      select count(*) into row_count
                      from dual
                      where exists (
                      select null
                      from my-table
                      where ... )
                      Count(*) is still useful because "select count(*) from ..." always returns one row (and never returns null) so it's easier to code with -- no need for handling NO_DATA_FOUND exceptions.
                      1 person found this helpful
                      • 8. Re: COUNT(*) --> COUNT(1)
                        BluShadow
                        sybrand_b wrote:
                        It won't make any difference.
                        The optimizer knows count(*) is a special case and treats it as count(1)
                        Now, whether it is better to follow convention and to use count(*) that is a different issue.
                        It's the other way round. Count(1) is rewritten by the optimizer as Count(*)

                        Padders: Re: Count(*)/Count(1)
                        Rafu: Re: Difference between count(*) & count(1)
                        1 person found this helpful
                        • 9. Re: COUNT(*) --> COUNT(1)
                          733880
                          Thanks All for your reply....

                          It was indeed a great response from you all experts!

                          Really proud to be a part of this group....