RE: Storing single numbers in the database

Hi Jason,  

First off, the spelling error (alphanumberic) was mine, not yours! You spelled it correctly in the original mail, so, no that's not what I was getting at. ;-)  

My point was (and still is) that I was not aware of any conversion of "characters into alphanumeric values and perform the sort on the converted values". In my quick testing yesterday, I was not able to notice any discernable performance difference between sorting a varchar2, char, or number column.  

If you do find a reference to that paper, I'd be interested in seeing it.  

Thanks!  

-Mark      

--

Mark J. Bobak
Senior 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: Buchanan, Jason [mailto:oracle_at_digistar.com] Sent: Tuesday, June 05, 2007 6:28 PM
To: Bobak, Mark
Cc: sbecker6925_at_gmail.com; Baumgartel, Paul; oracle-l Subject: Re: Storing single numbers in the database

On Jun 5, 2007, at 5:45 PM, Bobak, Mark wrote:

        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?                  

I was in a rush (like I am now) while replying and had Numbers on the brain. I meant to say "alphanumeric" (without the "b").          

        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.                  

That's the point I was trying to make earlier..... it was a leap of faith assumption on my part that the original poster's point was that this might happen. I've had to deal with developers who stored numbers in a varchar2 column and wondered why the sort performance of the numbers was poor ("it must be oracle's fault" mentality). Sandy's original post gave me the impression that her developer was of the same ilk; rather than put the effort into the application he is "throwing it over the wall" to the database engine to do the work. By itself these operations are small but in a large environment they add up quickly. In a multi-character database (UTF8) the sorting performance is considerably slower.          

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

Later tonight i'll have to dig up Wolfgang's paper on numeric sorts of data stored in character-based datatypes. The method Oracle uses to perform a numeric sort of numerical data stored in varchar2 fields is well documented in his paper.

thanks,
Jason

--

http://www.freelists.org/webpage/oracle-l Received on Wed Jun 06 2007 - 10:09:38 CDT