Oracle – Count(1) Vs. Count(*) Top Difference

In Oracle, the COUNT(1) and (*) functions are used to count the number of rows in a table or result set. However, there is a subtle difference between them.

Table of contents

1. Count(1) Usage
2. Count(*) Usage
3. No difference in Performance
4. Conclusion

Count(1) Usage

• The COUNT(1) function counts the number of rows where the expression 1 evaluates to a non-null value.
• Since 1 is a constant and always non-null, using COUNT(1) is a common way to perform row counting.

Count(*) Usage

• On the other hand, COUNT(*) counts the number of rows based on all columns in the result set, which includes all columns from all tables involved in the query. It doesn’t exclude rows where any of the columns might contain a null value.
• In most cases, there isn’t a significant performance difference between COUNT(1) and COUNT(*) in Oracle. However, there might be a slight difference in the execution time due to the internal mechanisms involved in evaluating the expressions.
• It’s worth noting that using COUNT(*) might be preferred when you want an accurate count that considers all rows, especially when using joins or subqueries where null values might be involved. On the other hand, using COUNT(1) is generally a safe and efficient choice for simple row counting.

No difference in Performance

I created a Table called agents. I executed with count(1) and count(*) and captured the time taken. In these two cases, it shows the same time.

Query to execute for Count(1)

DECLARE
    start_time TIMESTAMP;
    end_time TIMESTAMP;
    results NUMBER;
BEGIN
    start_time := CURRENT_TIMESTAMP;
    -- Your query goes here
    SELECT COUNT(1) 
        INTO results
        FROM agents;
    
    end_time := CURRENT_TIMESTAMP;
    
    DBMS_OUTPUT.PUT_LINE('Query Execution Time: ' || (end_time - start_time));
END;
/

Elapsed time

Statement processed.

Query Execution Time: +000000000 00:00:00.000083000

Query to execute for Count(*)

DECLARE
    start_time TIMESTAMP;
    end_time TIMESTAMP;
    results NUMBER;
BEGIN
    start_time := CURRENT_TIMESTAMP;
    -- Your query goes here
    SELECT COUNT(*) 
        INTO results
        FROM agents;
    
    end_time := CURRENT_TIMESTAMP;
    
    DBMS_OUTPUT.PUT_LINE('Query Execution Time: ' || (end_time - start_time));
END;
/

Elapsed time

There is a negligible difference in milliseconds.

Statement processed.

Query Execution Time: +000000000 00:00:00.001033000

Conclusion

While both COUNT(1) and COUNT(*) are used to count the number of rows in a table or result set, they have subtle differences. COUNT(1) counts the rows where the expression 1 is not null, making it a common and efficient choice. On the other hand, COUNT(*) considers all columns in the result set and does not exclude rows with null values.

In Oracle, there isn’t a significant difference in performance between COUNT(1) and COUNT(*). Using COUNT(*) may be preferred when an accurate count of all rows is necessary, especially in scenarios involving joins or subqueries with null values. However, COUNT(1) is generally a safe and efficient choice for simple row counting.

Comparing the execution times for COUNT(1) and COUNT(*) on the ‘agents’ table revealed a negligible difference in milliseconds, indicating that there is no significant disparity in performance.