Re: Storing single numbers in the database

  • From: "Buchanan, Jason" <oracle@xxxxxxxxxxxx>
  • To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
  • Date: Tue, 5 Jun 2007 18:27:52 -0400


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




Other related posts: