如何调优oracle 11g中的自连接查询

Question

Oracle &SQL对我来说是新的，我还在学习阶段。

你能帮我调一下下面的oracle查询吗?这个表包含约95条记录，使用此查询检索数据需要1小时。

欢迎您的建议/意见/帮助。

提前感谢。

    SELECT A.CNO   AS CNO,
       A.FNO AS FNO,
       A.CID AS CID,
       A.IID             AS IID
  FROM CAC_LKP A,
       (SELECT C_DATE, CNO, FNO
          FROM (SELECT MAX(CAC_LKP.C_DATE) AS C_DATE,
                       CAC_LKP.CNO AS CNO,
                       CAC_LKP.FNO AS FNO
                  FROM CAC_LKP
                 WHERE ACTIVE = 'Y'
                 GROUP BY CNO, FNO)) B
 WHERE A.C_DATE = B.C_DATE
   AND A.CNO = B.CNO
   AND A.FNO = B.FNO
   AND A.ACTIVE = 'Y'

主键定义为c_date,iid,active的组合。

Answer 1

您正在使用聚合来获取变量组的最近日期。然后，您将获得与这些日期匹配的行。

要优化查询，不要使用自连接，而是使用分析函数:

select cno, fno, cid, lid
from (select A.*,
             rank() over (partition by cno, fno order by c_date desc) as seqnum
      from cac_lkp A
      where A.active = 'Y'
     ) A
where seqnum = 1;

Answer 2

由于数据量大，删除不必要的子查询可以提高性能。确保在C_DATE、CNO、FNO和A.ACTIVE上都有索引。试试这个sql

SELECT A.CNO   AS CNO,
       A.FNO AS FNO,
       A.CID AS CID,
       A.IID             AS IID
  FROM CAC_LKP A,
       (SELECT MAX(CAC_LKP.C_DATE) AS C_DATE,
                       CAC_LKP.CNO AS CNO,
                       CAC_LKP.FNO AS FNO
                  FROM CAC_LKP
                 WHERE ACTIVE = 'Y'
                 GROUP BY CNO, FNO) B
 WHERE A.C_DATE = B.C_DATE
   AND A.CNO = B.CNO
   AND A.FNO = B.FNO
   AND A.ACTIVE = 'Y'