深入理解Oracle索引(18)：函数索引的陷阱以及如何避免索引被污染

最新推荐文章于 2021-04-06 21:50:39 发布

haiross

最新推荐文章于 2021-04-06 21:50:39 发布

阅读量1.2k

点赞数

分类专栏： Oracle数据库基本管理 oracle疑问集 Oracle索引文章标签： sqlplus工具及其命令包函数索引

Oracle数据库基本管理同时被 3 个专栏收录

337 篇文章

订阅专栏

oracle疑问集

81 篇文章

订阅专栏

Oracle索引

31 篇文章

订阅专栏

本文揭示了Oracle函数索引的潜在陷阱，并通过实例演示了如何重建索引来避免索引污染。此外，提供了两条避免索引被污染的实用建议。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

深入理解Oracle索引(18)：函数索引的陷阱以及如何避免索引被污染

分类： Oracle 基础管理 2013-06-06 18:14 448人阅读评论(2) 收藏举报

㈠函数索引的陷阱

使用函数索引一定要注意在函数代码变更后重建函数索引、否则、Oracle将返回错误结果但不给提示

测试如下：

[plain] view plain copy print ?

<SPAN style="BACKGROUND-COLOR: rgb(102,102,102)">hr@ORCL> drop table t purge;
Table dropped.
hr@ORCL> create table t (x number,y varchar2(30));
Table created.
hr@ORCL> insert into t select rownum,rownum||'a' from dual connect by rownum<1000;
999 rows created.
hr@ORCL> ed
Wrote file afiedt.buf
1 create or replace function f_david(p_value varchar2) return varchar2
2 deterministic is
3 begin
4 return p_value;
5* end;
6
7 /
Function created.
hr@ORCL> create index idx_f_david_t on t (f_david(y));
Index created.
hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'T',estimate_percent=>100,cascade=>TRUE,no_invalidate=>false);
PL/SQL procedure successfully completed.
hr@ORCL> select * from t where f_david(y)='8a';
X Y
---------- ------------------------------
8 8a
hr@ORCL> ed //ed是什么splplus命令？
Wrote file afiedt.buf
1 create or replace function f_david(p_value varchar2) return varchar2
2 deterministic is
3 begin
4 return p_value||'b';
5* end;
hr@ORCL> /
Function created.
/* 此时的函数 f_david 已经不是我们所认识的那个了、但是查询依然如故！！！*/
hr@ORCL> select * from t where f_david(y)='8a';
X Y
---------- ------------------------------
8 8a
/* 索引重建查询没有记录、这才是我们要的正确结果*/
hr@ORCL> drop index idx_f_david_t;
Index dropped.
hr@ORCL> create index idx_f_david_t on t (f_david(y));
Index created.
hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'T',estimate_percent=>100,cascade=>TRUE,no_invalidate=>false);
PL/SQL procedure successfully completed.
hr@ORCL> select * from t where f_david(y)='8a';
no rows selected</SPAN>

hr@ORCL> drop table t purge;

Table dropped.

hr@ORCL> create table t (x number,y varchar2(30));

Table created.

hr@ORCL> insert into t select rownum,rownum||'a' from dual connect by rownum<1000;

999 rows created.

hr@ORCL> ed
Wrote file afiedt.buf

  1  create or replace function f_david(p_value varchar2) return varchar2
  2  deterministic is
  3  begin
  4  return p_value;
  5* end;
  6  
  7  /

Function created.

hr@ORCL> create index idx_f_david_t on t (f_david(y));

Index created.

hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'T',estimate_percent=>100,cascade=>TRUE,no_invalidate=>false);  //这句话什么意思？

PL/SQL procedure successfully completed.

hr@ORCL> select * from t where f_david(y)='8a';

         X Y
---------- ------------------------------
         8 8a

hr@ORCL> ed
Wrote file afiedt.buf

  1  create or replace function f_david(p_value varchar2) return varchar2
  2  deterministic is
  3  begin
  4  return p_value||'b';
  5* end;
hr@ORCL> /

Function created.

/* 此时的函数 f_david 已经不是我们所认识的那个了、但是查询依然如故！！！*/
hr@ORCL> select * from t where f_david(y)='8a';

         X Y
---------- ------------------------------
         8 8a


/* 索引重建查询没有记录、这才是我们要的正确结果*/
hr@ORCL> drop index idx_f_david_t;

Index dropped.

hr@ORCL> create index idx_f_david_t on t (f_david(y));

Index created.

hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'T',estimate_percent=>100,cascade=>TRUE,no_invalidate=>false);

PL/SQL procedure successfully completed.

hr@ORCL> select * from t where f_david(y)='8a';

no rows selected

㈡避免索引被污染

这里给出 2 条意见、

① 不要在字段前增加函数

如：
to_char(start_time,'yyyy.mm.dd') between '2013.06.06' and '2013.06.10'
和
start_time between to_date('2013.06.06','yyyy.mm.dd') and to_date('2013.06.10','yyyy.mm.dd')

任何时候都应该是第二种！！！

② 不要把字段嵌入到表达式中

如：
start_time + 7 < sysdate;
和
start_time < sysdate - 7

By David Lin
2013-06-06
Good Luck