Oracle函数使索引列失效的解决办法
导读：收集整理的这篇文章主要介绍了Oracle函数使索引列失效的解决办法，觉得挺不错的，现在分享给大家，也给大家做个参考。 在索引列上使用函数使得索引失效的是常见的索引失效原因之一，因此尽可能...
收集整理的这篇文章主要介绍了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 - Production2.原始语句与其执行计划
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 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函数使索引列失效的解决办法，希望对大家有所帮助。在此也非常感谢大家对网站的支持！
您可能感兴趣的文章:- 详细整理Oracle中常用函数
- Oracle分组函数之ROLLUP的基本用法
- 如何利用Oracle命令解决函数运行错误
- Oracle数据库分析函数用法
声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！
若转载请注明出处： Oracle函数使索引列失效的解决办法
本文地址： https://pptw.com/jishu/631787.html