I'm using an own DETERMINISTIC function for a function based index on one of my tables. What will happen if I modify the PL/SQL-Code of this function? (located in an package)
Will the Index invalides? Do I have to rebuild it (manually)?
Thanks!
Edit:
Here is my example script, which I used for testing now.
CREATE OR REPLACE FUNCTION func_test(v IN NUMBER) RETURN VARCHAR deterministic IS
BEGIN RETURN 'lol' || To_Char(v); END;
/
CREATE TABLE tab_test (id NUMBER(20,0) NOT NULL);
INSERT INTO tab_test VALUES (1);
INSERT INTO tab_test VALUES (2);
INSERT INTO tab_test VALUES (3);
CREATE INDEX idx_test ON tab_test (func_test(id));
SELECT id, func_test(id) FROM tab_test WHERE func_test(id) = func_test(2);
--1 first records fetched in 0 ms
--
--| 2 | lol2 |
--Change Function
CREATE OR REPLACE FUNCTION func_test(v IN NUMBER) RETURN VARCHAR deterministic IS
BEGIN RETURN 'rofl' || To_Char(v); END;
/
SELECT id, func_test(id) FROM tab_test WHERE func_test(id) = func_test(2);
-- 0 first records fetched in 0 ms
ALTER INDEX idx_test rebuild;
SELECT id, func_test(id) FROM tab_test WHERE func_test(id) = func_test(2);
--1 first records fetched in 0 ms
--
--| 2 | rofl2 |
DROP TABLE tab_test;
DROP FUNCTION func_test;