Best way to write Oracle Select Statement with rownum = 1 and order by

Question

I have an oracle select statement but it takes 1.58 secounds to get my 1 row which is long time for this single query > table contain a lot of data

 select g.id
   from TBL1 g 
   left join TBL2 b on g.REF1= b.REF2 
  where b.id = 286 
    and ROWNUM = 1 
  order by g.id desc

But when I select all of data without ordering nor limiting it takes 0.027 !!

What is the best way to achieve this ?

The whole query whithout an ORDER BY clause may give even 1000000 rows, but your client (I guess - SQL-Developer or similar one) displays only first 50-100 rows. It is fast. But if you use the `ORDER BY` then the RDBMS must first sort all 1000000, then pick the higest/lowest 1 row. It must take some time. If you want to optimize this query then run `EXPLAIN PLAN FOR your_qury`, then `SELECT * FROM table(DBMS_XPLAN.Display)` and append a result of last query to the question. This is a first basic step to analyze performance problems. — krokodilko, Aug 24 '17 at 15:52
@Abu Yousef :Rownum and order by clause doesn't fit in one statement ,you definitely need to your rownum in outer query ,check this https://stackoverflow.com/questions/9240192/selecting-the-second-row-of-a-table-using-rownum — Gaurav Soni, Aug 24 '17 at 15:57
Edited: SQL is much easier to read when it's not all on one line! — Tony Andrews, Aug 24 '17 at 15:58
@Abu Yousef : What if you asked to pick a coin randomly from the basket containing distinct denomination 1,2,3,4,5,6,7,8,9,10 .It will be easy right.What if you have asked to pick the lowest denomination coin from the basket.For that you need to scan all the coins denomination and came to the conclusion that 1 is smaller ,same way oracle do the operation without ordering ,it pick and provide a random data.But if you ask oracle to order and fetch the lowest ,it will take time depend on the size of the table. — Gaurav Soni, Aug 24 '17 at 16:08
Thanks all for your explanation, very clear now. but @GauravSoni as you advise to put rownum in outer query... I run it with no difference in performance or result :S — Abu Yousef, Aug 24 '17 at 16:11
@AbuYousef don't look for performance now,understand the basic first of retrieving the data ,compare the result with both the query and you can understand what i am taking about. Regarding performance ,index on tbL1.id might help , as index by default are stored in ascending order ,please test though — Gaurav Soni, Aug 24 '17 at 16:16
you can remove 'ROWNUM = 1 order by g.id desc ' clauses and edit select max( g.id). Maybe it will run more fast. — mehmet sahin, Aug 24 '17 at 17:21

Jeffrey Kemp · Answer 1 · 2017-08-25T03:02:23.850

If you're on Oracle 12.1 you can use a Top-N query:

select g.id
   from TBL1 g 
   left join TBL2 b on g.REF1= b.REF2 
  where b.id = 286 
  order by g.id desc
FETCH FIRST 1 ROW ONLY

Otherwise, you can get the first row using a subquery:

select * from (
  select g.id
     from TBL1 g 
     left join TBL2 b on g.REF1= b.REF2 
    where b.id = 286 
    order by g.id desc
) where ROWNUM = 1

Both of these forms allow Oracle to use top-N optimisation to avoid keeping the entire result set in temp while it sorts the results.

To optimise further, you would need to look at your tables, their indexes and the explain plan for the query. Some rough ideas/guesses:

check that TBL2.id has an index
consider an index on TBL2 (id,REF2)
check that TBL1.REF1 has an index
consider an index on TBL1 (REF1,id)

However, before considering the above, make sure that the query you're optimising is actually the one your application will be using. It's suspicious that you have the literal value 286 hardcoded in your query - perhaps this should be a bind variable instead.

P.S. due to your WHERE clause on b.id, this is not an outer join, so the left keyword is redundant.

Best way to write Oracle Select Statement with rownum = 1 and order by

1 Answers1