Re: Exhaused sequence
- From: Tim Gorman <tim@xxxxxxxxx>
- To: "'Oracle-L@xxxxxxxxxxxxx'" <Oracle-L@xxxxxxxxxxxxx>
- Date: Thu, 17 Feb 2005 17:42:48 -0700
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@xxxxxxxxxxxxxx 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
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
