Q

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.

This was first published in August 2007

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close