Re: No future for DB2 - slightly off-topic, discusses what people are being taught at uni

"rkusenet" <rkusenet_at_hotmail.com> wrote in message news:3l3q1gF10mdvsU1_at_individual.net...
> The project I am working on has been developed by UK folks and we are
> customizing it for this Canadian customer. I too got a feeling that those
> guys are experts only in SQL Server.
> Their concept of a RDBMS seems to be weak. For e.g. in SQL Server
> one can create a Foreign Key without creating an index. In fact index
> creation on the FKY columns is a separate process. Same in Oracle
> and DB2. What the UK folks never realized is that almost all the time
> a FKY column is joined with PKY column for Query (otherwise why
> would it be a separate table). So creating index on the FKY column
> should be automatic when creating the FKY constraint.
> When I joined the project, one of the first fire I had to fight in the
> testing phase was locking. SQL server use to do table scan when it
> can't find matching index and that pretty soon escalated into locking
> and deadlock problem.
> ISOLATION LEVEL - well most of them don't even know what it is,
> let alone it is.
>
> I am beginning to wonder whether being a full time DBA is a
> dying profession left for old fogies like me.
>

The subject of foreign keys and indexes has been discussed in the Oracle newsgroup recently.

Not all foreign keys need (or should have indexes). Some foreign keys are merely connected to code tables that are used to make sure a valid value is used, and they are never joined. The example used in the Oracle thread is division_code on sales transaction table that relates to a division_code table with only 3 rows (divisions).

Having an index on the foreign key for division_code would slow down inserts on the sales transaction table, and would never be used for queries (cardinality of 3 is too low for a RDBMS to use this index for queries), except for the extremely unlikely event of someone trying to change or delete a row in the division_code table.

So creating an index on a foreign key should not be automatic. Received on Sun Jul 31 2005 - 07:38:16 CDT