SQL优化案例-自定义函数索引（五）

SQL 文本如下，表本身很小，走全表扫描也很快，但因业务重要性，要求尽可能缩短查询时间（为保证客户隐私，已经将注释和文字部分去掉）：

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 RETURNCODEFROM IC_MERCHTRANSDETAIL_428 IWHERE GETACCTNO(ACTSTLACCTNO)=GETACCTNO('14250000000454865') AND ROWNUM < 500;

执行计划如下：

可以看到谓词信息是客户号，可以确定此列选择性非常高，非常适合建立索引。

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

确定函数本身不会受到不确定值的影响，创建函数索引。

加上 deterministic 并且取别名，查看函数创建语句：

CREATE OR REPLACE FUNCTION GETACCTNOCY (acct varchar2) return varchar2 DETERMINISTICistmpacct varchar2(40);st_res  varchar2(40);  --st_res:=tmpacctbegintmpacct:='';st_res :='';IF (length(trim(acct))=16) THENBEGIN 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;EXCEPTIONWHEN OTHERS THENreturn '';END;

创建索引：

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

创建索引后的执行计划如下：

案例较为简单，希望可以帮助到大家。

| 作者简介

姚崇·沃趣科技高级数据库技术专家

熟悉Oracle数据库内部机制，丰富的数据库及RAC集群层故障诊断、性能调优、OWI、数据库备份恢复及迁移经验。