1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

group function

Discussion in 'SQL PL/SQL' started by kishore garlapati, Oct 19, 2010.

  1. kishore garlapati

    kishore garlapati Active Member

    Messages:
    15
    Likes Received:
    1
    Trophy Points:
    110
    what's the main difference between
    count(*) and count(1),and in place of projection operator if i gave any numeric value
    the o/p is generated with same result.In this function numeric value represents
    which one either column or row
    plz clarify me
    Thanks in advance
     
  2. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Hi kishore,

    There is no Difference between count(*) and count(1). Also count(1), count(2) or count(3) doesn't makes any difference. They implement ALL (col name) functionality in COUNT aggregate function.

    The only specific thing is that it counts all the rows irrespective of columns having NULL value. Instead, if you specify column name in COUNT, it will count only NOT NULL values of the column.

    Code (SQL):


    SQL> SELECT COUNT(*) FROM mytab;

      COUNT(*)
    ----------
         29859


    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=41 Card=1)
       1    0   SORT (AGGREGATE)
       2    1     INDEX (FAST FULL SCAN) OF 'SYS_C0011582' (INDEX (UNIQUE)
              ) (Cost=41 Card=14962)


    SQL> SELECT COUNT(44) FROM mytab;

     COUNT(44)
    ----------
         29859


    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=41 Card=1)
       1    0   SORT (AGGREGATE)
       2    1     INDEX (FAST FULL SCAN) OF 'SYS_C0011582' (INDEX (UNIQUE)
              ) (Cost=41 Card=14962)

     
     
  3. kishore garlapati

    kishore garlapati Active Member

    Messages:
    15
    Likes Received:
    1
    Trophy Points:
    110
    Thank you so much
    But i've a clarification Numeric value represents which one?
    plz keep my proble and give proper solution

    ThanX in advance
     
  4. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    count(1) is just like a counter intuitive, which can accpet any numeric value, it hardly matters. And also it does not refers to any row number or column number. It is internally taken as count(*) by Oracle server.

    The only difference in larger applications is the performance. Count(*) is bit faster than count(1), when it comes to huge data.