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

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

On Wed, 09 Jul 2003 14:44:51 -0400, bill wrote: Most of this has been covered so just briefs, but the last needs more comment: > 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. As noted the primary key is using that index. Check out the sysconstraints record for that constraint. > 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? As noted, you cannot get a dup unless the order of the columns is different or the ASCending/DESCending attributes of the ey fields are different. BTW IDS can read an index in either order so these attributes are only useful when the combination of them in the keys is not strictly the inverse of another index. When they are inverses the optimizer will likely use whichever was created first as the sort and filter values of each as well as the depth and node counts will be identical. > Are PKs implemented by an index, so if an index that duplicates a PK, > this is the same as an index that duplicates another? When you create a PK it will use any existing unique index on the selected key(s), if none exist it creates a 'hidden' index, one whose name begins with a space character. > 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? Just the space needed by the constraint record (sysconstraints) and the space needed by the primary key index. Clustering in Informix is different than in SQLServer and its progenitor Sybase (and different again in Oracle). Sybase/SQLServer Clustered indexes remove the table altogether and hang each data row off the index key node. These indexes remain clustered over time and are VERY efficient for selecting a single row by that one key. HOWEVER, a Sybase/SQLServer cluster index causes the table to be SLIGHTLY less efficient to access using other indexes and VERY INEFFICIENT to access using any non-indexed field (ie during a table scan) or even to retrieve a range of keys. Informix Clutered index is simply another index but the table's rows have been sorted on disk so that they are stored in the same order as their keys in the index so that rows with adjacent keys are adjacent on disk. This is VERY efficient for any kind of table scan using the cluster index including ranges of keys without affecting the efficiency of access via other indexes at all. The downside is that the table does not remain clustered long and the efficiency is lost over time so the index must be 'reclustered' periodically. IDS index location was covered in another post. Art S. Kagel