SQL優(yōu)化案例-自定義函數(shù)索引（五）

SQL 文本如下，表本身很小，走全表掃描也很快，但因業(yè)務(wù)重要性，要求盡可能縮短查詢(xún)時(shí)間（為保證客戶隱私，已經(jīng)將注釋和文字部分去掉）：

SELECT MERCHCODE AS R_MERCHCODE,
   TRANDATE,
   TRANTIME,
   TRANTYPE AS TRANSTYPE,
   TRACENO,
   POSID AS R_POSID,
   ACCOUNT AS R_CARDNO,
   AMT,
   FEE,
   NVL(RESERVED1,'N') BORDERCARDBUSIFLAG,
   CASE WHEN I.BANCSRETFLAG='0000' THEN '1'
   WHEN  I.BANCSRETFLAG='9999' THEN'0'
   ELSE '2' END AS RETURNCODE
FROM IC_MERCHTRANSDETAIL_428 I
WHERE 
GETACCTNO(ACTSTLACCTNO)=GETACCTNO('14250000000454865') AND ROWNUM < 500;

執(zhí)行計(jì)劃如下：

可以看到謂詞信息是客戶號(hào)，可以確定此列選擇性非常高，非常適合建立索引。

CREATE INDEX IDX_GETACCTNO ON IC_MERCHTRANSDETAIL_428 (GETACCTNO(ACTSTLACCTNO)) PARALLEL 10 TABLESPACE REPT
                                                   *
ERROR at line 1:
ORA-30553: The function is not deterministic

確定函數(shù)本身不會(huì)受到不確定值的影響，創(chuàng)建函數(shù)索引。

加上 deterministic 并且取別名，查看函數(shù)創(chuàng)建語(yǔ)句：

CREATE OR REPLACE FUNCTION GETACCTNOCY (acct varchar2) return varchar2 DETERMINISTIC
is
tmpacct varchar2(40);
st_res  varchar2(40);  --st_res:=tmpacct
begin
tmpacct:='';
st_res :='';
IF (length(trim(acct))=16) THEN
BEGIN
 SELECT ACCOUNT
   INTO tmpacct
   FROM LINK_L
  WHERE LINK_L.CARD=LPAD(trim(acct),20,0)
    AND ISO_TYPE='1'
    AND CATEGORY='0';
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      tmpacct:=TRIM(ACCT);
END;
END IF;
IF(length(trim(acct))>17) THEN
   BEGIN
     SELECT zh
       INTO tmpacct
       FROM load_zhmap
      WHERE jzh=trim(acct);
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       tmpacct:='';
   END;
END IF;
 IF(length(trim(acct))=17) THEN
   tmpacct:=substr(acct,1,16);
  END IF;
st_res:=tmpacct;
return st_res;
EXCEPTION
WHEN OTHERS THEN
return '';
END;

創(chuàng)建索引：

CREATE INDEX IDX_GETACCTNO ON IC_MERCHTRANSDETAIL_428 (GETACCTNOCY(ACTSTLACCTNO)) TABLESPACE TBSIDX;

創(chuàng)建索引后的執(zhí)行計(jì)劃如下：

案例較為簡(jiǎn)單，希望可以幫助到大家。

|  作者簡(jiǎn)介

姚崇·沃趣科技高級(jí)數(shù)據(jù)庫(kù)技術(shù)專(zhuān)家

熟悉Oracle數(shù)據(jù)庫(kù)內(nèi)部機(jī)制，豐富的數(shù)據(jù)庫(kù)及RAC集群層故障診斷、性能調(diào)優(yōu)、OWI、數(shù)據(jù)庫(kù)備份恢復(fù)及遷移經(jīng)驗(yàn)。