Manually create indexes for constraints or no need

Hello to all, I work in a software development department where it has been assumed for several years that when creating constraint objects (primary, reference or check) on our development/production Informix databases, one should always manually create before the respective index. This allows for following some naming rules, like the index to have the same name as the constraint. But if no index is created, Informix always creates one automatically for the given columns of the constraint (even though it has that numerical nomenclature). So I'd like to know what is generally assumed as the best practice: should one manually create the index or from a strictly practical point of view, is the one automatically created by Informix simply enough? Thank you very much for your kind help.

Hello Carlos, I'm not sure if I can tak "on behalf" off the "generally assumed as the best practice".... But in any case, I think it's nice to manually create the index not only because of naming conventions, but also because you can choose where to put the index (and some customers like to split data and indexes into different dbspaces). So, in practice, if you don't care about the naming conventions and you don't mind your indexes go into the same dbspace as the table, letting Informix do the job would be enough. But I think there are good reasons to create them manually. Also, from a very personal perspective, when I find this behavior in a customer It is clearer that they really know what they're doing :) Also note that in 11.70 you can avoid the automatically index creation (although in most cases this is not a good idea). Regards/Cumprimentos. On Tue, Aug 30, 2011 at 5:01 PM, CARLOS ALVES <carlos.oliveira@audaxys.com>wrote: > Hello to all, > > I work in a software development department where it has been assumed for > several years that when creating constraint objects (primary, reference or > check) on our development/production Informix databases, one should always > manually create before the respective index. This allows for following some > naming rules, like the index to have the same name as the constraint. But > if > no index is created, Informix always creates one automatically for the > given > columns of the constraint (even though it has that numerical nomenclature). > > So I'd like to know what is generally assumed as the best practice: should > one > manually create the index or from a strictly practical point of view, is > the > one automatically created by Informix simply enough? > > Thank you very much for your kind help. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --002215b03466cdb8aa04abbb3faf

My recommended Best Practice is to always manually create indexes to support constraints BEFORE creating the constraint. Like you I also name those indexes with the same name as the constraint it supports. I believe this so strongly that my dbschema replacement utility, myschema, will automatically output the constraint index's create statements before putting out the constraint definition. It does not attempt to name the indexes according to the constraint name (though that's an interesting exercise for a later release - hmm), but it does give the index a usable name that indicates the type of constraint that it supports. So a hidden index named " 102_34" that supports a primary key will become an index named "P102_34". If that index supports a foreign key index then it would become "R102_34" or for a unique key constraint index "U102_34". Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Aug 30, 2011 at 12:01 PM, CARLOS ALVES <carlos.oliveira@audaxys.com>wrote: > Hello to all, > > I work in a software development department where it has been assumed for > several years that when creating constraint objects (primary, reference or > check) on our development/production Informix databases, one should always > manually create before the respective index. This allows for following some > naming rules, like the index to have the same name as the constraint. But > if > no index is created, Informix always creates one automatically for the > given > columns of the constraint (even though it has that numerical nomenclature). > > So I'd like to know what is generally assumed as the best practice: should > one > manually create the index or from a strictly practical point of view, is > the > one automatically created by Informix simply enough? > > Thank you very much for your kind help. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba21239310c55b04abbb4c2b

Fernando, you said "in 11.70 you can avoid the automatically index creation". I'd like to clarify: In 11.70 you can create a FOREIGN KEY constraint without a supporting index. The index is mandatory if the constraint will include CASCADING DELETE and is absolutely needed (though the engine has no way to enforce the requirement) if you will be manually deleting or modifying any of the key values in the parent or independent table to which the foreign key refers (supported in some other RDBMS's as CASCADING UPDATEs). I don't agree that creating foreign keys without an index is "not a good idea". If you think through the processing involved, for foreign keys referencing lookup tables if users never update or delete the dependent key values and cascading delete is not defined, then the index on the dependent table's foreign key is rarely used. The exception is parent child relationships where queries will be driven by filters on the parent table and the foreign key index is needed as a join index to locate child table rows. In this case only is the foreign key constraint index required for efficient processing. My point is that for foreign key relationships that refer to a lookup or code translation table, the foreign key index on the dependent table is NEVER used, not for checking key validity, for joining, or for any other reason. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Aug 30, 2011 at 12:10 PM, Fernando Nunes <domusonline@gmail.com>wrote: > Hello Carlos, > I'm not sure if I can tak "on behalf" off the "generally assumed as the > best > practice".... > But in any case, I think it's nice to manually create the index not only > because of naming conventions, but also because you can choose where to put > the index (and some customers like to split data and indexes into different > dbspaces). > > So, in practice, if you don't care about the naming conventions and you > don't mind your indexes go into the same dbspace as the table, letting > Informix do the job would be enough. > But I think there are good reasons to create them manually. Also, from a > very personal perspective, when I find this behavior in a customer It is > clearer that they really know what they're doing :) > > Also note that in 11.70 you can avoid the automatically index creation > (although in most cases this is not a good idea). > > Regards/Cumprimentos. > > On Tue, Aug 30, 2011 at 5:01 PM, CARLOS ALVES > <carlos.oliveira@audaxys.com>wrote: > > > Hello to all, > > > > I work in a software development department where it has been assumed for > > several years that when creating constraint objects (primary, reference > or > > check) on our development/production Informix databases, one should > always > > manually create before the respective index. This allows for following > some > > naming rules, like the index to have the same name as the constraint. But > > if > > no index is created, Informix always creates one automatically for the > > given > > columns of the constraint (even though it has that numerical > nomenclature). > > > > So I'd like to know what is generally assumed as the best practice: > should > > one > > manually create the index or from a strictly practical point of view, is > > the > > one automatically created by Informix simply enough? > > > > Thank you very much for your kind help. > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --002215b03466cdb8aa04abbb3faf > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec52994bb9e09c804abbb763b

This is probably a personal choice :-) But I prefer going the manual route. I just prefer my own naming conventions, which sometimes make troubleshooting and maintenance easier for the dba (and even the software developer ?). It is much quicker to identify an index (and it's purpose) if it was named "customer_index1" instead of "ix014321" :-) ..... just makes it more user friendly :-) > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > CARLOS ALVES > Sent: Tuesday, 30 August 2011 06:02 PM > To: ids@iiug.org > Subject: Manually create indexes for constraints or no need [24771] > > Hello to all, > > I work in a software development department where it has been assumed > for > several years that when creating constraint objects (primary, reference > or > check) on our development/production Informix databases, one should > always > manually create before the respective index. This allows for following > some > naming rules, like the index to have the same name as the constraint. > But if > no index is created, Informix always creates one automatically for the > given > columns of the constraint (even though it has that numerical > nomenclature). > > So I'd like to know what is generally assumed as the best practice: > should one > manually create the index or from a strictly practical point of view, > is the > one automatically created by Informix simply enough? > > Thank you very much for your kind help. > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum. NOTE: This e-mail message is subject to the MTN Group disclaimer see http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx