Re: ALTER (Constraint)-INDEX TO CLUSTER, why not possible ?

Bernhard Derix wrote: > > In the Informix handbook, I can read, that's not possible to alter an index, > created by a constraint in a create table statement to cluster. > > I just ask myself now: "WHY ?" > > Do you know the answer ? Because the automatically created constraint index names begin with a space which is syntactically forbidden so you cannot name these indexes in any SQL statement explicitely. The best solution is to: 1) drop the constraint 2) build the corresponding index using a useful name 3) re-add the constraint, it will use the existing index Then you can ALTER INDEX ... TO CLUSTER anytime you need to. Note that if you are only doing this to defragment a table, rather than to take better advantage of sequential access, you can do that more quickly with the ALTER FRAGMENT ON TABLE ... INIT IN ... syntax. Art S. Kagel