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?
That depends on the types of the index, and the type of column C. Non metamorph indexes are always kept up to date. All metamorph indexes will need to be refresh if a varchar or other variable size record is updated as the record may move.
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.
A drop and recreate is never needed. After an incremental update the index will be identical to a drop and recreate.
If you were to change 30-40% of your records in a batch it might be more efficient to create a new index than to incrementally update the old one, but once they are completed they would be identical.
