Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to generate alpha-numeric sequence in Oracle?
G Quesnel wrote:
> What you are proposing is not the worst I have seen, but you could
> improve performance by avoiding the call to a user function. If you
> check other threads in this forum you will see that your SQL statement
> take a performance hit when they must execute PL/SQL.
> Trying to build with your proposed solution...
> - if you have a second sequence then why not have it cycle back to 1
> after it reaches 26 (avoid the MOD function)
> - why not use just one sequence, generating the number part, and then
> also used with a MOD function to generate the alpha part
> - replace the user function by a straight decode function on the MOD
> function
> (native Oracle function will perform better then user PL/SQL)
>
> The other thing about performance is when mixing data types in index
> column, the CBO has less/wrong information and can create less then
> optimal plans (see Tom Kytes - Effective Oracle by Design - Chap7)
>
> hth
With this in mind here is a possible solution.
One sequence that is created as:
CREATE sequence leading_digits;
And a second created with MINVALUE 1, MAXVALUE 26, CYCLE used with the CHR function to convert the digits into letters A through Z.
I don't like it. But I don't like it primarily because I question a design that implements a system such as:
00001A
00001B
I'd really like to see the business case.
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Thu Nov 02 2006 - 10:50:52 CST