
	Home > Ask the Oracle Experts > Database Design and Architecture Questions & Answers > Varchar or number for better performance?

Ask The Oracle Expert: Questions & Answers

EMAIL THIS

Varchar or number for better performance?

EXPERT RESPONSE FROM: Brian Peasland

		Pose a Question

		Other Oracle Categories

		Meet all Oracle Experts
		Become an Expert for this site

Oracle tips, scripts, and expert advice

Digg This! StumbleUpon Del.icio.us

QUESTION POSED ON: 27 August 2007
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.

EXPERT RESPONSE
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.

Digg This!

StumbleUpon

Del.icio.us

RELATED CONTENT

	Database Design and Architecture
	Why am I getting an Oracle error when issuing the RECOVER DATABASE command?
	Can I add multiple datafiles and make use of all the temporary datafiles at the same time?
	What's the best tape backup strategy for 10g RAC?
	Checkpoint vs. commit process
	How does an INSERT/UPDATE statement behave when executed compared to a SELECT statement?
	Can I resize the SYSTEM tablespace?
	What is the difference between an instance and mounting in Oracle?
	How to implement a ref cursor called in a stored procedure
	In Oracle, does the number of columns in a table affect the performance during SELECT/INSERT/UPDATE?
	Allocating bytes as extents in Oracle

Oracle database performance problems and tuning

Oracle 11g data compression

Do statistics on SYS-owned objects hurt performance in 10g?

Inside the Oracle 11g SQL Performance Advisor, part 1

Inside the Oracle 11g SQL Performance Advisor, part 2

Difference between driving table and driver table in Oracle

Best design for E-Business Suite on hard drive

20GB data dictionary causing performance problems

Using the cost-based optimizer to improve Database 10g performance

Online tablespace reorganization in Oracle 9i

How to read a STATSPACK report

Oracle database design

Weighing remote database administration pros and cons takes care

Oracle Database 11g makes waves at Burlington Coat Factory

How to create a database link in Oracle

Data modeling tools no substitute for hard work

How do I do that in Oracle?

The Oracle Database user's guide to Oracle OpenWorld 2007

Oracle OpenWorld 2007 Special Report

How many redo log files?

How to move tables from system tablespace to user tablespace

ORA-12560 error with Oracle 10g Instant Client

RELATED GLOSSARY TERMS

Terms from Whatis.com − the technology online dictionary

extent (SearchOracle.com)

field (SearchOracle.com)

flexfield (SearchOracle.com)

foreign key (SearchOracle.com)

quad tree (SearchOracle.com)

record (SearchOracle.com)

row (SearchOracle.com)

splay tree (SearchOracle.com)

RELATED RESOURCES

2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems

Search Bitpipe.com for the latest white papers and business webcasts

Whatis.com, the online computer dictionary

Search and Browse the Expert Answer Center
Search and browse more than 25,000 question and answer pairs from more than 250 TechTarget industry experts.

Browse our Expert Advice
Operating Systems IT Management Networking Database	Security Servers and Storage Applications and Development Career Center

SEARCH

TechTarget provides enterprise IT professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective IT purchase decisions and managing their organizations' IT projects - with its network of technology-specific Web sites, events and magazines.


TechTarget Corporate Web Site \| Media Kits \| Reprints \| Site Map All Rights Reserved, Copyright 2003 - 2008, TechTarget \| Read our Privacy Policy