Home » SQL & PL/SQL » SQL & PL/SQL » Diffderence between count(*) , count(1) (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production)
Diffderence between count(*) , count(1) [message #575649] Fri, 25 January 2013 03:23 Go to next message
saipradyumn
Messages: 183
Registered: October 2011
Location: Hyderabad
Senior Member

Hi Michel,

Could you please explain the difference between count(*) and count(1)
But from query prospective both are giving the same results

Thanks
SaiPrayumn


Re: Diffderence between count(*) , count(1) [message #575653 is a reply to message #575649] Fri, 25 January 2013 04:10 Go to previous messageGo to next message
Littlefoot
Messages: 19536
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Ask Tom.
Re: Diffderence between count(*) , count(1) [message #575658 is a reply to message #575649] Fri, 25 January 2013 04:40 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The best answer I can give is you want to count the rows, you don't want to count some 1 (why 1? why not 2? or 100?).

Now, Oracle knows many developers are dumb and so convert COUNT(1) to COUNT(*) anyway:
SQL> explain plan for select count(1) from emp;

Explained.

SQL> select * from table(dbms_xplan.display('PLAN_TABLE',null,'ALL'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
Plan hash value: 3381701820

-------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     1 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |        |     1 |            |          |
|   2 |   INDEX FULL SCAN| EMP_PK |    15 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / EMP@SEL$1

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]

Note the column projection, Oracle use to return the result.

So, be smart and use COUNT(*).

Regards
Michel

[Updated on: Fri, 25 January 2013 04:40]

Report message to a moderator

Re: Diffderence between count(*) , count(1) [message #575975 is a reply to message #575649] Wed, 30 January 2013 00:20 Go to previous messageGo to next message
aswary
Messages: 1
Registered: January 2013
Location: India
Junior Member
[font=Arial]both COUNT(*) and COUNT(1) execute with the same CPU time and elapsed time. Occasionally COUNT(*) would have a higher CPU time and sometimes COUNT(1) would have a higher CPU time. But neither is drastically different from the other. In addition to the statistics from the run if you look at the execution plans for both of these two they will be the exact same, providing further evidence that they behave the same.
Re: Diffderence between count(*) , count(1) [message #575977 is a reply to message #575975] Wed, 30 January 2013 00:53 Go to previous message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't read what I said, do you?
You didn't read the link LittleFoot provided, do you?

COUNT(1) IS COUNT(*) as Oracle internally converts COUNT(1) to COUNT(*).
COUNT(1) just shows you don't understand what you write.

Regards
Michel
Previous Topic: PL/SQl for multiple flat file formats
Next Topic: Re-Arrange Serial
Goto Forum:
  


Current Time: Tue Sep 02 00:04:47 CDT 2014

Total time taken to generate the page: 0.11210 seconds