?
Solved

Difference between count(1) and count(*).

Posted on 2011-03-22
7
Medium Priority
?
1,573 Views
Last Modified: 2013-12-19
Difference between count(1) and count(*).
How Using count(1) increases performance?
0
Comment
Question by:sakthikumar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 27

Expert Comment

by:Tolomir
ID: 35188382
Since the COUNT function will return the same results regardless of what NOT NULL field(s) you include as the COUNT function parameters (ie: within the brackets), you can change the syntax of the COUNT function to COUNT(1) to get better performance as the database engine will not have to fetch back the data fields.
0
 
LVL 27

Expert Comment

by:Tolomir
ID: 35188391
For example, based on the example above, the following syntax would result in better performance:

SELECT department, COUNT(1) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

Now, the COUNT function does not need to retrieve all fields from the employees table as it had to when you used the COUNT(*) syntax. It will merely retrieve the numeric value of 1 for each record that meets your criteria.
0
 
LVL 27

Expert Comment

by:Tolomir
ID: 35188412
The point is a count (*) needs each field in a dataset row.
count(1) instead doesn't look at the real data in the dataset.

Imagine a dataset with 1 million fields per row. Couting 10 rows would result in checking 10 million dates.
counting 10 rows of (1) results in adding 10 times the number 1.

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!

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35188414
if it is small table which you are querying, you might not see a real performance gain for count(1) or count(*)

Technically both are same and can produce the same output. but * when used with count(*) means count all records. count(1) or count(2) are all basically the same and will produce the same output which is just to count the number of records.
0
 
LVL 70

Accepted Solution

by:
Qlemo earned 668 total points
ID: 35188494
That's a rumor . The days are long gone when using count(*) could introduce more work. In fact, since the number of records is counted without considering NULL, there is no difference anymore between a count(1) or count(*). Size does not matter.

However, count(column) and count(distinct column) and count(*) are all different in results and performance (distinct requires an additional sort and elimination of duplicates).

The same rumor tells people a
  select * from tbl1 where exists (select one from tbl2 where tbl1.col1 = tbl2.col1)
could make a difference compared with
  select * from tbl1 where exists (select * from tbl2 where tbl1.col1 = tbl2.col1)
That's wrong, too. Any modern Query Optimizer will disregard the column list in the subselect when used for EXISTS.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 664 total points
ID: 35188519
It's a myth, there is no performance gain by using 1 vs *.  

Generate an explain plan for both and you'll see there is no difference between them
0
 
LVL 13

Assisted Solution

by:riazpk
riazpk earned 668 total points
ID: 35204831
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

765 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