如何提高带有where子句的视图的性能，该子句使用带有max()的子查询

Question

我有以下视图，用于检查每个代理商每年的销售额：

SELECT C.SUBSIDIARY_ID AS SUBSIDIARY_ID,
SUM(CI.CURRENT_VALUE) AS TOTAL_SALES,
C.AGENT_USERNAME AS AGENT,
TO_CHAR(C.SIGN_DATE, 'YYYY') AS SIGN_YEAR
FROM CONTRACT_INFO CI
INNER JOIN CONTRACT C ON C.ID = CI.CONTRACT_ID
WHERE CI.UPDATE_DATE = (SELECT MAX(CI2.UPDATE_DATE) 
FROM CONTRACT_INFO CI2 
WHERE CI.CONTRACT_ID = CI2.CONTRACT_ID)
GROUP BY SUBS.NAME, C.SUBSIDIARY_ID, C.AGENT_USERNAME, TO_CHAR(C.SIGN_DATE, 'YYYY');

此视图由应用程序查询，该应用程序可能通过SIGN_YEAR、SUBSIDARY_ID和/或AGENT进行筛选

CONTRACT_INFO表不断更新新的重新协商值和其他内容，因此我们需要最大update_date来获取最新值。

当我们通过SIGN_YEAR和/或SUBSIDARY_ID进行查询时，一切正常。当AGENT查询时，执行查询需要5分钟以上。

这些表中的每一个都包含超过30列，SIGN_YEAR、SUBSIDARY_ID和AGENT都在它们的表中进行了索引，并且我不能使用ID代替代理的用户名，因为原因(代理的用户名应为'john.doe'(。

AGENT查询时，如何提高性能？

Answer 1

对于您编写的查询，您需要contact_info(contract_id, update_date)上的索引

但是，还有其他方法可以编写查询。使用相同的索引，您可以尝试：

SELECT C.SUBSIDIARY_ID AS SUBSIDIARY_ID,
SUM(CI.CURRENT_VALUE) AS TOTAL_SALES,
C.AGENT_USERNAME AS AGENT,
TO_CHAR(C.SIGN_DATE, 'YYYY') AS SIGN_YEAR
FROM CONTRACT C JOIN
(SELECT CI.*,
ROW_NUMBER() OVER (PARTITION BY CONTRACT_ID ORDER BY UPDATE_DATE DESC) as SEQNUM
FROM CONTRACT_INFO CI
) CI
ON C.ID = CI.CONTRACT_ID
WHERE seqnum = 1
GROUP BY SUBS.NAME, C.SUBSIDIARY_ID, C.AGENT_USERNAME, TO_CHAR(C.SIGN_DATE, 'YYYY');