>>> 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
http://www.upscene.com

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

Database questions? Check the forum:
http://www.databasedevelopmentforum.com