Re: Using SYSDATE as part of Primary Key?

Hi!

I don't think it's a good idea.

SYSDATE would not have any problems, but you most likely will. SYSDATE, as any other Oracle server DATE has a 1 sec. resolution. There is no (theoretical) restriction on how many calls to SYSDATE you may have within one second, hence it is possible for two sessions to get the same value.

The likelyhood of this (a collission) happening during a 8-hour period is greater than 50% if your transaction rate is higher than 200 transactions per 8 hours. You don't need many users to create that little data!

The result of a collission is not serious. The database will not accept two records with the same primary key, so the user will have to try again (with a new ENTERED_DATE, if this is generated in the client!). The likelihood of a second collission is small.

But why not use a sequence instead, and just have ENTERED_DATE as an attribute? This way, you will avoid the problem completely. If it's vital to get quick access to specific ENTERED_DATEs (or a range of ENTERED_DATEs), you can put a non-unique index on it.

HTH, Roy Brokvam
roy.brokvam_at_conax.com Received on Wed Feb 24 1999 - 10:17:50 CST