Unique Index or PK ?

[p175]

What are the advantages / disadvantages of having primary keys in lieu
of ordinary indexes ? If there are no foreign relationships defined, is
it better to have a two column index allowing reverse scans or primary
key ? Any performance or other benefits / issues ?

Many thanks.

[p175]

p175 wrote:

What are the advantages / disadvantages of having primary keys in lieu
of ordinary indexes ? If there are no foreign relationships defined, is
it better to have a two column index allowing reverse scans or primary
key ? Any performance or other benefits / issues ?

Many thanks.

Ignore, sorry . found a similar post .. siiigh DOH !!

[Mark A]

"p175" <td******@hotmail.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...

What are the advantages / disadvantages of having primary keys in lieu
of ordinary indexes ? If there are no foreign relationships defined, is
it better to have a two column index allowing reverse scans or primary
key ? Any performance or other benefits / issues ?

Many thanks.

Keep in mind that if you create the index first (before creating the PK),
then you can define reverse scans, cluster, etc and then when you create the
PK it will use the existing index with the attributes you want in the index.

Reverse Scans has "virtually" no overhead, and "may" have benefits in
certain situations. It really should be the default.

[p175]

Mark A wrote:

Keep in mind that if you create the index first (before creating the PK),
then you can define reverse scans, cluster, etc and then when you create the
PK it will use the existing index with the attributes you want in the index.

Reverse Scans has "virtually" no overhead, and "may" have benefits in
certain situations. It really should be the default.

Now that I didn't know, so if I create an index, I can then alter the
table, adding a PK constraint that will assume the attributes of the
original index .. hmmm

Most useful, thanks.

[Mark A]

"p175" <td******@hotmail.comwrote in message
news:11*********************@p79g2000cwp.googlegro ups.com...

>
Now that I didn't know, so if I create an index, I can then alter the
table, adding a PK constraint that will assume the attributes of the
original index .. hmmm

Most useful, thanks.

A PK will create a unique index unless there is already a unique index on
the same columns, in which case no additional unique index is created. You
will get the following warning message:

Assume table TEST has no keys and no indexes to start with:

CREATE UNIQUE INDEX DB2INST1.TEST_IX1 ON DB2INST1.TEST (COL1 ASC);
DB20000I The SQL command completed successfully.

ALTER TABLE DB2INST1.TEST PRIMARY KEY (COL1);
SQL0598W Existing index "DB2INST1.TEST_IX1" is used as the index for the
primary key or a unique key. SQLSTATE=01550

In the DB2 catalog (SYCAT.INDEXES), the UNIQUERULE wil change from U
(Unique) to P (used for a Primary Key). If the PK is dropped, then the
unique index remains (only if you created it before the PK).