People often create far more “foreign key” indexes than are needed – and any redundant index is a waste of several types of resources. I’ve just made some comments on an OTN thread about this topic, so rather than repeat the comments here I’ll just give you the URL.
Update 5th Nov 2009: and here’s another common issue about foreign keys that I also addressed by a link to OTN some time ago.
Oracle Scratchpad 
I absolutely agree that there are cases where it is not necessary to index foreign keys. Your advice on that front is absolutely spot on.
I am curious, though, whether you regularly find systems in the real world with too many indexes on foreign keys. My experience has been that people are far more likely to suffer performance problems because they’ve missed a foreign key index. I’m sure I don’t have a scientifically valid sample set, but I’ve dealt with far more problems where systems were spending far more time than necessary on deletes because of a missing index (particularly when someone expects that “we’ll never delete X” at design time and then someone decides 6 months later that X does need to be deleted). That’s why my bias is always to index foreign keys unless there is a compelling reason not to.
Of course, if every change to an OLTP system were thoroughly performance tested against realistic data volumes and every developer validated that the recursive SQL their DELETE statements issued were supported by indexes, it would be easy enough to verify that only the foreign key indexes that are strictly necessary are created, but that seems relatively unlikely to me in practice.
So on balance, the risk to me in an OLTP system seems heavily balanced to prefer generating 10-20% more foreign key indexes than you may actually need rather than debugging future performance problems because an index was missed. I’m curious if your experience leads you to the opposite conclusion.
Comment by Justin Cave — November 4, 2009 @ 6:00 am GMT Nov 4,2009 |
> That’s why my bias is always to index foreign keys unless there is a compelling reason not to.
That would be my experience and my rule of thumb as well.
I see more problems based on unindexed foreign keys than too many indexes.
Then again, I probably see even more problems based on no foreign keys…
Comment by dombrooks — November 4, 2009 @ 10:21 am GMT Nov 4,2009 |
An extreme example is packaged software. Becuase the developers don’t actually know each and every usage instance that will be encountered at customers – they ship with a “full set” of indexes. This goes beyond foreign key indexes and includes other columns as well. Looking at Siebel, they have thousands upon thousands of indexes that ship with the software. Granted many of these are on tables that go unused at most implementations – but even the “main” tables have a large number of indexes. It should be noted that on the “unusued” indexes, the tables typically have few if any records, and/or the columns of the records that are there typically contain NULL values.
I am aware of some Siebel sites that have trimmed this down – presumably because it made a difference. But the cost of doing so is high. You have to determine which ones are really necessary, navigate vendor support, drop them, and then keep them dropped. That last bit is important because a standard code migration will simply create the “missing” indexes that the software thinks should be there. (If you haven’t taken steps to prevent this.) For the vast majority of sites – the benefit of trimming the indexes is immaterial.
In my experience with Siebel, once the problems of missing indexes and bad plans are resolved – you hit the point of diminishing returns in terms of fiddling with indexes. Yes – there are sites where this is not true. But if you’re running one of those sites, you’re not just blindly following advice from the internet!
For non-packaged implementations, I suspect you have a similar dynamic the larger the development team. Going hog wild and indexing every column is surely not appropriate, but meticulously analyzing the purpose of each and every index is probably not worth the effort in every case either.
Comment by Joe Coffey — November 4, 2009 @ 4:56 pm GMT Nov 4,2009 |
Joe,
“Going hog wild and indexing every column is surely not appropriate, but meticulously analyzing the purpose of each and every index is probably not worth the effort in every case either.”
I think as a generic piece of advice for handling any Oracle system that pretty much says it all: deal with the extreme cases, and everything else will look after itself. (The trick is being able to recognise a special case when you see it ;) )
Comment by Jonathan Lewis — November 5, 2009 @ 9:28 pm GMT Nov 5,2009 |
Justin,
In general, I think I probably see quite a number of places which have redundant (typically “foreign key”) indexes. This tends to show up as indexes that are exact matches for foreign keys combined with indexes that start with the foreign key.
I don’t tend to see many places which have performance problems because of missing “foreign key” indexes – perhaps because such problems are often fairly easy to spot (especially the locking one) and have probably been fixed before I get there.
A “missing index” phenomenon I do tend to see fairly frequently is the absence of multi-column indexes. Although recent versions of Oracle make it viable to survive on a combination of single column indexes, I frequently saw cases in the past where the only indexes on a table were single column indexes (which usually could have been compressed but weren’t) when a few multi-column indexes would have been more appropriate.
As a side note – do you see many people taking advantage of index compression for foreign key indexes; it’s often a very suitable mechanism to employ, but in my experience doesn’t seem to be used much.
Comment by Jonathan Lewis — November 5, 2009 @ 9:20 pm GMT Nov 5,2009 |
I would say that I think DBAs has a tendency to create all foreign keys they know they need, primary keys on all tables, and be too restrictive on other indexes. Systems that developers has designed the indexes in (yes they do exist, especially in the CRM space) tends to have too many indexes. I’m currently working on an implementation where the 10 most indexed tables has between 60 and 95 indexes. As the initial load as done with all indexes created on the empty tables, it took a while for very modest volumes of data.
Comment by Mathias Magnusson — November 4, 2009 @ 8:44 am GMT Nov 4,2009 |
Mathias,
Yes, that’s a phenomenon I run into from time to time. It’s always very hard to persuade anyone to take the risk of dropping indexes which are probably excess to requirements.
Thank goodness for 11g, which allows you to make indexes invisible for a test period – but watch out for the comments by Richard Foote and Christian Antognini about invisibility and foreign keys.
Comment by Jonathan Lewis — November 5, 2009 @ 9:23 pm GMT Nov 5,2009 |