时间戳

首页 > 解决方案 > 除了索引之外，如何优化 sql 代码？

sql - 除了索引之外，如何优化 sql 代码？

问题描述

我写了一个如下的sql代码。后来根据where条件添加了一些索引，但是性能还不够。sql有没有一般的优化规则或者下面的sql有什么建议？如何改进我的 sql 代码？

    SELECT COUNT(*) OVER() AS tot_count,
    PG.DESCR as partner, 
    THAF.APPDATA 
    FROM   transhist th, 
    trans_app thaf, 
    partnergroup pg, 
    partnergrpkey pgk 

    WHERE TH.CreateDt >= to_date('?startdate', 'yyyymmddhh24mi') 
    AND TH.CreateDt <= to_date('?enddate', 'yyyymmddhh24mi')+1/1440 


    AND TH.TRANSSET = '820' 
    AND THAF.TRANSHISTID = th.id 
    AND THAF.COLUMNID = 74 

    AND THAF.APPDATA LIKE '%|?acctNo|%' 

    AND TH.TRNSLTPARTNERKEY = PGK.DOCHISTPARTNERKEY 
    AND PG.ID = PGK.PARTNERGROUPID 

    AND pg.ID IN (?tpValues) 


    ORDER BY TH.ID;

索引如下：

CREATE INDEX TRANS_APP_ALL ON TRANS_APP(TRANSHISTID, COLUMNID, APPDATA);
CREATE INDEX TRANS_CREATEDT ON TRANSHIST(TRANSSET, CREATEDT, TRNSLTPARTNERKEY);

标签： sqloracleperformanceindexingquery-optimization

解决方案

首先，使用正确、明确、标准 join的语法编写查询：

select count(*) over () AS tot_count,
       PG.DESCR as partner, 
       THAF.APPDATA 
from transhist th join 
     partnergrpkey pgk 
     on TH.TRNSLTPARTNERKEY = PGK.DOCHISTPARTNERKEY join
     partnergroup pg
     on PG.ID = PGK.PARTNERGROUPID join
     trans_app thaf
     on THAF.TRANSHISTID = th.id
where TH.CreateDt >= to_date('?startdate', 'yyyymmddhh24mi') and
      TH.CreateDt <= to_date('?enddate', 'yyyymmddhh24mi')+1/1440 and
      TH.TRANSSET = '820' and
      THAF.COLUMNID = 74 and
      THAF.APPDATA LIKE '%|?acctNo|%' and
      pg.ID IN (?tpValues) 
order by TH.ID;

对于此查询，我希望以下索引会有所帮助：

  • transhist(TRANSSET, CreateDt, TRNSLTPARTNERKEY, id)
  • partnergrpkey(DOCHISTPARTNERKEY, PARTNERGROUPID)
  • partnergroup(id)
  • trans_app(TRANSHISTID, COLUMNID, APPDATA)

这些类似于您拥有的索引。您可能需要检查执行计划以查看索引是否实际被使用。

推荐阅读