0

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.

Ben
  • 51,770
  • 36
  • 127
  • 149
user1831003
  • 184
  • 1
  • 4
  • 13
  • possible duplicate of [Oracle SELECT TOP 10 records](http://stackoverflow.com/questions/2498035/oracle-select-top-10-records) – Raptor Nov 27 '12 at 07:38
  • @ShivanRaptor ... no, they didn't mention how to not use rownum in a way such that oracle still scans the entire table instead of stopping at first match. – user1831003 Nov 27 '12 at 08:05
  • @shivan please don't edit a question to include a link to as possible duplicate. At the moment only you think so as no one else has voted to close. If the question gets closed it will be automatically added. – Ben Nov 27 '12 at 08:54
  • Oracle will stop on first match unless you are doing a sort prior to the rownum filter, like so `select * from (select * from X order by Y) where rownum =1` With the sort Oracle may have to gather all the rows to determine the ordering to determine which is first. – Shannon Severance Nov 28 '12 at 00:18

2 Answers2

4

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.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
0

Using query hints is generally not recommended unless it is 101% sure that there is no other way to achieve the result (it is very important to remember the quote about premature optimization!!!), but for this purpose, there is a /*+ FIRST_ROWS(<integer>) */ hint that hints Oracle to optimize the execution plan for getting the first record as quick as possible.

select * /*+ FIRST_ROWS(1) */ from table1
where table1.column1 = 'somevalue' 
  AND table2.column2 = 'somevalue' 
  AND rownum = 1

Recommended reading:

Always remember: using hints is not always positive, the optimizer is pretty clever by itself... One can make queries much slower by applying hints than without. Also, if a hint works now, for example with the tables growing, it might hurt in the future... Also, hints are black magic, so unless you are a DBA, you should try to steer away from them, or at least seek help from a seasoned DBA. (Not in this quite straightforward case, but it might just help to rethink the query itself.)

ppeterka
  • 20,583
  • 6
  • 63
  • 78
  • 1
    I wouldn't rule out that first_rows is invoked anyway when a rownum predicate is attached. The behaviour is governed by a hidden parameter _optimizer_rownum_pred_based_fkr, the default value of which might vary between versions. – David Aldridge Nov 27 '12 at 11:00