Path: utzoo!attcan!uunet!zephyr!tektronix!tekcae!moiram From: moiram@tekcae.CAX.TEK.COM (Moira Mallison) Newsgroups: comp.databases Subject: Re: Attn: RTIngres GURUs Message-ID: <2899@tekcae.CAX.TEK.COM> Date: 29 Jun 89 17:19:58 GMT References: <449@sdcc15.ucsd.edu> <2981@rtech.rtech.com> <1389@rivm05.UUCP> Reply-To: moiram@tekcae.CAX.TEK.COM (Moira Mallison) Organization: Tektronix, Inc., Beaverton, OR. Lines: 23 In article <1389@rivm05.UUCP> llojd@rivm.UUCP (J.W. Diesel) writes: >scheme to insert unique values: > INSERT INTO yourtable (serial_column) > SELECT MAX(serial_column) + 1 FROM yourtable; > >Informix does not permit you to use the insert-table in the from clause, maybe >INGRES does. You may of course use a temporary table to store the maxvalue >but that would make you pay a considerable performance penalty... If the table has a heap or hash storage structure, the use of the max function will result in a sequential scan, which REALLY imposes a performance penalty on a table of any size. (This may also be true of the isam and btree structures, depending on how smart the query optimizer is. Dave?). I solved this problem by storing the next surrogate key value in the table with an artificial primary key value. ("DUMMY" in the name field in my case. I retrieve it with a direct access, increment it and update it. Moira Mallison Tektronix, Inc.