Peter Ypenburg wrote:

>...we do bulk deletes
>
>If I try to run: alter index RDB$PRIMARY20 inactive
>

The primary key index is not a problem since it is unique by
definition. Don't bother messing with it.

>So then I tried to run: alter index RDB$FOREIGN21 inactive and I get the
>same error. I suspect the reason is that the foreign key is linked to
>the primary key on the other table and you cannot deactivate a primary
>index.
>

In ISQL, execute the command : show table LEADTIMEANALYSIS;

After the description of the fields, the output will list the
automatically generated constraint name and the name of the referenced
table for each foreign key.

Then, for each of the foreign keys, execute the command: select count
(*) from <referenced table>;

When you find a referenced table that is much smaller than
LeadTimeAnalysis (e.g. 1:10000), drop that foreign key reference and
replace it with a named constraint. Before your bulk delete, drop the
named constraints.

>QUESTIONS:
>With mass deletes I need to take care of indices but how do I
>programmatically deactivate the indices with out going to the extend of
>dropping and recreating the constraints?
>

The only way to deactivate indexes created for foreign key constraints
is to delete the constraints. However, by using named constraints, you
can drop and recreate the constraints without having to look up their
names each time.

>How do I know (if possible) what index is messed up, is there a way to
>test or check indices programmatically?
>

The index isn't really messed up - it's just - well it _is_ messed up
but not corrupt. You shouldn't need to repeat the analysis - in all
probability the relative size of the referenced and referencing tables
is stable so the same set of constraints will cause the problem each time.

Regards,


Ann