Query optimization [Oracle DBMS]

Question

I have a query that always must return a single row and its execution time is about 5s, so I need to optimize it:

SELECT * FROM RESPONSE res
INNER JOIN REQUEST req ON res.ID = req.ID
WHERE res.FIELD_47 = ? 
AND req.CREATE_DATE = ( SELECT max(req2.CREATE_DATE) FROM RESPONSE r2
INNER JOIN REQUEST req2 ON r2.ID = req2.ID
WHERE r2.FIELD_47 = ? );

All present columns have a index and my db is transactional. Please suggest me. Thank you!

Answer 1

Try this one:

SELECT 
   MAX(req.CREATE_DATE) KEEP (DENSE_RANK FIRST ORDER BY req.CREATE_DATE DESC) CREATE_DATE,
   MAX(req.column_x) KEEP (DENSE_RANK FIRST ORDER BY req.CREATE_DATE DESC) column_x,
   MAX(req.column_y) KEEP (DENSE_RANK FIRST ORDER BY req.CREATE_DATE DESC) column_y,
   ... some more columns, same as above
FROM RESPONSE res
    INNER JOIN REQUEST req ON res.ID = req.ID
WHERE res.FIELD_47 = ?;