Re: 5.01 SE - Q: How to alter indexes like ' 152_56' to cluster

> Hi, > trying to improve performance of some queries, we are looking for a > possibility to alter indexes, which are resulting from primary and foreign key > constraints and having names like ' 152_56', to cluster. > > Any ideas? > > Martin Berns (martin.berns@materna.de) This isn't possible directly as these indexes cannot be specified in a alter index statement as they begin with space. But you can do it byfirst dropping the constraint, then you create a standard index which has the same structure as the constraint index. Once this is done you can recreate the constraint and you will find that it will use the index that was just created. These indexes can then be clustered in the normal way. As you no doubt are aware this only needs to be done for one constraint per table as only one index can be clustered. One thing you can't do, which I see as drawback, is to create an index consisting of more columns than is needed for the constraint but done so that the constraint could make use of it. For example on a foreign key constraint for a foreign key that allows nulls you cannot create an index with FK_col, PK_col and have the constraint use it. This means that you cannot avoid the performance degradation caused by multiple null index entries. Another issue is that you someimes have to create performance indexes alongside constraint indexes when the performance index could be used by the constraint. This results in additional index maintenance overhead on the engine. I did raise a feature request over a year ago but have heard nothing from Informix. Perhaps if others were to raise a similar request this might make it into future product they sure dont respond to me. Cheers - Jim My opinions are my own. They may vary with time but they remain MINE! ---------------------------------------------------------------------- Name: Jim Gordon Company: DHL Systems Inc, Burlingame, CA, USA ----------------------------------------------------------------------