Re: Primary key, Foreign key and indexes in Informix

"Andrew Hamm" <ahamm@mail.com> wrote in message news:c6n282$dqs2i$1@ID-79573.news.uni-berlin.de... > > Therefore, if you are going to put an index onto a column or set of columns > that has a large number of duplicates, then you would probably be better off > NOT using the index. Nope...add a serial field to the table and build the index as a unique index on (myfield1,serial_field). Then the index is used but is unique so no rowid lists are used! > > Therefore, every time an order is added or removed from the table, there > will be a lot of hard work to be done adjusting the index. This index would > be A Very Bad Idea. > See above. > Now, if you are a Relational Database Purist, you might insist that your > order table MUST have a foreign key on the state table. If you believe this, > you will suffer. I am assuming that the foreign key will be smart enough to use the above index? ..wait a sec I have 9.40 on windows here.....ok playing in dbaccess I can create create table "Administrator".t2 ( p1 integer ); revoke all on "Administrator".t2 from "public"; create unique index "Administrator".pi1 on "Administrator".t2 (p1) using btree ; alter table "Administrator".t2 add constraint primary key (p1) constraint "Administrator".c1 ; and create table "Administrator".t1 ( a serial not null , f1 integer ); revoke all on "Administrator".t1 from "public"; create unique index "Administrator".fi1 on "Administrator".t1 (f1,a) using btree ; alter table "Administrator".t1 add constraint (foreign key (f1) references "Administrator".t2 constraint "Administrator".fc1); ...even though my index fi1 contains the serial column a as well as the foreign key f1 the constraint does NOT create it own index! > > It is just as easy (generally) to make the programs check these > relationships. To get the best from FKs, I suggest that you should use as > many foreign keys as you need in a development database, but you should You mean like Oracle Financials which I heard has hundreds of tables and only 3 foreign keys even though Oracle recommend using foreign keys? > remove the bad FK and index definitions from production databases. If you do > adequate testing in development then any software faults should be caught > before it ever gets to production. At the very least, the existence of the > FK in development is a very strong hint to the programmers that they need to > validate the state field. The indexes should be used anyway. When adding a row in the foreign table using the index on the primary. When deleting (or flagging as disabled) a row in the primary check for no references in the foreign. > > > 4. If i am using 4 columns in one query which runs very frequently, > > and one of the same column i use in another qury, and another column > > in another query. In this case is it better to create a composite > > index on all 4 columns , and separate indexes on each column? > > If an index exists on columns (A,B,C,D) then the engine can use the index on > queries that refer to (A,B,C,D) or (A,B,C) or (A,B) or (A) columns. So I try > to order (A,B,C,D) in a way that can be useful for other queries. If you > also want to query by column C only, then you may need an index on (C). > > On the other hand, the engine can do other query paths that don't > necessarily need indexes. This applies especially when you are selecting > large parts of the tables involved in the joins. The engine can either make > a temporary index, or perform a sort operation at Just The Right Time, or it > can perform a hash-join. A temporary index (autoindex path) is usually a very bad idea!