在索引列上使用函数使得索引失效的是常见的索引失效原因之一，因此尽可能的避免在索引列上使用函数。尽管可以使用基于函数的索引来解决索引失效的问题，但如此一来带来的比如磁盘空间的占用以及列上过多的索引导致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倍。
以上所述是www.887551.com给大家介绍的oracle函数使索引列失效的解决办法，希望对大家有所帮助。在此也非常感谢大家对www.887551.com网站的支持！