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

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 31 Jul 2005 11:58:10 -0700
Message-ID: <1122836268.839070@yasure>

Mark A wrote:

> "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. 

A syntax such as the following would solve the problem:

ALTER TABLE tab1
ADD CONSTRAINT fk_tab1_col1
FOREIGN KEY (col1)
REFERENCES tab2(col2)
USING INDEX; Giving the power to decide whether to index, or not, to the database professional. It needn't be an all, or nothing, syntax.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Sun Jul 31 2005 - 13:58:10 CDT