Re: all foreign key should have index?

Mark D Powell wrote:
> Only indexes used to support PK and UK constraints can be determined by
> querying dba_constraints directly. The indexes that exist to support
> FK constraints, if they exist, are not available without doing a little
> more work. Unfortunately, the Reference manual (9.2)does not list the
> fact the index_name is only populated for PK and UK constraints.
>
> Queries to find these indexes have been posted in the past. Try
> searching the archives.
>
> HTH -- Mark D Powell --
>

Mark, with all due respect, you do not
address the "should there be" or "is this bad" issue.

The the OP: as always: it depends. Has been addresses before.

No, there is no law that there should be indexes on FK columns. But, consider locking mechanisms Oracle uses. Without the index, table locks are used, with an index, row (or block - forget which) level locks. That's good, improves scalability. In the odd time, the reference table (the one with the index on it) is updated, it means there's an extra index to update. That's bad. However, that table, being a reference table, hardly ever gets updated, so there's some mantra saying "index every FK column".

It's up to you to decide whether you should for each and every table (will an index on a 20 record table help?!?).

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...