I'm not sure if "fall out" is the correct description, but what I mean is: If I have three indexes on a table, for fields A, B and C, and I update column C for every record, obviously I'll need to recreate the index for C.
But what about indexes A and B? I didn't update their underlying column values, but would the records that had column C updated have fallen out of A and B as well?
Let's assume for argument B & C are varchar fields, while column A is a blob. It sounds like from your description that indexes for A, B and C will all need refreshing if column C is updated?
Thanks Kai....er, can I ask a followup? What if instead of updating a number of rows I delete them? Would I be wise to rebuild the metamorph indexes then too, or are they still ok?
They still need updating if you delete rows. But the performance hit of not updating the index is usually slightly less, as the effort to remove hits from the index result set for those deleted rows is typically less than the effort to linearly scan updated/inserted/changed rows that haven't yet been indexed.
Just to be clear. You don't need to drop and rebuild any index. Just update metamorph indices by reissuing the same create index statement used to create it. The other option is to get the monitor (chkind) update it automatically as needed. See also SYSMETAINDEX.
I thought that incremental reindexing (eg issuing the same index commands) eventually led to bloated indexes, much like changing the oil in your car without replacing the filter can hurt your valves eventually.
How often would you reccomend a drop-and-recreate, to keep things nice and fresh? After three incrementals, ten, maybe twenty? Let's assume 5% of our rows will have changed between reindexes.
