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

绝地灬酷狼 2022-05-18 04:16 168阅读 0赞

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;

执行计划如下：

![70][]

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

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

![70 1][]

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

*加上*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;

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

![70 2][]

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

### **|  作者简介** ###

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

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

[70]: /images/20220518/e79aee1f95e34377a1eee5f80dd4d6f4.png
[70 1]: /images/20220518/f090fedad869474a84875ef1eaed45eb.png
[70 2]: /images/20220518/2a0447414b1844968d34157456807a90.png