Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    153

    Unanswered: Performance & Tunning

    Hi All,

    Could any one let me know which below statement gives better performance in Oracle and why?

    select count(*) from Emp
    select count(Empno) from Emp -- Empno is Indexed column
    select count(1) from Emp

    Thanks with Regards,
    JD

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    count(1) is mapped to count(*) by the database anyway, but count(*) tells the database to count all the records the fastest way it can find. count(empno) will need to do a full tablescan (unless not null and unique index) because it wont count null empno's. so your answer is use count(*)
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Feb 2009
    Posts
    62
    I would anticipate that count(empno) would be as fast or faster than count(*).

    If empno has a not null constraint on it, and is indexed, then count(*) would quite possibly do a Index FFS to get the count.

    If empno can be null, then count(empno) will can still use the empno index (because it is only counting the rows where empno is not null - which are the indexed ones), whereas count(*) will go and count the number of rows in the table.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    The explain plan for any table, where I have a primary key, shows that SELECT COUNT(*) does not use a full tablescan.

    --=Chuck

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    count(1) and count(*) and count(column) = same
    I fail to see the reason why you would not do your own performance test.
    Last edited by The_Duck; 08-20-09 at 16:03.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    well, since you asked here are the identical results on each type
    (no, you didn't ask, but nevertheless):
    PHP Code:
    duck@db1set autotrace on explain statistics
    duck
    @db1set timing on
    duck
    @db1set lines 600
    duck
    @db1select count(*) from lodstest.m_individual_telephone;

        
    COUNT(*)
    ------------
      
    21,905,243

    Elapsed
    00:00:02.42

    Execution Plan
    ----------------------------------------------------------
    Plan hash value1666076747

    ----------------------------------------------------------------------------------------------
    Id  Operation             Name                         Rows  Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------
    |   
    SELECT STATEMENT      |                              |     19434   (2)| 00:03:54 |
    |   
    |  SORT AGGREGATE       |                              |     |            |          |
    |   
    |   INDEX FAST FULL SCANIX_M_INDIVIDUAL_TELEPHONE_02 |    21M19434   (2)| 00:03:54 |
    ----------------------------------------------------------------------------------------------


    Statistics
    ----------------------------------------------------------
              
    1  recursive calls
              0  db block gets
          73461  consistent gets
              0  physical reads
              0  redo size
            210  bytes sent via SQL
    *Net to client
            248  bytes received via SQL
    *Net from client
              2  SQL
    *Net roundtrips to/from client
              0  sorts 
    (memory)
              
    0  sorts (disk)
              
    1  rows processed

    duck
    @db1select count(1from lodstest.m_individual_telephone;

      
    COUNT(1)
    ----------
      
    21905243

    Elapsed
    00:00:02.28

    Execution Plan
    ----------------------------------------------------------
    Plan hash value1666076747

    ----------------------------------------------------------------------------------------------
    Id  Operation             Name                         Rows  Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------
    |   
    SELECT STATEMENT      |                              |     19434   (2)| 00:03:54 |
    |   
    |  SORT AGGREGATE       |                              |     |            |          |
    |   
    |   INDEX FAST FULL SCANIX_M_INDIVIDUAL_TELEPHONE_02 |    21M19434   (2)| 00:03:54 |
    ----------------------------------------------------------------------------------------------


    Statistics
    ----------------------------------------------------------
              
    1  recursive calls
              0  db block gets
          73461  consistent gets
              0  physical reads
              0  redo size
            227  bytes sent via SQL
    *Net to client
            248  bytes received via SQL
    *Net from client
              2  SQL
    *Net roundtrips to/from client
              0  sorts 
    (memory)
              
    0  sorts (disk)
              
    1  rows processed

    duck
    @db1select count(indiv_idfrom lodstest.m_individual_telephone;

    COUNT(INDIV_ID)
    ---------------
           
    21905243

    Elapsed
    00:00:02.26

    Execution Plan
    ----------------------------------------------------------
    Plan hash value1666076747

    ----------------------------------------------------------------------------------------------
    Id  Operation             Name                         Rows  Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------
    |   
    SELECT STATEMENT      |                              |     19434   (2)| 00:03:54 |
    |   
    |  SORT AGGREGATE       |                              |     |            |          |
    |   
    |   INDEX FAST FULL SCANIX_M_INDIVIDUAL_TELEPHONE_02 |    21M19434   (2)| 00:03:54 |
    ----------------------------------------------------------------------------------------------


    Statistics
    ----------------------------------------------------------
              
    1  recursive calls
              0  db block gets
          73461  consistent gets
              0  physical reads
              0  redo size
            234  bytes sent via SQL
    *Net to client
            248  bytes received via SQL
    *Net from client
              2  SQL
    *Net roundtrips to/from client
              0  sorts 
    (memory)
              
    0  sorts (disk)
              
    1  rows processed 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Feb 2009
    Posts
    62
    count(1) and count(*) and count(column) = same
    ONLY if the column has a not null constraint on it. COUNT(column) counts the number of non-null values in column

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    good point JRow. I was counting the PK in my example.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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