bill wrote:
> 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.

Primary keys are implemented using an index, so the index was most
likely created by the primary key. If the index has a sensible name,
then it was most likely created manually, and the primary key is using
it as well.

> 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?

AFAIK, you cannot create a duplicate index. You should receive an error
if you try this, although I haven't tried it for some time. If you
create a primary key and a similar index exists, then that index is used
by the primary key.

> Are PKs implemented by an index, so if an index that duplicates a PK,
> this is the same as an index that duplicates another?

I think I covered this.

> 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?

I don't know SQLServer, but clustering in Informix is a term referring
to the order of the data, not the indexes. However, you implement this
by creating a clustered index, which will physically order the data in
the table. The location of the index pages is something different.

In version 7 and before, index pages are stored with the data pages by
default. You can explicitly detach indexes by locating them in a
different dbspace or fragment them.

In version 9, index pages are NOT stored with the data pages by default,
they are detached. You can still explicitly locate them in a different
dbspace or fragment them.

Cheers,
--
Mark.

+----------------------------------------------------------+-----------+
| Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /|
| Mydas Solutions Ltd http://MydasSolutions.com |///// / //|
| +-----------------------------------+//// / ///|
| |We value your comments, which have |/// / ////|
| |been recorded and automatically |// / /////|
| |emailed back to us for our records.|/ ////////|
+----------------------+-----------------------------------+-----------+

sending to informix-list