CODEWITHSUNDEEP
sqloracle-database
user9923760
user9923760

Reputation: 656

Oracle - Can this query be optimized?

I want to get the last Date of a set of rows. What is more performant: Query1 or Query2:

Query1

select *
from(
select column_date
from table1 a join table2 b on a.column1=b.column1
where id= '1234'
order by column_date desc) c
where rownum=1

Query2

select column_date
from table1 a join table2 b on a.column1=b.column1
where id= '1234'
order by column_date desc

and take the first row in backend.

Or maybe is there another way to take the first row in Oracle? I know that normally subselects are bad performant. That's why I am trying to remove the subselect.

I tried that but I am not getting the result expected:

select column_date
from table1 a join table2 b on a.column1=b.column1
where id= '1234' and rownum=1
order by column_date desc

Upvotes: 0

Views: 62

Answers (1)

eaolson
eaolson

Reputation: 15090

First, you can't really optimize a query. Queries are always rewritten by the optimizer and may give very different results depending on how much data there is, indexes, etc. So if you have a query that is slow, you must look at the execution plan to see what's happening. And if you have a query that is not slow, you shouldn't be optimizing it.

There's nothing wrong with subselects, per se. As Wernfriend Domscheit suggests, this will give you the minimum column_date, which I assume resides in table2.

SELECT MIN( b.column_date ) 
  FROM table1 a 
 INNER JOIN table2 b on a.column1 = b.column1 
 WHERE a.id= '1234'

That is guaranteed to give you a single row. If you needed more than just the date field, this will select the rows with the minimum date:

 SELECT a.*, b.column_date
  FROM table1 a 
 INNER JOIN table2 b on a.column1 = b.column1 
 WHERE a.id= '1234'
   AND b.column_date = ( SELECT MIN( b2.column_date ) FROM table2 b2 )

But if your column_date is not unique, this may return multiple rows. If that's possible, you'll need something in the data to differentiate the rows to select. This is guaranteed to give you a single row:

SELECT * FROM (
     SELECT a.*, b.column_date
      FROM table1 a 
     INNER JOIN table2 b on a.column1 = b.column1 
     WHERE a.id= '1234'
       AND b.column_date = ( SELECT MIN( b2.column_date ) FROM table2 b2 )
     ORDER BY a.some_other_column 
)
WHERE ROWNUM = 1

In a recent enough version of Oracle, you can use FETCH FIRST 1 ROW ONLY instead of the ROWNUM query. I don't think it makes a difference.

Upvotes: 1

Related Questions