"Matthias Hoys" <idmwarpzone_NOSPAM__at_yahoo.com> wrote in message
news:42eb5c96$0$12049
> Mmm ... from my experience, I would like to disagree. For the DBA, it's
> often not clear which tables are "static" code tables and which ones are
> highly dynamic. If you have hundreds of table to manage in a schema,
> selectively creating indexes on FK columns can be an administrative
> nightmare. Also, applications can change over time (again, often without
> the DBA knowing so). I give the example of some batch that is started in
> the weekend to reload the code table. Without indexes on the FK columns of
> the child tables, it will take hours to complete, people will start
> complaining, and everyone will blame the DBA ... It's true that indexes
> slow down insert/update/delete statements - but if there are so many
> indexes on a table that this leads to *significant* performance problems,
> then there's something wrong with the design of your db. And for batch
> jobs, it's always possible to drop the indexes or make them UNUSABLE and
> set skip_unusable_indexes to true followed by an index rebuild in
> parallel.
>
> Matthias
>
Yes, it does take some work to sit down and figure out which FK indexes are
worthless (usually the ones which refer back to low cardinality code
tables), but their is a payoff for doing that (better performance for
inserts, and deletes (and less often dates) of the dependent table. We get
paid to make those decisions, and to come up with the optimum physical
database design.
Having too few indexes is a terrible thing for performance, but so is having
too many indexes also bad for performance.
Received on Sat Jul 30 2005 - 07:40:22 CDT
