Why is Select 1 faster than Select count(*)?

database performance oracle rdbms
user290 picture user290 · Sep 10, 2009 · Viewed 11.3k times · Source

In Oracle, when querying for row existence, why is Select 1 fast than Select count(*)?

Answer

MartW picture MartW · Sep 10, 2009

It is better still to use EXISTS where the RDBMS supports it or an equivalent, as this will stop processing rows as soon as it finds a match.

Related questions

How do you interpret a query's explain plan?

When attempting to understand how a SQL statement is executing, it is sometimes recommended to look at the explain plan. What is the process one should go through in interpreting (making sense) of an explain plan? What should stand out …

Read More
Using Oracle hint "FIRST_ROWS" to improve Oracle database performances

I have a statement that runs on Oracle database server. The statement has about 5 joins and there is nothing unusual there. It looks pretty much like below: SELECT field1, field2, field3, ... FROM table1, table2, table3, table4, table5 WHERE table1.id = …

Read More
How does database indexing work?

Given that indexing is so important as your data set increases in size, can someone explain how indexing works at a database-agnostic level? For information on queries to index a field, check out How do I index a database column.

Read More