Re: index duplicates PK, is it redundant?

Yes. No point in having 2 identical indexes, chances are the optimizer is only going to use one of them. If one is a PK then that will be used. PKs are implemented as an index. If you do not provide an explicit name for the PK the engine generates one for you. I think it is best to deliberately specify a name for the PK. Since the PK is enforced via an index, the answer to you last question is it depends: 1) Version of informix. With 9.x indexes are separate from the table by default. 2) If you create the index first and then add the PK, the PK will use the existing index. The location specified for the index dictates where the index is stored. In pre9 versions, if you do not specify a location then the index will be stored in the same extents as the data [the index will have separate pages]. 3) In 9.x, if you set the environment variable DEFAULT_ATTACH=1 when the engine is started, indexes will be created in the same extents as the table if you do not explicitly change the location [ie 7.x functionality is adhered too]. Mark ----- Original Message ----- From: "bill" <rcairflyer@hotmail.com> To: <informix-list@iiug.org> Sent: Wednesday, July 09, 2003 14:44 Subject: index duplicates PK, is it redundant? > I came across a database that has an index with the same columns, in > the same order, as the primary key. Is this index likely to be > useless and redundant? I expect that if the index had different sort > orders it could be useful. > > How about one index that duplicates another - if an index duplicates > another, would the second index be useless, redundant, and a drag on > the system? > > Are PKs implemented by an index, so if an index that duplicates a PK, > this is the same as an index that duplicates another? > > What space is used by the PK? Is it like a MSSQLServer key and can be > clustered or not, or is the PK always stored in the same space as the > table? > > Bill sending to informix-list