tag:blogger.com,1999:blog-16999808.post113943389203953769..comments2023-03-23T08:32:03.831-07:00Comments on Jordi L. Ramot's weblog: RDBMS - Foreign key indexingJordi Lopez Ramothttp://www.blogger.com/profile/17089956522641790841noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-16999808.post-9272547002812005472007-02-25T05:16:00.000-08:002007-02-25T05:16:00.000-08:00Thanks diego for expanding the subject with your e...Thanks diego for expanding the subject with your experience. I should have stated that I usually use PostgreSQL and have perceived a good behavior following the simple rule described in here. Hope someone else will answer your question about profilers for Oracle and DB2.Jordi Lopez Ramothttps://www.blogger.com/profile/17089956522641790841noreply@blogger.comtag:blogger.com,1999:blog-16999808.post-53919330650724544942007-02-09T12:37:00.000-08:002007-02-09T12:37:00.000-08:00I think the question is not only “to index or not ...I think the question is not only “to index or not to index” on foreign keys. <BR/>I have been debating this subject with my boss (a hardcore Informix believer) today. We found that Informix has always created indexes (that you actually cannot drop) on foreign keys automatically, but none of Oracle, DB2, and SQL Server do it.<BR/>So, why some engineers decided to go one way and others in the opposite? I see a very interesting design decision there.<BR/>Informix won’t give you a choice, taking responsibility in optimizing JOINs and CASCADING operations on the foreign key.<BR/>However, in many cases a composite index that is headed by the foreign key but also includes other columns relevant to frequent queries is superior to a mere foreign key index.<BR/>So, in Oracle, DB2 or SQL Server you can choose to create this kind of index (actually the responsibility of doing so is yours), which theoretically could give you better performance than Informix (obviously, my boss won’t swallow that pill!).<BR/>I think an important conclusion is that you should always tune your indexes globally, by using real profiling data. <BR/>Fortunately, those of us using SQL Server can count on the Index Tuning Wizard. Are there similar tools for Oracle and DB2?Diegohttps://www.blogger.com/profile/06211524340372500555noreply@blogger.com