What would be the best way to determine the optimal state of an index? SYSINDEX entry and _T.btr/_D.btr file sizes usually indicate index health. Is there anything else that I should be looking for to determine if an index needs to be recreated?
The _T and _D files of metamorph indices reflect record insert/update/delete activity since last index update. If they are large (relative to their empty state of less than a K) the index should probably be updated (not dropped and recreated).
If you're processing records in batches you may want to do an index update after each batch.
If your database is changed continuously or sporadically in small batches you should use SYSMETAINDEX with chkind to control when metamorph indices are automatically updated.
_D files continues to grow as batch delete commands (tsql statements in a file) are issued against a texis table with metamorph inverted index. I've noticed that as the _D file grow larger, deletes become slower. Eventually the slowness becomes extreme - even single deletes take about 40 seconds.
If I delete 1000 records at a time and then update the index, will that lower the size of the _D file before I execute the next 1000 delete statements?
Texis version: Texis Version 04.04.1067366033(20031028)
Number of rows: 5 million
Size of the table: 30 GB
