oracle自定义函数索引陷阱

来自《剑破冰山——ORACLE开发艺术》的结论:

使用函数索引一定要注意在修改函数代码后重建函数索引,否则,Oracle将使用这个函数索引查询出错误结果且不提示任何错误,这是非常为限度地。

以下为验证:

--构造表数据
DROP TABLE t;
CREATE TABLE t(x NUMBER,y VARCHAR2(30));
INSERT INTO t SELECT ROWNUM,ROWNUM||'a' FROM dual CONNECT BY ROWNUM<100;
--建函数
CREATE OR REPLACE FUNCTION f_sing(p_value in VARCHAR2)RETURN VARCHAR2 
deterministic IS 
BEGIN 
  RETURN p_value;
END;
--建函数索引,并查询结果
CREATE INDEX idx_f_sing_y ON t(f_sing(y));
ANALYZE TABLE t compute STATISTICS FOR TABLE FOR ALL indexes FOR ALL indexed COLUMNS;
SELECT * FROM t WHERE f_sing(y)='8a';
X Y
8 8a
--修改函数代码
CREATE OR REPLACE FUNCTION f_sing(p_value in VARCHAR2)RETURN VARCHAR2 
deterministic IS 
BEGIN 
  RETURN p_value||'b';
END;
--重新查询结果,提示函数索引被禁用——书中的结论是修改函数代码后仍查到8 8a,此点与原书不符,版本问题?
SELECT * FROM t WHERE f_sing(y)='8a';
20 1 ORA-30554: 基于函数的索引TWGERP.IDX_F_SING_Y被禁用

--使用hints禁用索引后,查不到资料,正确。
SELECT /*+no_index(t)*/ * FROM t WHERE f_sing(y)='8a';
X Y

--重建索引后查询结果,查不到资料,正确。
DROP INDEX idx_f_sing_y;
CREATE INDEX idx_f_sing_y ON t(f_sing(y));
SELECT * FROM t WHERE f_sing(y)='8a';
X Y


 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值