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

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.

No, it's not useless. The PK will "piggy-back" onto the index of the same
shape.

There are actually some administration benefits to doing it that way. You
can specifically remove the primary key and yet the index will keep working
allowing improved performance while you perform administration tasks. If the
PK is the index (ie based on the secret underlying index) then you can't
disable the PK without also disabling the index and therefore losing some
performance benefits.

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

Theoretically (and in practice?) the engine should block the existence of an
identical index. However it does appear to allow one index to exist that is
a sub-set of another index. That sorta makes sense IF the smaller index
allows duplicates and the larger index is unique, but even so I'd be
automatically suspicious of any indexs that are "too close" to each other.
I'd have to see a very strong case that proves they are relevant.

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

If a suitable unique index already exists when a PK is defined, then the PK
will piggy-back a ride on the index. If no suitable index exists, then the
engine will create it's own index. If you look in sysindexes, you'll notice
that these secret indexes have a name that starts with a space character,
therefore it becomes impossible for anyone to affect that index with
ordinary SQL statements. You *could* affect them by doing violence to the
sysindexes and other tables, but that's just begging for trouble when you
screw up some underlying relationship that should exist...

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

Errrr emm errr....

Mapping the space concepts of one engine to another is not always possible
or complete. Informix indexes may be stored either with or separate from the
rows of the table. Even if stored together the index pages occupy different
"extents" within the "tablespace".

Any informix index can be clustered, but only one per table. For Informix,
the clustering operation is a one-off operation which physically sorts the
data rows to follow the sequence of the index. You need to re-apply the
clustering operation on a regular basis if events such as row deletions,
modifications, or irregular insertion of rows causes the clustering effect
to deteriorate.

Note that this operation demands the existence of enough space to duplicate
the table, since the old rows are not destroyed until the new copy of the
table is completely written. Further, this extra space must exist in the
dbspace(s) that the table currently occupies.

However if you have a table, clustered perhaps on the always-increasing key,
and rows are never deleted, then the table will maintain it's natural
clustering because new rows will be added to the end and the key will always
be bigger than the previous row. In fact, for such a table, applying a
clustering operation is a moot point because the laws of nature keep the
data rows in physical order.

--
There's nowt wrong wi' owt what mitherin' clutterbucks don't barly grummit!
-
Replies directly to this message will go to an account that may not be
checked for a week or two. For more timely e-mail response, use (only
in an emergency) ahamm sanderson net au with all the usual punctuation.

"Andrew Hamm" <ahamm@mail.com> wrote in message
news:beifjt$59ekq$1@ID-79573.news.dfncis.de...
> 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.
>
> No, it's not useless. The PK will "piggy-back" onto the index of the same
> shape.

So you're saying that the engine will not create another index of the same
construction, and you won't suffer the additional overhead of maintaining
one extra physical index?

On Thu, 10 Jul 2003 03:40:29 -0400, Neil Truby wrote:

> "Andrew Hamm" <ahamm@mail.com> wrote in message
> news:beifjt$59ekq$1@ID-79573.news.dfncis.de...
>> 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.
>>
>> No, it's not useless. The PK will "piggy-back" onto the index of the
>> same shape.
>
> So you're saying that the engine will not create another index of the
> same construction, and you won't suffer the additional overhead of
> maintaining one extra physical index?

Correct, IDS will NOT create two indexes with the identical set of keys
in the same order with the identical sort ordering. You CAN create
multiple indexes with the same keys in different order or with the
ASCending/DESCending attributes of the columns different.

If you create a PK and an index on those columns already exists the PK
will use the existing index and no create a new one, yes.

Art S. Kagel