Q

Creating a random value for the primary key

When we add a new record in the table, how can we insert a random value for the primary key?

There are many ways to do this, all of which have some "cost" associated with them.

The simplest way to do it uses "brute force" from the storage perspective in order to make the process fast and relatively foolproof. First, create a "candidate key" table containing only one column for the primary key. Each time you need a new key, pick one of these keys at random and delete it from the "candidate" key table. It doesn't really matter how random the selection method is, since removing candidates as you use them will effectively "stir" the randomizer with each use. This method has the advantage of being simple to understand, easy to code, and tough to break!

Another method uses similar logic. Each time you need a new key, try to insert a random key value. If the insert fails, try again. This is also simple to code, but it is non-deterministic: you never know how long it will take to find a new key, and it gets slower as more keys get used.

You can improve on the previous idea by using statistical information kept in another table to guide the selection of new keys. This can produce much better results much faster, and it also allows you to "doctor" the key distribution if necessary.

Just as an observation: the use of random keys was once very important in making databases perform well. The problems associated with sequential key generation have been solved in nearly every major database engine. Unless there is some strong reason that requires you to use randomly distributed key values, I'd recommend using something simpler!

For More Information


This was first published in December 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close