[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

is there any difference between count(*)  and count(column_name ) ?

Posted on 2005-05-17
17
Medium Priority
?
1,330 Views
Last Modified: 2011-10-03
Hi dear experts,
when select row number from a table we use count(*)  but i am wondering if there is a performance differance between count(*) and count(column_name)

select count(*)  from table ;

select count(table.column_name)  from table;

sincerely,
0
Comment
Question by:elmakarge
  • 4
  • 3
  • 3
  • +4
14 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 14020378
Technically you can't count on the asterisk (*), but if you could, there would be no difference whatsoever, as both count the numer of records, which would be the same for one field vs. the entire table.

Hope this helps.
-Jim
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 100 total points
ID: 14020447
Disagree a bit.  A count(*) will always do a full table scan since it includes nulls in the count.  count(table.column_name) will only count non-null columns and if there is an index on the column, it will scan the index.


sample test case showing this:
-----------------------------------------------
drop table tab1;

create table tab1( col1 char(1));


begin
      for i in 1..100 loop
            insert into tab1 values('a');
            insert into tab1 values(null);
            insert into tab1 values('b');
      end loop;
end;
/

commit;
create index tab1_idx on tab1(col1);

set autotrace;

select count(*) from tab1;
select count(col1) from tab1;


0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 14020473
The behavior I described was for Access.  Your database may differ.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:elmakarge
ID: 14020495
I use oracle 10g  and my sql sentences about 50 lines so  i need to performance
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 14020564
jimhorn,
I forgot I was in the general DB forum (I don't make it out of the Oracle forum often).

elmakarge,
In Oracle, one of the best ways to tune SQL is to use explain plan to show the execution plan for the SQL.  Check out the docs for assistance on how to do this:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/ex_plan.htm#19259
0
 
LVL 1

Expert Comment

by:Agro42
ID: 14021195
Count(column_name) is always more efficient than count(*) in an Oracle environment unless you have a single column table.  Something to do with the amount of memory used.
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 14021526
some versions of ingres count(1) worked best as it didn't require any data to be examined just the amount of data that existed
0
 
LVL 2

Expert Comment

by:dba9i_barak
ID: 14080278
Hey

  using count(*) will calculate all rows in the table without ignoring null values!
 using count(col_name) will calculate only the significant values that means NO null values.

in the matter of performence, using count(col_name) is better if you have an index on that column.
 
0
 

Expert Comment

by:johnmswan
ID: 14129136
I disagree with Agro42.

For ORACLE only, the bottom line is:

The fastest, most reliable and consistent method is always
select count(*) from table;

If you're using the cost based optimizer, count(*) WILL use the Primary Key index if the table has a Primary key defined.

Testcase Proof:

-- Create table and fill it
create table t50 (c1 number(11), c2 varchar2(50));
alter table t50 add constraint t50_pk primary key (c1) using index tablespace index_ts;
insert into t50 (select rownum,'12345678901235467890123546789012345678901235467890' from all_objects);
insert into t50 (select rownum+50000,'12345678901235467890123546789012345678901235467890' from all_objects);
insert into t50 (select rownum+100000,'12345678901235467890123546789012345678901235467890' from all_objects);
insert into t50 (select rownum+150000,'12345678901235467890123546789012345678901235467890' from all_objects);
insert into t50 (select rownum+200000,'12345678901235467890123546789012345678901235467890' from all_objects);
set autotrace on

Optimizer mode "CHOOSE" - Uses full table scan, count(*), count(1), count(c1) are exactly the same:

SQL> select count(c1) from t50;

 COUNT(C1)
----------
    238275


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T50'




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2285  consistent gets
       1086  physical reads
          0  redo size
        213  bytes sent via SQL*Net to client
        275  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(1) from t50;

  COUNT(1)
----------
    238275


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T50'




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2285  consistent gets
        723  physical reads
          0  redo size
        212  bytes sent via SQL*Net to client
        275  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 t50;

  COUNT(*)
----------
    238275


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T50'




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


Optimizer mode "FIRST_ROWS" - COST Based - Uses the index, count(*), count(1), count(c1) are STILL exactly the same:

SQL> alter session set optimizer_mode='FIRST_ROWS';

Session altered.

SQL> select count(*) from t50;

  COUNT(*)
----------
    238275


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=4 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'T50_PK' (UNIQUE) (Cost=4 Card
          =5963)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        502  consistent gets
          0  physical reads
          0  redo size
        213  bytes sent via SQL*Net to client
        275  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(1) from t50;

  COUNT(1)
----------
    238275


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=4 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'T50_PK' (UNIQUE) (Cost=4 Card
          =5963)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        502  consistent gets
          0  physical reads
          0  redo size
        213  bytes sent via SQL*Net to client
        275  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(c1) from t50;

 COUNT(C1)
----------
    238275


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=4 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'T50_PK' (UNIQUE) (Cost=4 Card
          =5963)





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


If you get something different, repeat the exercise as caching can give you different results the first time you execute a query.




0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 14130046
>>count(*) WILL use the Primary Key index if the table

disagree:  add some nulls into your test case and rerun.  count(*) will count nulls, count(c1) will not.

Also, you might want to generate statistics after loading the table and see if any of your testcases change.........
0
 

Assisted Solution

by:johnmswan
johnmswan earned 100 total points
ID: 14137597
johnmswan>>>count(*) WILL use the PRIMARY KEY INDEX IF THE TABLE HAS A PRIMARY KEY

slightwv>disagree:  add some nulls into your test case and rerun.  count(*) will count nulls, count(c1) will not.

With respect, you are wrong - you CAN'T add nulls to a primary key column because of course all primary keys are NOT NULL by definition.

So I repeat what I stated earlier, count(*), count(c1) and count(1) produce the SAME result and have exactly the SAME performance IF THE TABLE HAS A PRIMARY KEY (which almost all tables do).

IF the table does NOT have a primary key (and c1 is NULLable) then count(c1) will produce a different result because it will not count nulls but performance will be the SAME or WORSE - definitely NOT better.

> Also, you might want to generate statistics after loading the table and see if any of your testcases change.........

OK, I generated statistics (below), testcases DIDN'T change. Having statistics does force us into COST based optimizer (of course) but performance, results and query plans were exactly the same as I presented in my previous comment:

SQL> analyze table t50 compute statistics;
 
SQL> select count(*) From t50;

  COUNT(*)
----------
    238275


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=44 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'T50_PK' (UNIQUE) (Cost=44 Car
          d=238275)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        502  consistent gets
          0  physical reads
          0  redo size
        213  bytes sent via SQL*Net to client
        276  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(1) From t50;

  COUNT(1)
----------
    238275


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=44 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'T50_PK' (UNIQUE) (Cost=44 Car
          d=238275)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        502  consistent gets
          0  physical reads
          0  redo size
        213  bytes sent via SQL*Net to client
        276  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(c1) From t50;

 COUNT(C1)
----------
    238275


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=44 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'T50_PK' (UNIQUE) (Cost=44 Car
          d=238275)





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

Expert Comment

by:slightwv (䄆 Netminder)
ID: 14141257
>>With respect, you are wrong - you CAN'T add nulls to a primary key column because of course all primary keys are NOT NULL by definition.
Point made and accepted.  Overlooked the primary key piece of your post......
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 16159645
Recommend at minimum PAQ for slightwv 14020447
0
 

Expert Comment

by:johnmswan
ID: 16169023
I put quite a bit of work into this, correcting slightwv and providing two long posts with testcases and proofs. Was this all for nothing?
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question