mysql 自定义函数 索引_沃趣科技-文档-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 RETURNCODE

FROM IC_MERCHTRANSDETAIL_428 I

WHERE

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 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;

创建索引：

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

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

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

|  作者简介

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

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