Problem solve Get help with specific problems with your technologies, process and projects.

Varchar or number for better performance?

Which one is better for performance as a unique index, varchar or number?

Which one is better for performance as unique index, varchar or number? I have peculiar requirements where my unique key is varchar(6) something like "006500," which is used in many other tables as foreign key. I was wondering which can give me better performance in this case -- defining one numeric unique ID number and using this as foreign key in other tables? Because in the last case i need to make two queries first to retrieve unique ID number and then on other table where it is foreign key to retrieve the value I need, while in the first case I can make query directly to the foreign key table.

In all my years of working with databases, I have never once worried about whether an index on VARCHAR2 or NUMBER columns performs better. If I have a number, and I will be performing some sort of numerical calculations on it, then I would never store this as a character string in the column. And unless I had a specific reason to do otherwise, I would store the value as a NUMBER datatype even if I am not performing calculations on it. Many times I have gone into database design thinking I was not going to perform calculations on a field and then had to change that idea in the future. I would store your data as a NUMBER because that is the proper datatype for the data. But I still am not thinking of performance reasons here. Any performance differences are likely to be so miniscule as to go unnoticed.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.