Oracle custom function index trap

The oracle custom function index trap comes from the tip of the iceberg-ORACLE development art conclusion: You must re-build the function index after modifying the function code. Otherwise, oracle will use this function index to query the error results without prompting any errors, which is very limited. [SQL] -- construct TABLE data [SQL] DROP TABLE t; CREATE TABLE t (x NUMBER, y VARCHAR2 (30); INSERT INTO t SELECT ROWNUM, ROWNUM | 'A' FROM dual connect by rownum <100;
[SQL] -- CREATE OR REPLACE FUNCTION f_sing (p_value in VARCHAR2) RETURN VARCHAR2 deterministic IS www.2cto.com BEGIN RETURN p_value;
END; [SQL] -- CREATE a function INDEX and query the result 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
[SQL] -- modify the FUNCTION Code create or replace function f_sing (p_value in VARCHAR2) RETURN VARCHAR2 deterministic is begin www.2cto.com RETURN p_value | 'B'; END; [SQL] -- <span style = "color: # ff0000;"> re-query the result and prompt that the function index is disabled. The conclusion in the book is that 8 8a is still found after the function code is modified, this is inconsistent with the original book. Is the version incorrect? </Span> SELECT * FROM t WHERE f_sing (y) = '8a '; 20 1 ORA-30554: function-based index TWGERP. IDX_F_SING_Y disabled
[SQL] -- after the index is disabled using hints, no information is found. This is correct. SELECT/* + no_index (t) */* FROM t WHERE f_sing (y) = '8a '; x y [SQL] -- re-create the index and query results. No data can be found, correct. 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