Path: news.easynews.com!core-easynews!newsfeed1.easynews.com!easynews.com!easynews!newsfeeds.sol.net!hammer.uoregon.edu!logbridge.uoregon.edu!newsfeed.stanford.edu!postnews1.google.com!not-for-mail From: Mark.Powell@eds.com (Mark D Powell) Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.marketplace,comp.databases.oracle.server,comp.databases.oracle.tools Subject: Re: Index and Foreign key Date: 28 Apr 2003 06:56:40 -0700 Organization: http://groups.google.com/ Lines: 30 Message-ID: <2687bb95.0304280556.31d5cbf5@posting.google.com> References: <3eac76c6.53752522@nntp.mindspring.com> NNTP-Posting-Host: 192.85.50.1 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1051538201 26333 127.0.0.1 (28 Apr 2003 13:56:41 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: 28 Apr 2003 13:56:41 GMT Xref: core-easynews comp.databases.oracle.misc:97058 comp.databases.oracle.marketplace:124761 comp.databases.oracle.server:184614 comp.databases.oracle.tools:58669 X-Received-Date: Mon, 28 Apr 2003 06:56:12 MST (news.easynews.com) willjamu@mindspring.com (James Williams) wrote in message news:<3eac76c6.53752522@nntp.mindspring.com>... > On Sat, 26 Apr 2003 08:01:58 +0000 (UTC), "Salim Harouat" > wrote: > > It is important to index all foreign key columns as a general rule > even though 9i adds some features to migitate the effects (ora-0060's) > of a parent causing the entire child table to be locked for the > duration of the operation. > > >Hi Everybody, > > > > I wish to optimize certain queries done on a Oracle database to gain > >in response times. With this intention, I plan to pose a non unique > >index on each foreign key of the base. Am i right ? > > > >Thank u for everything > > Salim, please do not cross-post. James, my experience says that the majority of FK constaints do not need an index to support them since they point to relatively static tables. Unless row deletes take place or updates on the key there will never be a need for the index to support locking. Probably only about 20% of FK constaints actually need an index to support efficient usage. There have been several threads on this topic in the past. IMHO -- Mark D Powell --