Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: automatically calculating ID field on INSERT

Re: automatically calculating ID field on INSERT

From: Shane Petroff <spetroff_at_hayward-and-associates.com>
Date: 2000/06/25
Message-ID: <395652B3.FC2DBDCD@hayward-and-associates.com>#1/1

Joe Celko wrote:

> UPDATE HighestId
> SET id = id + CAST (RANDOM(id) * 100 AS INTEGER);

Since you seem to be attempting to distribute your surrogate key values, I was wondering if anyone had utilized an algorithmic approach. The formula below is intended to distribute key values evenly throughout a 31bit address space (the 31bit alg. is simpler that the full 32bit range and needn't worry about overflow).

UPDATE id_table
SET id = id/2+mod(mod(id,2)+mod(id/8,2),2)*2**30;

SELECT id FROM id_table;

(from "Key Points About Surrogate Keys" Roy Hann)

While the formula looks ugly to a human, a quick benchmark indicates that the calculation overhead is negligible even on a small pc.

--
Shane
Received on Sun Jun 25 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US