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、数据库备份恢复及迁移经验。

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏james大数据架构

你真的会玩SQL吗?玩爆你的数据报表之存储过程编写(下)

你真的会玩SQL吗?系列目录 你真的会玩SQL吗?之逻辑查询处理阶段 你真的会玩SQL吗?和平大使 内连接、外连接 你真的会玩SQL吗?三范式、数据完整性 你真...

1946
来自专栏python3

mysql-外键的三种关系

mysql> create table press(id int primary key auto_increment,name varchar(20));

1163
来自专栏企鹅号快讯

数据分析师必备的数据提取技能

数据分析师必备技能SQL 在数据分析的整个流程中,数据获取是不可或缺的一环,那么作为数据分析师,我们不仅仅需要了解如何获取二手数据,还必须掌握如何从数据库中获取...

29010
来自专栏一个爱吃西瓜的程序员

学习SQL【6】-复杂查询

到目前为止,我们学习了表的创建、查询和更新等数据库的基本操作方法。现在我们将会在这些基本方法的基础上,学习一些实际应用的方法。 一:视图 1:视图和表 表中存...

3429
来自专栏Grace development

老项目重构手记之用户系统

重构首先要注意几个点 – 重构后功能的可扩展性 – 业务互相依赖的复杂度 – 脱离本身的业务进行重构 – 重构后的代码可读性与可维护性 – 性能的提升...

1302
来自专栏后端技术探索

MySQL优化的奇技淫巧之STRAIGHT_JOIN

通过「SHOW FULL PROCESSLIST」语句很容易就能查到问题SQL,如下:

1151
来自专栏杨建荣的学习笔记

相差数十倍的SQL性能分析(r11笔记第98天)

今天处理开发同学提交的一个数据查询需求,看起来是一个很常规的SQL,但是有一点不同的是,他们提供了两份文件,一份是一个id列表,大概有3000多个id值,...

3498
来自专栏程序猿DD

一个不可思议的MySQL慢查分析与解决

前言 开发需要定期的删除表里一定时间以前的数据,SQL如下 mysql > delete from testtable WHERE biz_date <= '2...

3515
来自专栏数据和云

SQL优化:紧急情况下提高SQL性能竟是这样实现的!

作者 | 黄堋 ,多年一线 Oracle DBA 经验,长期服务电信、电网、医院、政府等行业客户。擅长数据库优化、数据库迁移升级、数据库故障处理。

936
来自专栏小怪聊职场

MySQL(八)|MySQL中In与Exists的区别(2)

1823

扫码关注云+社区

领取腾讯云代金券