优化子选择语句以减少抓取时间

Question

我有一个查询

select * from 
(select a.*, /*+ FIRST_ROWS(10) */ rownum rnum 
 from (SELECT AL.AL_UID, AL.TP_ID, AL.EVENTDATE, TP.NAME, AL.EVENT, AL.FILE_NAME,
              AL.NOTES, AL.XU_NAME, AL.STATUS, AL.LOG_SOURCE, rownum
       FROM AUDITLOG AL,
            TRADINGPARTNER TP 
       WHERE  ( AL.LOG_SOURCE = 'File Deployment' )  AND TP.ID = AL.TP_ID 
       ORDER BY AL.EVENTDATE desc, AL.AL_UID desc
      ) a
 where rownum < 101
) 
where rnum > 0;

这将花费大约一分钟来获取结果。请让我知道如何优化它，以减少抓取时间。这里可以使用连接吗?

Answer 1

关于(LOG_SOURCE, EVENTDATE desc, AL_UID desc)的索引值得一看——desc子句可能不是必需的，如果可能的话尽量不要使用它们。

我将first_rows提示放置在与谓词和命令相同的查询块中。

Answer 2

窗口函数通常比order by更快。你可以试试:

select *
from (SELECT AL.AL_UID, AL.TP_ID, AL.EVENTDATE, TP.NAME, AL.EVENT, AL.FILE_NAME,
             AL.NOTES, AL.XU_NAME, AL.STATUS, AL.LOG_SOURCE,
             ROW_NUMBER() over (order by AL.EVENTDATE desc, AL.AL_UID desc) as seqnum
      FROM AUDITLOG AL join
           TRADINGPARTNER TP 
           on TP.ID = AL.TP_ID
      WHERE AL.LOG_SOURCE = 'File Deployment'
     ) t
where seqnum between 1 and 100;

有了这个，你实际上可以这样写:

select al.*, TP.name
from (SELECT AL.AL_UID, AL.TP_ID, AL.EVENTDATE, AL.EVENT, AL.FILE_NAME,
             AL.NOTES, AL.XU_NAME, AL.STATUS, AL.LOG_SOURCE,
             ROW_NUMBER() over (order by AL.EVENTDATE desc, AL.AL_UID desc) as seqnum
      FROM AUDITLOG AL
      WHERE AL.LOG_SOURCE = 'File Deployment'
     ) al join
     TradingPartner tp
     on TP.ID = AL.TP_ID 
where seqnum between 1 and 100;

很明显，AuditLog(Eventdate, AL_UID)上的索引将大大提高性能。