At 07:00 PM 3/04/2008, kimon_the_athenian2 wrote:

>Hi!
>
>I have 2 databases with identical structure and different data. (But
>the amount of data is approximately same)
>
>I noticed that one query runs much slower in one of the databases and
>it turned out that FB is not using primary key index of on of the
>tables. When executing sama query in other DB the index was used.
>
>When i backed up and restored the slow DB then it started to use PK index.
>
>
>Are there some other options besides backup/restore to fix this kind
>of problems? Sweeping did not help.

Sweeping doesn't rebuild indexes.

>And i suppose i can't make primary key index inactive/active.

If you are able to access the database exclusively, dropping and then recreating PK and FK constraints will have the same effect as setting ordinary indexes inactive/active. It will require dropping dependent FKs first so its usefulness largely depends on the number of dependencies there are - as well as the feasibility of taking the users offline for the duration, of course.

This sounds like a compound PK where one (or more) segments have low selectivity and mass inserts and deletes are being performed without any follow-up housekeeping. If the PK is getting so out of balance, it would be expected that dependent FK indexes would suffer the same kind of deterioration.

How often do you run SET STATISTICS on your indexes as a maintenance measure? This won't rebuild indexes but it should help to improve the selectivity of these unstable indexes.

As a tip, it's generally a bad idea to put constraints on columns with low selectivity. Better to use a surrogate key with a generator for your PK and to use a unique INDEX (not a UNIQUE constraint) if you have a multi-column set for which you need to enforce uniqueness. Then you need never tamper with the keys at all and can run ALTER INDEX on the unique index as required to keep things shipshape.

./heLen


./heLen