Oracle函数使索引列失效的解决办法
在索引列上使用函数使得索引失效的是常见的索引失效原因之一，因此尽可能的避免在索引列上使用函数。尽管可以使用基于函数的索引来解决索引失效的问题，但如此一来带来的比如磁盘空间的占用以及列上过多的索引导致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')
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函数使索引列失效的解决办法，希望对大家有所帮助。在此也非常感谢大家对我们网站的支持！
相关推荐
-
oracle分区索引的失效和重建代码示例
上一篇文章中我们了解了oracle普通表转化为分区表的方法的相关内容,接下来的这篇文章,我们将探讨oracle分区索引的失效和重建问题,提供了相关代码示例供大家参考,具体如下. --创建测试表 SQL> create table t as select object_id,object_name from dba_objects; 表已创建. SQL> select min(object_id),max(object_id) from t; MIN(OBJECT_ID) MAX(OBJECT_
-
oracle数据库索引失效
今天一个同事突然问我索引为什么失效.说实在的,失效的原因有多种: 但是如果是同样的sql如果在之前能够使用到索引,那么现在使用不到索引,以下几种主要情况: 1. 随着表的增长,where条件出来的数据太多,大于15%,使得索引失效(会导致CBO计算走索引花费大于走全表) 2. 统计信息失效 需要重新搜集统计信息 3. 索引本身失效 需要重建索引 下面是一些不会使用到索引的原因 索引失效 1) 没有查询条件,或者查询条件没有建立索引 2) 在查询条件上没有使用引导列 3) 查询
-
Oracle函数使索引列失效的解决办法
在索引列上使用函数使得索引失效的是常见的索引失效原因之一,因此尽可能的避免在索引列上使用函数.尽管可以使用基于函数的索引来解决索引失效的问题,但如此一来带来的比如磁盘空间的占用以及列上过多的索引导致DML性能的下降.本文描述的是一个索引列上使用函数使其失效的案例. 一.数据版本与原始语句及相关信息 1.版本信息 SQL> select * from v$version; BANNER --------------------------------------------------------
-
Oracle表空间不足的两种解决办法
首先查询表空间的大小以及文件路径地址 select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name; 解决方案一 //修改表空间大小(32000可改为想要的数值) alter database datafile '要修改的数据文件地址' resize 32000m; 解决方案二 //新增数据文件 ALTER
-
Oracle 8i字符集乱码问题析及其解决办法
正在看的ORACLE教程是:Oracle 8i字符集乱码问题析及其解决办法.一.问题描述 SQL Plus WorkSheet是一个窗口图形界面的SQL语句编辑器,对于那些喜欢窗口界面而不喜欢字符界面的用户,该工具相对SQL/PLUS受到了很大的欢迎.但从Oracle 8i以后,如果安装Oracle 8i时选取的是别于英语的字符集,对于我们中国,通常会选取简体中文字符集(ZHS16GBK),安装成功后,运行SQL Plus WorkSheet程序,会出现所有的中文显示以及查询结果均为乱码的情况.
-
Oracle用户被锁的原因及解决办法
在登陆时被告知test用户被锁 1.用dba角色的用户登陆,进行解锁,先设置具体时间格式,以便查看具体时间 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; Session altered. 2.查看具体的被锁时间 SQL> select username,lock_date from dba_users where username='TEST'; USERNAME LOCK_DATE TEST 2009-03-1
-
js 开发之autocomplete="off"在chrom中失效的解决办法
js 开发之autocomplete="off"在chrom中失效的解决办法 问题:在表单的输入框中,有时候我们并不希望点击输入框时,会出现提示信息.这时,在输入框中添加属性:autocomplete="off",一般能达到目的.而在chrom里面就失效. 失效的原因是:浏览器会根据输入框的input的type属性为password的时候,自动将用户名和密码框填充. 解决方法:因为浏览器是否自动填充内容,根据type=password来判断的.此时先将作为密码的输入
-
js 开发之autocomplete="off"在chrom中失效的解决办法
js 开发之autocomplete="off"在chrom中失效的解决办法 问题:在表单的输入框中,有时候我们并不希望点击输入框时,会出现提示信息.这时,在输入框中添加属性:autocomplete="off",一般能达到目的.而在chrom里面就失效. 失效的原因是:浏览器会根据输入框的input的type属性为password的时候,自动将用户名和密码框填充. 解决方法:因为浏览器是否自动填充内容,根据type=password来判断的.此时先将作为密码的输入
-
MSSQL报错：参数数据类型 text 对于 replace 函数的参数 1 无效的解决办法
但是我们可以换一种方法解决这个问题.下面就来分析下解决办法. 对text或ntext类型的数据在查询中不能进行字符串操作.这时用得最多的是把text当作varchar(实际内容长度低于8000字节时)或把ntext当作nvarchar(实际内容长度低于4000字节时)来处理.但是这样处理也不是非常妥当毕竟如果text字段内容超过8000了岂不是要被截断或者忽略掉.最终找到了一种方法解决了"参数数据类型 text 对于 replace 函数的参数 1 无效"的问题.下面用一个例子来说明怎
-
PHP的fsockopen、pfsockopen函数被主机商禁用的解决办法
也许fsockopen.pfsockopen函数的确存在着安全的隐患,但是我们却已经无从考证,这都是IDC商说的,不管是什么原因吧,反正他们是把这两个函数禁用了,那么如何解决呢,下面是小编整理的方法,希望对用到的同学有一定的参考. 解决方法如下: 一.使用stream_socket_client()替代 服务器同时禁用了fsockopen.pfsockopen,那么用其他函数代替,如stream_socket_client().注意:stream_socket_client()和fsockope
-
IOS上iframe的滚动条失效的解决办法
问题描述: iframe设置了高度(例如500px).倘若iframe的内容足够长超出了iframe设定的高度时,在ipad等设备上.iframe内部html的滚动条不出现.并且活生生的从500px处截断,(类似overflow:hidden的效果)下面的内容不再显示. 问题重现: 结构: index.html : <style> #iframe{height:500px;} </style> <div id="content"> <ifram
-
Xcode 升级导致插件失效的解决办法
最近升级Xcode8 遇到了些问题,这里整理下,希望读者少走弯路,尽快解决问题. Xcode8不能使用插件了,此文作废... Xcode的各种插件极大地提高了开发效率,比如我常用的FuzzyAutocomplete(添加模糊匹配来提高Xcode代码自动补全功能,开发者无需遵循从头匹配的原则,只要记得方法里某个关键字即可进行匹配). VVDocument.KSImageNamed.Peckham(可以在任意位置通过快捷键添加#import 语句).XAlign(代码对齐),但是升级Xcode导致插