文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

SQL优化案例-自定义函数索引(五)

2024-04-02 19:55

关注

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;

执行计划如下:

SQL优化案例-自定义函数索引(五)

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


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

SQL优化案例-自定义函数索引(五)

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

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

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

SQL优化案例-自定义函数索引(五)

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


|  作者简介

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

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

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯