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

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to generate alpha-numeric sequence in Oracle?

Re: How to generate alpha-numeric sequence in Oracle?

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 02 Nov 2006 08:50:52 -0800
Message-ID: <1162486251.367625@bubbleator.drizzle.com>


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.org
Received on Thu Nov 02 2006 - 10:50:52 CST

Original text of this message

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