Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
You should only recompile invalid objects. Otherwise Oracle thinks your function's body has changed. And then how is Oracle supposed to know that your function Str2Number doesn't all of a sudden return different values? –  Colin 't Hart Apr 29 '13 at 8:09
add comment

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
    
this really helps. thanx. but it is unfair from oracle 1. befor compile_schema indexes was ok and than become broken 2. if it recompiles ALL objects, why it skips function-based indexes 3. no warnings about ... just screams from end-point users :( –  evg345 Apr 29 '13 at 8:34
    
An index isn't a compiled object like a stored procedure. You (re)build an index, not (re)compile it. –  Colin 't Hart Apr 29 '13 at 8:37
    
I do also wonder if TO_NUMBER has been overlooked –  FreshPhilOfSO Apr 29 '13 at 8:45
    
Possibly, but in general for a user-defined function used in a function-based index the above holds. –  Colin 't Hart Apr 29 '13 at 8:47
    
Seems like a good reason to try to avoid using user-defined functions in function-based indexes if they can be avoided. Str2Number doesn't sound like it brings much to the party that to_number wouldn't. –  David Aldridge Apr 30 '13 at 11:19
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.