On Sun, Feb 27, 2011 at 6:24 PM, Tim Wilson <timwilsonaus@...>wrote:

> 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?
>
>

OK, I was having a bad day. You've got four copies of each client record,
three of which have
different values for LastName. One has the same value for each LastName.
That explains why
there are many more entries for the secondary indexes than for the primary.
Particularly if
you did that sequence of actions several times. If there's an overlapping
transaction that inhibits
garbage collection, you'll see something like what you describe.

Is it possible to do all the updates in a single pass, or at least in a
single transaction?

Good luck,

Ann


[Non-text portions of this message have been removed]