RE: Storing single numbers in the database

Um, Jason, what are you talking about, with regard to "...forces the database engine to silently convert the characters to into alphanumberic values and perform the sort on the converted values..."?? What does that even mean?  

Just to prove this to myself, I just did some testing, creating a table like this:
create table test_sort(numerical_values number(10), varchar_values varchar2(10), char_values char(10));  

and then inserted 1,000,000 rows, using dbms_random to generate random values.  

I then executed:

select * from test_sort order by numerical_values;
select * from test_sort order by varchar_values;
select * from test_sort order by char_values;
 

And I saw no difference in performance, after caching. (The first select was executed twice and the first result was ignored.)  

Of course, as previously discussed, if you have a non-numerical column, and compare to a numeric literal or bind value, such as: select * from test_sort where varchar_values = '1';  

Then this will cause an implicit data conversion, and prevent a possible index access path.  

However, I know of no "conversion" required when sorting a non-numeric datatype.  

-Mark
--

Mark J. Bobak
Senior Oracle Database Administrator, System & Product Technologies ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346 Ann Arbor MI 48106-1346
734.997.4059 or 800.521.0600 x 4059
mark.bobak_at_il.proquest.com <mailto:mark.bobak_at_il.proquest.com> www.proquest.com <http://www.proquest.com/> www.csa.com <http://www.csa.com/>

ProQuest...Start here.  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Buchanan, Jason Sent: Tuesday, June 05, 2007 4:10 PM
To: sbecker6925_at_gmail.com
Cc: Baumgartel, Paul; oracle-l
Subject: 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 Received on Tue Jun 05 2007 - 16:45:19 CDT