Search code examples
sqloracle-databaseoracle11grownum

Does Oracle fetch all the rows before evaluating rownum?

I have a table that will have millions of records.

I want to make sure Oracle stops looking as long as it finds its first match. 

select * from table1
where table1.column1 = 'somevalue' 
  AND table2.column2 = 'somevalue' 
  AND rownum = 1

I heard about that Oracle will fetch ALL the rows that meet the criteria on column1 and column2 and then only apply the rownum filter to get the first row, which defeats the purpose.

Solution

  • Run an explain plan on the query, and you'll find that you heard wrong -- Oracle will apply the rownum predicate as soon as a row is identified as part of the result set based on the rest of the query.