We have managed to narrow down the growing of the database/indexes to the following scripts:

Update Clients
Set EntityName = RTRIM(LastName),
FirstNames = '', LastName = ''
where IsIndividual = 'N';
Commit;

Update Clients
Set EntityName = RTRIM(LastName) || ', ' || COALESCE(FirstNames, '')
where IsIndividual = 'Y';
Commit;

Update Clients
Set Firstnames = Fname, Lastname = Lname
Where isindividual = 'N';
Commit;


Is there anything in that can cause the duplicate indexes to rise so dramatically?

--- In firebird-support@yahoogroups.com, "Ann W. Harrison" <aharrison@...> wrote:
>
> On 2/17/2011 6:09 PM, timwilsonaus wrote:
> > --- In firebird-support@yahoogroups.com, Helen Borrie<helebor@> wrote:
>
> >
> > We ran gstat – before and after performing a backup restore (Image on left is after restore). Most of the statistics are very similar when comparing the reports, except for the clients table – in particular these two indexes in the table. For whatever reason its showing a massive variance between the total number of rows that share duplicate indexes (Total Dup) and Max Dup.
> >
> > Gstat Image Link: http://imgh.us/Comparison.png
> >
> > We have a data refresh process for our clients which pulls in contact information via an ODBC connection from an external Database. If anywhere this would be where the issue is stemming from, however it seems to only affect this one particular clients Database. The Client also complains about slowness when users are searching for contacts…
> >
> > Is there is any way to fix this problem without having to do a backup restore every time?
> >
>
>
> Those images make no sense to me at all. I'm probably just having
> an unusually bad day, but why would there be more nodes in secondary
> indexes than in the primary? Every record and old record version has
> a primary key, so ... did you by any chance run gfix without backing
> up and restoring? Nah,if you'd done that, I'd be able to smell the
> database from here - 10 time zones away - and besides the size of the
> database would not be your first concern.
>
> What does the gstat of the table look like? Use the -r switch, if
> you would. On the before backup state, of course.
>
> To answer your question, if you see something like this, you can fix it
> be deactivating and reactivating the bloated indexes.
>
> The question is how did that happen? In particular, how did you happen
> to get two million one hundred twenty-four thousand eight hundred and
> thirty-nine clients with the same last name?
>
> What version of Firebird? Which architecture? What operating system?
> Do you regularly shutdown the machine by pulling out the power cord?
> Anything odd about the indexes themselves - e.g. expression based ...
>
> Very odd looking indeed. Maybe a bit more tea will clear my mind.
>
> Cheers,
>
> Ann
>