1

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;

1 Answer 1

2

Yes, you have to rebuild the index.

Check this link on Oracle Docs, section Disadvantages of Function-Based Indexes.

An index does store physical data, be it function-based or otherwise. If you modify the underlying deterministic function, your index no longer contains the valid data and you have to rebuild it manually and analyze it afterwards.

Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for the link, meanwhile, I wrote a testscript, that show this problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.