With this blog entry I start a new series discussing bad database standards. Almost every DBA group keeps a database standards manual - but most do not keep it up-to-date. I'll try to tackle some of the more popular standards that should be done away with.
Just about every company with a DBMS has that binder full of corporate and/or IT standards. You know, it is that one over there in the corner with the cobwebs on it? The one that you only use when you need an excuse to avoid work… OK, well, maybe its not quite that bad.
Basically, what happens is that some well-meaning authority comes up with a “rule” or “guideline” that makes sense at some point – and then decides to enshrine it for eternity in the standards manual. Now don't get me wrong, company standards can be a very good thing. It is the eternity part that I take exception with. Standards need to be a living, breathing "thing" that change with the times.
You see, standards can be worthwhile as a measuring stick to work from, hopefully ensuring that reliable and efficient databases and applications are built in a standard manner. But a rule that made sense 10 or 20 years ago probably is no longer reasonable. Every standard in your book should be reviewed at least annually to determine whether it is still reasonable to enforce.
One such rule is today’s topic, and if a form of it still exists in your standards manual drop everything you were going to do today and expunge it immediately from your standards book.
There should be no arbitrary limit on the number of indexes that you can create for any database table. Indexes are undoubtedly one of the most important factors in creating efficient queries. Relational optimizers rely on indexes to build fast access paths to data. Without indexes data must be scanned – and that can be a long, inefficient means by which to retrieve your data. When a rule such as this exists, it usually is stated in the standards manual using verbage something like this:
- “Each table can have at most 5 indexes created for it” - or -
- “Do not create more than 3 indexes for any single table in the database.”
These are bad standards. Very bad standards. Horrible standards.
If you already have 3 indexes, or 5 indexes, or even 32 indexes, and another index will improve performance why would you arbitrarily want to avoid creating that index?
Anyway, a good indexing standard, if you choose to have one, should read something like this: “Create indexes as necessary to support your database queries. Limitations on creating new indexes should only be entertained when they begin significantly to impede the efficiency of data modification.”
Now that is a good standard!
But most standards do not read that way because they are not easy to impose without arbitrary numbers and restrictions embedded within them.
I intend to take on other nasty standards that should be eliminated in subsequent blog entries here. Please feel free to e-mail me your favorites (or perhaps I should say least favorites) standards that should be eliminated. Or leave a comment below. I’d be happy to take them on here for you.