RE: Storing single numbers in the database
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: "Buchanan, Jason" <oracle@xxxxxxxxxxxx>
- Date: Wed, 6 Jun 2007 11:09:38 -0400
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@xxxxxxxxxxxxxxx <mailto:mark.bobak@xxxxxxxxxxxxxxx>
www.proquest.com <http://www.proquest.com/>
www.csa.com <http://www.csa.com/>
ProQuest...Start here.
________________________________
From: Buchanan, Jason [mailto:oracle@xxxxxxxxxxxx]
Sent: Tuesday, June 05, 2007 6:28 PM
To: Bobak, Mark
Cc: sbecker6925@xxxxxxxxx; 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
Other related posts:
