Use COUNT on indexed column or asterisk or 1

It is always prefered to use the count on indexed column or asterisk (*) or by representing it by one (1) for any table.

For showing the example all the below query will give the same output but there will be a difference in the execution plan.

Let us take the example of EMP table where an index is present on EMPNO column and ENAME column is not having any index on it, so let us first consider ENAME column to get the count and check its explan plan :

SQL>explain plan for
select count(ename) from emp_temp;

PLAN_TABLE_OUTPUT

Plan hash value: 2836287311

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | TABLE ACCESS FULL| EMP_TEMP | 23 | 161 | 2 (0)| 00:00:03 |
——————————————————————————-

Note
—–
– dynamic sampling used for this statement (level=2)

Now let us take the indexed column i.e. EMPNO column from EMP table:

SQL>explain plan for
select count(empno) from emp_temp;

PLAN_TABLE_OUTPUT

Plan hash value: 1671363213

———————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
———————————————————————-
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| EMPNO_IDX | 23 | 1 (0)| 00:00:02 |
———————————————————————-

Note
—–
– dynamic sampling used for this statement (level=2)
From the above mentioned plans we can see that it is more efficient by using the indexed column to get the counts.
The similar results are also retrived from the below queries by using asterisk or one.

# By using one (1) to get the counts:

SQL>explain plan for
select count(1) from emp_temp;

PLAN_TABLE_OUTPUT

Plan hash value: 1671363213

———————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
———————————————————————-
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| EMPNO_IDX | 23 | 1 (0)| 00:00:02 |
———————————————————————-

Note
—–
– dynamic sampling used for this statement (level=2)

# By using asterisk (*) to get the counts:

SQL>explain plan for
select count(*) from emp_temp;

PLAN_TABLE_OUTPUT

Plan hash value: 1671363213

———————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
———————————————————————-
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| EMPNO_IDX | 23 | 1 (0)| 00:00:02 |
———————————————————————-

Note
—–
– dynamic sampling used for this statement (level=2)

Now we know in which column or how to optimize our query if we are taking counts.

Advertisements

One thought on “Use COUNT on indexed column or asterisk or 1

  1. Thanks for sharing your knowledge and findings…
    Looks a small change but helps a lot to improve the performance..
    Many times we ignore this..
    This is surely going to help in better coding.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s