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
Solved

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

Posted on 2011-03-22
7
1,557 Views
Last Modified: 2013-12-19
Difference between count(1) and count(*).
How Using count(1) increases performance?
0
Comment
Question by:sakthikumar
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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 post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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 video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

809 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