Followup: Sequence Numbers as Primary Keys -- More Information

Thanks to everyone who has responded so far. The IT group has compiled more information. But first, a little more background information is necessary.

The project is designed to track/display/plan events in a fast moving environment. Most of the tables do not change very often. A few are updated constantly from TPF/IMS sources as the events take place. Approximately 180 users have to be notified when an update takes place. The IT group has already devised a clever little mechanism to automatically send a message to the users when interesting data comes in. (which BTW, has yet to be approved by the DBAs).

The following points were discussed:

1. Speed/Efficiency

IT group did speed tests of handle objects within their C++ program with the handle objects mirroring the PK of the database. This was not done to time the speed of Oracle, but the speed of the program (I can elaborate more if you wish -- send e-mail). The results of doing 6 million compares with sequence numbers was 13 seconds. With the most expensive composite key (6 elements -- 4 varchar2, 1 date, 1 number), it took 4 minutes 38 seconds. The program is estimated to do 36 million comparisons a day.

2. Insulation from changes in the primary key

IT group claims that using sequence numbers as PK positions them for change. IF PK ever changes, the data distributor would not have to change at all, and the C++ class that contained the changed object would have to be updated. Using composite keys, changes would have to be made to the coding/parsing routines on data distribution as well as changes in the handle objects and the class itself -- not even mentioning the DB changes.

DB group claims that any change would result in modifications to the database and the program anyway.

3. Coding/Development time would be shorter. Less complex code, easier to maintain -- All non-quantifyable right now.

The DBA group claims that they have followed their rules and guidelines in the design of the database. If IT doesn't approve, then don't expect any support from them.

Therefore the question now is ... Is this still worth pursuing? The cost would be to hire IT people who specialize in Oracle DB. The system is critical and has to be up 24x7, so it wouldn't be wise to have only one person support it. There may be some other projects which would require databases, but I don't know if there is enough work for full-time IT DBAs ...

Thanks again for any help,
Joel Hagans
jhagans_at_telerama.lm.com Received on Tue Sep 26 1995 - 00:00:00 CET