Oracle函数使索引列失效的解决办法
作者:Leshami 发布时间:2024-01-15 16:52:08
在索引列上使用函数使得索引失效的是常见的索引失效原因之一,因此尽可能的避免在索引列上使用函数。尽管可以使用基于函数的索引来解决索引失效的问题,但如此一来带来的比如磁盘空间的占用以及列上过多的索引导致DML性能的下降。本文描述的是一个索引列上使用函数使其失效的案例。
一、数据版本与原始语句及相关信息
1.版本信息
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
2.原始语句与其执行计划
SQL> set autotrace traceonly exp;
SELECT acc_num,
curr_cd,
DECODE('20110728',
(SELECT TO_CHAR(LAST_DAY(TO_DATE('20110728', 'YYYYMMDD')),
'YYYYMMDD')
FROM DUAL),
0,
adj_credit_int_lv1_amt + adj_credit_int_lv2_amt -
adj_debit_int_lv1_amt - adj_debit_int_lv2_amt) AS interest
FROM acc_pos_int_tbl ACC_POS_INT_TBL1
WHERE SUBSTR(business_date, 1, 6) = SUBSTR('20110728', 1, 6)
AND business_date <= '20110728';
Execution Plan
----------------------------------------------------------
Plan hash value: 3114115399
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 336K| 12M| 96399 (1)| 00:19:17 |
| 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| ACC_POS_INT_TBL | 336K| 12M| 96399 (1)| 00:19:17 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SUBSTR("BUSINESS_DATE",1,6)='201107' AND
"BUSINESS_DATE"<='20110728')
从执行计划可以看出,SQL语句使用了全表扫描,而where 子句中只有唯一的一列business_date
3.表上的索引信息
SQL> set autotrace off;
SQL> set linesize 190
SQL> @Idx_Info
Enter value for owner: goex_admin
old 10: AND owner = upper('&owner')
new 10: AND owner = upper('goex_admin')
Enter value for table_name: ACC_POS_INT_TBL
old 11: AND a.table_name = upper('&table_name')
new 11: AND a.table_name = upper('ACC_POS_INT_TBL')
TABLE_NAME INDEX_NAME COL_NAM CL_POS STATUS IDX_TYP DSCD
------------------ ------------------------ -------------------- ------ -------- --------------- ----
ACC_POS_INT_TBL ACC_POS_INT_10DIG_IDX SYS_NC00032$ 1 VALID FUNCTION-BASED ASC
NORMAL
ACC_POS_INT_TBL ACC_POS_INT_10DIG_IDX BUSINESS_DATE 2 VALID FUNCTION-BASED ASC
NORMAL
ACC_POS_INT_TBL ACC_POS_INT_10DIG_IDX CURR_CD 3 VALID FUNCTION-BASED ASC
NORMAL
ACC_POS_INT_TBL PK_ACC_POS_INT_TBL ACC_NUM 1 VALID NORMAL ASC
ACC_POS_INT_TBL PK_ACC_POS_INT_TBL BUSINESS_DATE 2 VALID NORMAL ASC
从索引的情况上来看有一个基于主键的索引包含了BUSINESS_DATE列,而查询语句并没有走索引而是选择的全表扫描,而且预估所返回的行Rows与bytes也是大的惊人,cost的值96399,接近10W。
二、分析与改造SQL语句
1.原始的SQL语句分析
SQL语句中where子句的business_date列实现对记录过滤
business_date <= '20110728'条件不会限制索引的使用
SUBSTR(business_date, 1, 6) = SUBSTR('20110728', 1, 6)使用了SUBSTR函数,限制了优化器选择索引
基于business_date列来建立索引函数,从已存在的索引来看,必要性不大
2.改造SQL语句
SUBSTR(business_date, 1, 6) = SUBSTR('20110728', 1, 6)的实质是等于当月,即限制返回的行为从2011.7.1日至2011.7.28
因此其返回的记录大于等于2011.7.1,且小于2011.7.28
做如下改造
business_date >=to_char(last_day(add_months(to_date('20110728','yyyymmdd'),-1)) + 1,'yyyymmdd')
3.改造后的SQL语句
SELECT acc_num,
curr_cd,
DECODE('20110728',
(SELECT TO_CHAR(LAST_DAY(TO_DATE('20110728', 'YYYYMMDD')),
'YYYYMMDD')
FROM DUAL),
0,
adj_credit_int_lv1_amt + adj_credit_int_lv2_amt -
adj_debit_int_lv1_amt - adj_debit_int_lv2_amt) AS interest
FROM acc_pos_int_tbl ACC_POS_INT_TBL1
WHERE business_date >=
to_char(last_day(add_months(to_date('20110728', 'yyyymmdd'), -1)) + 1,
'yyyymmdd')
AND business_date <= '20110728';
4.改造后的执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 66267922
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1065K| 39M| 75043 (1)| 00:15:01 |
| 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| ACC_POS_INT_TBL | 1065K| 39M| 75043 (1)| 00:15:01 |
|* 3 | INDEX SKIP SCAN | PK_ACC_POS_INT_TBL | 33730 | | 41180 (1)| 00:08:15 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("BUSINESS_DATE">='20110701' AND "BUSINESS_DATE"<='20110728')
filter("BUSINESS_DATE">='20110701' AND "BUSINESS_DATE"<='20110728')
改造后可以看到SQL语句的执行计划已经由原来的全表扫描改为执行INDEX SKIP SCAN,但其cost也并没有降低多少
三、进一步分析
1.表的相关信息
SQL> @Tab_Stat
Enter value for input_table_name: ACC_POS_INT_TBL
old 11: WHERE table_name = upper('&input_table_name')
new 11: WHERE table_name = upper('ACC_POS_INT_TBL')
Enter value for input_owner: goex_admin
old 12: AND owner = upper('&input_owner')
new 12: AND owner = upper('goex_admin')
NUM_ROWS BLKS EM_BLKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_ROWS_PER_BLOCK LST_ANLY STA
---------- ---------- ---------- ---------- ---------- ----------- ------------------ --------- ---
33659947 437206 1322 855 0 99 77 27-SEP-11 NO
2.索引的相关信息
SQL> @Idx_Stat
Enter value for input_table_name: ACC_POS_INT_TBL
old 11: WHERE table_name = upper('&input_table_name')
new 11: WHERE table_name = upper('ACC_POS_INT_TBL')
Enter value for input_owner: goex_admin
old 12: AND owner = upper('&input_owner')
new 12: AND owner = upper('goex_admin')
BLEV IDX_NAME LF_BLKS DST_KEYS NUM_ROWS LF_PER_KEY DAT_BLK_PER_KEY CLUS_FCT LST_ANLY
---- ------------------------------ ---------- ---------- ---------- ---------- --------------- ---------- ---------
3 PK_ACC_POS_INT_TBL 155658 33777720 33777720 1 1 33777447 27-SEP-11
3 ACC_POS_INT_10DIG_IDX 160247 32850596 32850596 1 1 32763921 27-SEP-11
3.尝试在BUSINESS_DATE列上创建索引
SQL> create index I_ACC_POS_INT_TBL_BS_DT on ACC_POS_INT_TBL(BUSINESS_DATE) tablespace tbs_tmp nologging;
Index created.
SQL> @Idx_Stat
Enter value for input_table_name: ACC_POS_INT_TBL
old 11: WHERE table_name = upper('&input_table_name')
new 11: WHERE table_name = upper('ACC_POS_INT_TBL')
Enter value for input_owner: goex_admin
old 12: AND owner = upper('&input_owner')
new 12: AND owner = upper('goex_admin')
BLEV IDX_NAME LF_BLKS DST_KEYS NUM_ROWS LF_PER_KEY DAT_BLK_PER_KEY CLUS_FCT LST_ANLY
---- ------------------------------ ---------- ---------- ---------- ---------- --------------- ---------- ---------
2 I_ACC_POS_INT_TBL_BS_DT 93761 908 33659855 103 506 460007 30-SEP-11
3 PK_ACC_POS_INT_TBL 155658 33777720 33777720 1 1 33777447 27-SEP-11
3 ACC_POS_INT_10DIG_IDX 160247 32850596 32850596 1 1 32763921 27-SEP-11
建立索引后聚簇因子较小,差不多接近表上块的数量
4.使用新创建索引后的执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 2183566226
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1065K| 39M| 17586 (1)| 00:03:32 |
| 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| ACC_POS_INT_TBL | 1065K| 39M| 17586 (1)| 00:03:32 |
|* 3 | INDEX RANGE SCAN | I_ACC_POS_INT_TBL_BS_DT | 1065K| | 2984 (1)| 00:00:36 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("BUSINESS_DATE">='20110701' AND "BUSINESS_DATE"<='20110728')
从上面的执行计划看出,SQL语句已经选择了新建的索引尽管返回的rows,bytes没有明显的变化,但cost已经少了近7倍。
以上所述是小编给大家介绍的Oracle函数使索引列失效的解决办法网站的支持!
来源:https://blog.csdn.net/leshami/article/details/6851973
猜你喜欢
- PHP异步调用实现方式 浏览器和服务器之间只一种面向无连接的HTTP协议进行通讯的,面向无连接的程序的特点是客户端请求服务端,服务端根据请求
- 在程序设计中,鸭子类型(英语:duck typing)是动态类型的一种风格。在这种风格中,一个对象有效的语义,不是由继承自特定的类或实现特定
- 刚开始接触Python,首先要解决的就是Python开发环境的搭建。目前比较好用的Python开发工具是PyCharm,他有社区办和专业版两
- 代码如下:using System; using System.Data; using System.Configuration
- 导语:目前点评“2008年10佳改版网站”也许为时尚早,但2008年毕竟已经过去了9个多月,周四又同时有Twitter和FriendFeed
- 从字节码角度看描述器在前面的内容当中我们已经详细分析了描述器的使用和其相关的应用,我们通常使用描述器都是将其作为类的一个类属性使用,而使用的
- 在odoo中,通过iframe嵌入 html,页面数据则通过controllers获取,使用jinja2模板传值渲染html页面分页内容,这
- 关于这个话题,我也看到网上有其他方法来解决。 不过从性能和代码量上来看,我有更好的办法。 在这里和大家 分享。 原理其实很简单, 知识1、长
- python 封装tokenimport datetimeclass MyJwt:def __init__(self): &n
- 在Flash播放器运行时,将不同来源的资源划分到独立的沙箱(sandbox)内,不同沙箱之间不能彼此操作数据(除非目标沙箱做过一些设置,授权
- SocketServer创建一个网络服务框架。它定义了类来处理TCP,UDP, UNIX streams 和UNIX datagrams上的
- 本文先比较range与arange的异同点,再详细介绍各自的用法,然后列举了几个简单的示例,最后对xrange进行了简单的说明。1. ran
- 我就废话不多说了,直接上代码吧!from numpy import *import numpy as npimport cv2, os, m
- django路由和视图要了解django是如何运行的,首先要了解路由和视图两个概念,然后我们在项目中添加一些简单的路由和视图路由和视图的概念
- 一、为什么要搭建爬虫代理池在众多的网站防爬措施中,有一种是根据ip的访问频率进行限制,即在某一时间段内,当某个ip的访问次数达到一定的阀值时
- super()函数可以用于继承父类的方法,语法如下:super(type[, object-or-type])虽然super()函数的使用比
- 前言:看本教程,必须先仔细看前言的内容,否则会进入误区!最近在做个性休闲服装内网站的设计课程,过程中发现,个性元素的应用成为最难的问题,第一
- 1 简介今天我要给大家介绍的这个Python库prettymaps非常的有趣,基于它,我们只需要简单的代码就可以对地球上给定坐标和范围的任意
- 问题:m = re.findall('[0-9]*4[0-9]*', '[4]') 可以匹配到4.m = r
- 数组排序排序是指将元素按有序顺序排列。有序序列是拥有与元素相对应的顺序的任何序列,例如数字或字母、升序或降序。NumPy ndarray 对