Oracle函数使索引列失效怎么办
发表于：2025-11-07 作者：千家信息网编辑
千家信息网最后更新 2025年11月07日，小编给大家分享一下Oracle函数使索引列失效怎么办，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！一、数据版本与原始语句及相关信息1.版本信息SQL> select * from v
千家信息网最后更新 2025年11月07日Oracle函数使索引列失效怎么办
小编给大家分享一下Oracle函数使索引列失效怎么办，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！
一、数据版本与原始语句及相关信息
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 NO2.索引的相关信息
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-113.尝试在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函数使索引列失效怎么办"有了一定的了解，如果想了解更多相关知识，欢迎关注行业资讯频道，感谢各位的阅读！
索引
语句
改造
信息
函数
原始
分析
选择
限制
怎么办
版本
篇文章
惊人
明显
必要
差不多
不大
因子
子句
完了
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
软件开发维护 招聘
娄底软件开发哪家专业
社保缴费端口怎么显示服务器异常
嘉定区专业性网络技术代理价格
云服务器为什么那么贵
对词汇软件开发人员的建议
6g网络技术研制
霍尼韦尔数据库管理系统
猪猪视频软件开发
网络安全中心简单的手抄报
深圳市盛云网络技术
锡山区加工软件开发生产过程
ioc在网络安全中代表什么意思
崇明区网络软件开发信息中心
交通银行软件开发中心 加班
路由器怎么总是连接服务器
人类的网络技术
网络安全建设技术方案
常用mpp架构数据库
javs写入数据库代码
查询所有数据库语句怎么写
中专毕业生数据库查无此人
网络安全创新新技术
我的世界神奇宝贝仙云服务器下载
北京停车系统软件开发定制
中华人民共和国网络安全法6
我国国产数据库的发展与应用综述
黎川县人才开发交流服务器中心
城固网络安全宣传周
网络安全卡纸报
