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

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 Received on Thu Nov 02 2006 - 08:13:28 CST