Saturday, July 4, 2009

SELECT COUNT(*) v/s SELECT COUNT(1) - FASTER??

I was writing a PL/SQL code one day using select count(*) from Table_name when somebody told me that select count(1) is better and faster. Although, he couldn't tell me the reason but he gave me a fact. So i jumped upon to search the reason.

Select count(*) uses a TABLE SCAN ie all the rows are looked upon one by one based on your WHERE CLAUSE whereas select count(1) uses an INDEX SCAN which has better performance as the scan is based on indexes.

TABLE scan is faster if the table is small.