Solved

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

Posted on 2011-03-22
7
1,558 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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 69

Accepted Solution

by:
Qlemo earned 167 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 166 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 167 total points
ID: 35204831
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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

726 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