Why does DBMS_UTILITY.COMPILE_SCHEMA break function-based indexes?

After mass structure updates, some views/procedures became invalid.

So I just recompiled them with:

exec dbms_utility.compile_schema( USER )

But after that, all function-based indexes, which before were in a good state, became broken, and SQL statements against those tables failed. And that is the problem.

Example of such indexes:

CREATE INDEX DOCUMENTS_NMBR_UCN_FIDX ON DOCUMENTS(Str2Number(NUMBER_UCN));

CREATE INDEX DOCUMENTS_DATE_FIDX ON DOCUMENTS(Date2Number(REG_DATE));

Why did that happen?

Oracle is compiling all objects, so as far as Oracle is concerned all code in all objects is new code.

As a consequence, Oracle has no way of knowing that your function Str2Number still returns the same values that were used when the index was created.

Instead you should only recompile invalid objects,

exec dbms_utility.compile_schema(user, false);

will do this.

StackExchange Database Administrators Q#41130, answer score: 7