Re: FKs and indexes

Add advantage #4, which is that when a self-named index gets fragmented, you can do an ALTER FRAGMENT on it to alleviate fragmentation. ----- Original Message ----- From: Mark Collins <mcollins@us.dhl.com> To: Thomas J. Girsch <tgirsch@iname.com>; <informix-list@iiug.org> Sent: Tuesday, April 20, 1999 5:37 PM Subject: Re: FKs and indexes > The short answer is no. There is no difference (unless someone corrects me) in > the behavior of an index that is automatically created vs. one that is manually > created. > > That said, there are a few reasons that you would want to manually create an > index: > 1. You can name the index (as you mentioned). The immediate > benefits of this are minimal EXCEPT THAT some ODBC tools > (like MS-Access) won't recognize indexes with a space for > the first character. If they don't recognize the primary > key index, they can't determine that a given column (or > set of columns) is a primary key, and can not determine > relationships with other tables. Also, any updates or > deletes can create some really nasty WHERE clauses, with > every column named and tested for equality, instead of only > those columns in the primary key. > 2. You can specify a non-default fillfactor. > 3. You can specify a different dbspace, so that the index > is detached from the table. You can also make the index > fragmented. Either or both of these can improve > performance. > > This is why the general consensus is that you should create the table, build the > index, then alter the table to declare the primary/foreign keys. > > > > Here's another question for the group: What are the differences between > > these two things: > > > > -- First thing > > CREATE TABLE foo ( > > col1 INTEGER, > > col2 INTEGER > > ); > > ALTER TABLE foo ADD CONSTRAINT PRIMARY KEY (col1) CONSTRAINT foo_pk;> > > > -- Second thing > > CREATE TABLE foo ( > > {as above} > > ); > > CREATE UNIQUE INDEX foo_ix00 ON foo(col1); > > ALTER TABLE foo ADD CONSTRAINT PRIMARY KEY (col1) CONSTRAINT foo_pk;> > > > > > In the second way, you get to choose the name for the index, and in the > > first way, you don't. But is there anything more subtle than that? That > > is, does an implicitly created index behave any differently than an > > explicitly created one? > > > > Mark Collins > mcollins@us.dhl.com >

There is another difference not yet mentioned (also less important). The automatically generated index is dropped when then constraint is dropped, the other one is not. Achim