From oracle-l-bounce@freelists.org Thu Feb 17 19:45:46 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j1I1jk1W002511 for ; Thu, 17 Feb 2005 19:45:46 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j1I1jhem002505 for ; Thu, 17 Feb 2005 19:45:44 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8A60372223; Thu, 17 Feb 2005 19:43:31 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 27434-10; Thu, 17 Feb 2005 19:43:31 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1163D732EF; Thu, 17 Feb 2005 19:43:31 -0500 (EST) User-Agent: Microsoft-Entourage/10.1.4.030702.0 Date: Thu, 17 Feb 2005 17:42:48 -0700 Subject: Re: Exhaused sequence From: Tim Gorman To: "'Oracle-L@freelists.org'" Message-ID: In-Reply-To: <17ECCBDCF27C544583F2CAD928F953260221FB1C@memex1.corp.cefs.int> Mime-version: 1.0 Content-type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit X-archive-position: 16354 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: tim@evdbt.com Precedence: normal Reply-To: tim@evdbt.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=AWL autolearn=ham version=2.60 X-Spam-Level: Jon, Will they accept hex? :-) Just kidding... It's Y2K all over again, eh? With only 1,000,000 possible values (assuming 0 is included), how bad could it get? You can't beat it for simplicity, and what's the worst that could happen? A million failed tries at inserting a row? Of course, you'd want to make sure that it'll stop at some point and not keep circling endlessly... The users will just have to be prepared for poor performance on inserts. Maybe that will help them make up their minds? Another possible option (as opposed to changing the application code to use a stored function) is perhaps create a BEFORE INSERT FOR EACH ROW trigger on the table that will test the value being attempted, using a SELECT command. If that unique value already exists in the table, then have the trigger do the NEXTVAL->try/reject logic, so that the :NEW record will have the right value when the INSERT actually happens? Don't know if it'll work, but maybe it'll save you having to modify application code? That way, when they give the OK to expand the width of the column, you can just reset the sequence and get rid of the trigger, instead of doing another application code change. ...on the other hand, maybe hiding the problems from them is NOT the best way to handle this...? Let 'em savor the pain... Best of luck! -Tim on 2/17/05 2:55 PM, Knight, Jon at jknight@concordefs.com wrote: > We have a sequence that is about to reach it's maximum value (999999). > Ordinarly, I would just expand the column and let it keep going, but it > populates a business column. They don't care what the value is, as long as > it's unique and no longer than 6 digits. Of course, like many sequence > populated columns, it's not consecutive. > > I'd like to go back and fill in those "gaps" while the business users > decide what they want to do. So, I'm thinking: reset the sequence to zero & > create a function that calls nextval until it finds one that's available. > Besides a performance hit, are there any other gotchas I'm missing? > Scalability? > > Has anyone done something similar before? > > Thanks, > Jon Knight > -- > http://www.freelists.org/webpage/oracle-l > -- http://www.freelists.org/webpage/oracle-l