Re: Dumb Question regarding Indexes

"Mark A" <nobody_at_nowhere.com> wrote in message news:RaWdnY4_B7qjk3bfRVn-hg_at_comcast.com...
> <fitzjarrell_at_cox.net> wrote in message
>
>> And in DB2 this may not be an issue, however with Oracle unindexed
>> foreign keys create problems you can read about here:
>>
>> http://asktom.oracle.com/~tkyte/unindex/
>>
>> And, if you read the above link you'll see the expense of NOT indexing
>> is greater.
>>
>> I'm glad you're not working with Oracle, as you'd be replacing them for
>> performance reasons.
>>
>> David Fitzjarrell
>>
> I work with both Oracle and DB2. The article you cited in the link above
> reinforces exactly what I said about indexes on foreign keys (and when
> they are not necessary).
>
> Let's quote one point at a time from the article and then compare it to
> what I said.
>
> 1. "The [issue] first is the fact that a table lock will result if you
> update the parent records primary key (very very unusual) or if you delete
> the parent record and the child's foreign key is not indexed."
>
> Yes that is correct, but if the parent table is a code table, like
> division_code, it is not going to be updated or deleted on the parent code
> table except in very, very unusual circumstances.
>

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 Received on Sat Jul 30 2005 - 05:55:18 CDT