Reputation: 656
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
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