查詢優化OracleDBM小號○r一個Cl和D乙米小號Oracle DBMS

我有一個查詢，它總是必須返回一行，它的執行時間大約是 5s，所以我需要優化它：

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 = ? );

所有現有列都有一個索引，我的數據庫是事務性的。請給我建議。謝謝！

試試這個：

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 = ?;

嗨，您必須看到此復選標記以獲得最佳查詢：您的查詢具有以下條件越多，候選人就越有可能在夜間執行：

Selecting from large tables (>1,000,000 records)
Cartesian Joins or CROSS JOINs
Looping statements
SELECT DISTINCT statements
Nested subqueries
Wildcard searches in long text or memo fields
Multiple schema queries

引用自：https://dba.stackexchange.com/questions/314499