Re: Attempting to ALTER INDEX TO CLUSTER on Primary Key

> So what's wrong? > > I'm attempting to use ALTER INDEX TO CLUSTER to reorganize a table. > The table definition includes a PRIMARY KEY and two other indexes but > the primary key would appear to be the best candidate for the ALTER. > > The table definition looks like: > > snip... > > When I check the SYSINDEXES table I see an index name of 280_1305 with > what appears to be a space in front of the '280'. > > I have yet to determine how to use this index name in the alter > statement. All the attempts listed below fail: > > ALTER INDEX 280_1305 TO CLUSTER> ALTER INDEX ' 280_1305' TO CLUSTER > ALTER INDEX " 280_1305" TO CLUSTER This is a common problem with Informix. I don't know if there is a way to do what you want with the index as it is. I can tell you how to avoid this in future databases, though. When you create a table, do not specify any primary or foreign keys, or any other constraints that require an index (e.g., UNIQUE). After you create the table, create all of the indexes necessary to support the referential integrity and other constraints, naming each index according to whatever naming standards you choose. Then, alter the table to add the RI and constraints. Informix will see that the index that it would automatically create already exists, so it will use your index. This also allows you to create the indexes as detached, which you can not do with implicity created indexes. Mark Collins mcollins@us.dhl.com