Subject Re: [firebird-support] Stored procedures vs. indexes
Author Martijn Tonies
>>> In my quest to deactivate unneeded indexes I encountered a problem:
>>> Stored procedures (and possibly triggers) are compiled to use these
>>> indexes and apparently they don't like it when I delete (or deactivate)
>>> those indexes ("index unexpectedly deleted" - Firebird 1.5). At the
>>> moment it seems that I will have to drop and re-create all procedures to
>>> solve this problem. Isn't there an easier solution? For example, if I
>>> could somehow get a list of indexes used by a procedure, I could match
>>> it against my list of indexes to delete and drop/create only procedures
>>> that need it.
>> Indices are not fixed in use by a stored procedure, UNLESS you use a
>> PLAN clause in the SQL statements.
> I don't. But I can't imagine any other reason why I would get that
> "index unexpectedly deleted" when an application tries:
> SELECT * FROM some_procedure

I believe this message comes when you have the stored procedure loaded
in the metadata cache (as done by the Firebird server), executed once so
a particular PLAN is created and then delete the index.

Could be wrong though, as the internals of the server are entirely known to
me ;-)

Either way, stored procedures are, metadata-object-wise, not "depending"
on indices.

With regards,

Martijn Tonies
Upscene Productions

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum: