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

㈠ 函数索引的陷阱 使用函数索引一定要注意在函数代码变更后重建函数索引、否则、Oracle将返回错误结果但不给提示 测试如下： [plain] view plaincopyprint? SPANstyle=BACKGROUND-COLOR:rgb(102,102,102)hr@ORCLdroptabletpurge; Tabledropped. hr@ORCLcrea

㈠ 函数索引的陷阱

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

测试如下：

[plain]

view plaincopyprint?

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

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 本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源：php中文网