GETTING NEXT AVAILABLE INDEX

[piterskiy]

I have the following query to get my next available number from the table:

SELECT MIN(INT(A.CODE) + 1)
FROM TABLE_CODE A
WHERE INT(A.CODE) NOT IN (
SELECT INT(B.CODE) -1
FROM TABLE_CODE B
WHERE INT(B.CODE) = (INT(A.CODE) + 1))

It take about 15 sec to execute it.
Is there any way to increase the performance of that query?

Thank's

 

[fredericofonseca]

Remove the INT function from the SQL. If you have to use them, then thats a poor table design for a numbering table. CHANGE IT.

Also, and if you are using v8+, consider using SEQUENCES for your numbering.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[piterskiy]

Thank you for your reply, Frederico

The reason I'm using INT is that the CODE field is a character field in a table. That's the business rules since a long long time ago.
Unfortunately, I need to work around that problem.

Eugene.

 

[MarcLodge]

Eugene,

What sort of values do you have in the table? Are the number all consecutive or are you attempting to fill in holes?

Marc