需要Oracle查询优化(运行时间= 2.37秒)

Question

执行以下查询需要花费时间( 2.37秒)。我在FORWARD_INFO和G_PROFILE表中分别有大约5,000，00条记录和2,000，00条记录。

我需要优化这个查询。应该需要大约20秒。你能建议一下。

使用Oracle10g DB。

SELECT S_NUM, STATUS, TRANSLATION_NUM, ANN, NOTIFY_ANN, ID, NAME, 
  PROFILE_ID, ANN_ID, RES_PROFILE_ID, R_ANN, RES_ANN_ID 
FROM(
  SELECT CSC.S_NUM,CSC.STATUS, CSC.TRANSLATION_NUM, CSC.ANN, CSC.NOTIFY_ANN, CSC.ID, CGP.NAME, CGP.PROFILE_ID, CGP.ANN_ID, CGP.RES_PROFILE_ID, CGP.R_ANN, CGP.RES_ANN_ID, 
     RANK() OVER(ORDER BY LENGTH(CSC.S_NUM) DESC) RANKING 
  FROM 
    FORWARD_INFO CSC, G_PROFILE CGP 
  WHERE '0120913005' LIKE CSC.S_NUM||'%' AND 
         CGP.NAME LIKE 'ase' ||'%' AND 
         CSC.ACCOUNT_NUMBER=10 AND CSC.ID= CGP.ID AND 
         CSC.ACTIVE = 1 AND CGP.ACTIVE = 1) 
WHERE RANKING = 1 AND 
         ROWNUM = 1;

Answer 1

在稍微重新格式化之后

SELECT S_NUM, STATUS, TRANSLATION_NUM, 
    ANN, NOTIFY_ANN, ID, NAME, 
    PROFILE_ID, ANN_ID, RES_PROFILE_ID , R_ANN,
    RES_ANN_ID 
FROM
    (SELECT CSC.S_NUM,CSC.STATUS, CSC.TRANSLATION_NUM, CSC.ANN, CSC.NOTIFY_ANN, CSC.ID, 
        CGP.NAME, CGP.PROFILE_ID, CGP.ANN_ID, CGP.RES_PROFILE_ID , CGP.R_ANN, CGP.RES_ANN_ID, 
        RANK() OVER (ORDER BY LENGTH(CSC.S_NUM) DESC) RANKING 
    FROM FORWARD_INFO CSC
        JOIN G_PROFILE CGP ON CSC.ID = CGP.ID 
    WHERE '0120913005' LIKE CSC.S_NUM||'%' 
        AND CGP.NAME LIKE 'ase' ||'%' 
        AND CSC.ACCOUNT_NUMBER = 10          
        AND CSC.ACTIVE = 1 
        AND CGP.ACTIVE = 1) 
WHERE RANKING = 1 
    AND ROWNUM = 1;

'0120913005' LIKE CSC.S_NUM||'%' AND CGP.NAME LIKE 'ase' ||'%' 

您可以创建基于函数的索引。

CREATE INDEX NAME_LIKE_ASE ON G_PROFILE (SUBSTR(NAME, 1, 3));
CREATE INDEX S_NUM_LIKE ON G_PROFILE (S_NUM||'%');

你的WHERE条件会是什么

'0120913005' LIKE CSC.S_NUM||'%' AND SUBSTR(NAME, 1, 3) = 'ase'

然后，您应该在FORWARD_INFO.ID和G_PROFILE.ID上有索引。

Answer 2

在WHERE子句中

WHERE '0120913005' LIKE 
  CSC.S_NUM||'%' AND CGP.NAME LIKE 'ase' ||'%' AND 
  CSC.ACCOUNT_NUMBER=10 AND CSC.ID= CGP.ID AND 
  CSC.ACTIVE = 1 AND CGP.ACTIVE = 1

更改如下条件的顺序

WHERE CSC.ACTIVE = 1 AND CGP.ACTIVE = 1  AND 
  CSC.ACCOUNT_NUMBER=10 AND CSC.ID= CGP.ID  AND 
 '0120913005' LIKE 
  CSC.S_NUM||'%' AND CGP.NAME LIKE 'ase' ||'%'

字符串比较需要很大的成本