Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

GETTING NEXT AVAILABLE INDEX

Status
Not open for further replies.

piterskiy

Technical User
Aug 24, 2007
8
US
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
 
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
 
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.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top