Re: Storing single numbers in the database

The numbers will be stored as characters if they aren't stored in a NUMBER datatype column. Any type of sort operation (looking for a range of rows, etc.) against these columns stored in char/varchar2 datatype columns forces the database engine to silently convert the characters into alphanumeric values and perform the sort on the converted values (very inefficient and consumes memory). The performance suffers a great deal. The only reason this bad practice seems to work is the hard work Oracle developers did to write an efficient sorting algorithm to deal with this (unfortunately common) type of operation. But even as good as the conversion is, a native numerical sort of numbers in a NUMBER datatype column is far superior.

Zipcodes are a notable exception... 5-digit zipcodes reference an entity rather than a numerical value so they usually are not handled efficiently when stored as a NUMBER. Columns that are involved in any type of mathematical operation are prime candidates for NUMBER (i.e., no one adds two zipcodes together).

hope this helps.
jason

On Jun 5, 2007, at 3:04 PM, Sandra Becker wrote:

> Paul,
>
> The developer believes that numbers are just characters, same as
> letters, ergo they are treated the same way in the database. I
> know that the values are stored with different types, but beyond
> that, I don't know what issues could bite me. I have seen code for
> other columns defined as CHAR or VARCHAR2 that use <, >, and <> to
> pull the desired rows. Would this be relevant to the discussion as
> well?
>
> Sandy
>
>
> The developer doesn't want to translate? What, he thinks 0 and 1
> are letters?
>

--
http://www.freelists.org/webpage/oracle-l