RE: Storing single numbers in the database
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle@xxxxxxxxxxxx>, <sbecker6925@xxxxxxxxx>
- Date: Tue, 5 Jun 2007 17:45:19 -0400
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@xxxxxxxxxxxxxxx <mailto:mark.bobak@xxxxxxxxxxxxxxx>
www.proquest.com <http://www.proquest.com/>
www.csa.com <http://www.csa.com/>
ProQuest...Start here.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Buchanan, Jason
Sent: Tuesday, June 05, 2007 4:10 PM
To: sbecker6925@xxxxxxxxx
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?
Other related posts:
