Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, inan.

Asked: August 06, 2010 - 8:02 am UTC

Last updated: August 26, 2010 - 1:26 pm UTC

Version: 10,2,0,4

Viewed 10K+ times! This question is

You Asked

can u compare indexes below;

varchar2(19) vs char(19)
varchar2(255) vs varchar2(19)
number(19) vs varchar2(19)
number(19) vs char(19)

and Tom said...

"U" isn't available, "U" is dead as far as I know. Look it up, it is true.

http://en.wikipedia.org/wiki/U_of_Goryeo



See http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2668391900346844476

for a talk about char versus varchar2 - just use varchar2 and never ever use char.

So, we are down to number versus varchar2. Since a number in Oracle is just a varying length string that represents a number (it is like a packed decimal number) - it starts with a null indicator followed by a length byte followed by the number data - it is just like a varchar2, which (just like a char) starts with a null indicator followed by a length byte followed by the data.

And since you never put strings into numbers (they do not fit) and we all know you never put numbers in strings - the discussion boils down to this simply:


WHAT DATA DO YOU NEED TO INDEX.


Your data dictates the data type - if you have numbers, you WILL use numbers. If you have strings - you WILL use strings. After that - it just is a question "do I need to index that data or not"

That is where the discussion begins and ends.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

char vs varchar

A reader, August 06, 2010 - 3:20 pm UTC

Why was char datatype introduced in the first place, if it is of not much use and varchar datatype can serve the same purpose in a better way ?
Tom Kyte
August 09, 2010 - 12:28 pm UTC

Because we wanted to comply with a standard and the standard said "have char" I suppose.

It is just part of the language.

RE: Why char and varchar

Duke Ganote, August 09, 2010 - 11:29 am UTC

Both CHAR and VARCHAR data types are SQL standard. Oracle's implementation makes VARCHAR2 the generally superior choice. It's what I use.

Some other DBMSs, which are in my opinion inferior or primitive, may perform better using CHAR because they are based on simple, fixed-width files and only begrudgingly accept VARCHAR datatypes.

indexes number(19) vs varchar2(19)

new to oracle, August 26, 2010 - 12:26 am UTC

I am new to oracle and was really surprised to see that there is no difference between number and varchar2 indexes.

I have been working with MySQL, even though is a primitive DB compared to Oracle and PostgreSQL, for the last 10 years and there, they clearly recommend string indexes over number indexes as sorting and searching is more effective on strings than the numbers.

I guess the difference is due to the fact that numbers in Oracle are saved as strings.
Tom Kyte
August 26, 2010 - 1:26 pm UTC

sorting and searching on strings is more effective (think you mean efficient)????

really - conventional wisdom (and experience) would dictate the opposite be true if anything. Numbers are usually (not in Oracle) stored in a 2 or 4 byte format - which search and sort much easier than a string. The logic is almost always reversed - everyone things NUMBERS are better than STRINGS.



The difference is due to the fact that mysql and Oracle are different databases. sqlserver would be different from both. db2 from all three. and so on. different software = different rules to the game.

Please, don't store numbers in strings.

Shannon Severance, August 26, 2010 - 3:02 pm UTC

> they clearly recommend string indexes over number indexes as sorting and searching is more effective on strings than the numbers.

I'm not sure how searching or sorting numbers in a string is more effective since it will often return the **wrong** results, see for examples:
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:524526200346472289#2675641900346653561
http://stackoverflow.com/questions/3527977/i-am-unable-to-sort-data-in-ascending-order-using-order-by-asc

U is dead

A reader, March 02, 2015 - 9:58 am UTC

haha !! good one
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.