Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 13566 invoked from network); 6 Jun 2007 10:51:34 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 6 Jun 2007 10:51:34 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B47816EFB23; Wed, 6 Jun 2007 11:49:36 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 05340-01; Wed, 6 Jun 2007 11:49:36 -0400 (EDT) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1CC376EEC43; Wed, 6 Jun 2007 11:49:36 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 06 Jun 2007 11:08:57 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 25C716F121C for ; Wed, 6 Jun 2007 11:08:57 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 32586-06 for ; Wed, 6 Jun 2007 11:08:57 -0400 (EDT) Received: from mx02.il.proquest.com (mx02.il.proquest.com [192.195.245.52]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C125F6F0DEF for ; Wed, 6 Jun 2007 11:08:56 -0400 (EDT) X-ASG-Debug-ID: 1181142650-6ff7000a0002-JbYmij X-Barracuda-URL: http://spam.il.proquest.com:8000/cgi-bin/mark.cgi X-Barracuda-Connect: aabo-exchange05.bos.il.pqe[172.24.3.61] X-Barracuda-Start-Time: 1181142650 X-ASG-Whitelist: Client Received: from aabo-exchange05.bos.il.pqe (aabo-exchange05.bos.il.pqe [172.24.3.61]) by mx02.il.proquest.com (Spam Firewall) with ESMTP id 3CCEF1C9196; Wed, 6 Jun 2007 11:10:50 -0400 (EDT) Received: from AABO-EXCHANGE02.bos.il.pqe ([172.24.3.202]) by aabo-exchange05.bos.il.pqe with Microsoft SMTPSVC(6.0.3790.3959); Wed, 6 Jun 2007 11:09:41 -0400 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C7A84C.B5261988" X-ASG-Orig-Subj: RE: Storing single numbers in the database Subject: RE: Storing single numbers in the database Date: Wed, 6 Jun 2007 11:09:38 -0400 Message-ID: In-Reply-To: <24A78E69-31EB-4CC8-893F-F19521473EF6@digistar.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Storing single numbers in the database From: "Bobak, Mark" To: "Buchanan, Jason" Cc: , "Baumgartel, Paul" , "oracle-l" X-OriginalArrivalTime: 06 Jun 2007 15:09:41.0627 (UTC) FILETIME=[B55054B0:01C7A84C] X-Barracuda-Virus-Scanned: by Barracuda Spam Firewall at il.proquest.com X-archive-position: 49518 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: Mark.Bobak@il.proquest.com Precedence: normal Reply-to: Mark.Bobak@il.proquest.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------_=_NextPart_001_01C7A84C.B5261988 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi Jason, =20 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. ;-) =20 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. =20 If you do find a reference to that paper, I'd be interested in seeing it. =20 Thanks! =20 -Mark =20 =20 =20 -- 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@il.proquest.com =20 www.proquest.com =20 www.csa.com =20 ProQuest...Start here.=20 =20 ________________________________ From: Buchanan, Jason [mailto:oracle@digistar.com]=20 Sent: Tuesday, June 05, 2007 6:28 PM To: Bobak, Mark Cc: sbecker6925@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? =09 =09 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"). =20 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 =3D '1'; =20 Then this will cause an implicit data conversion, and prevent a possible index access path. =09 =09 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. =20 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 ------_=_NextPart_001_01C7A84C.B5261988 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Hi=20 Jason,
 
First=20 off, the spelling error (alphanumberic) was mine, not yours!  You = spelled=20 it correctly in the original mail, so, no that's not what I was getting=20 at.  ;-)
 
My=20 point was (and still is) that I was not aware of any conversion of = "characters=20 into alphanumeric values and perform the sort on the converted = values".  In=20 my quick testing yesterday, I was not able to notice any discernable = performance=20 difference between sorting a varchar2, char, or number=20 column.
 
If you=20 do find a reference to that paper, I'd be interested in seeing=20 it.
 
Thanks!
 
-Mark
 
 
 
--
Mark J.=20 Bobak
Senior Database Administrator, System & = Product=20 Technologies
ProQuest
789 E. Eisenhower, Parkway, = P.O. Box=20 1346
Ann Arbor MI 48106-1346
734.997.4059  or = 800.521.0600 x=20 4059
mark.bobak@il.proquest.com
www.proquest.com
www.csa.com

ProQuest...Start here.

 

From: Buchanan, Jason=20 [mailto:oracle@digistar.com]
Sent: Tuesday, June 05, 2007 = 6:28=20 PM
To: Bobak, Mark
Cc: sbecker6925@gmail.com; = Baumgartel,=20 Paul; oracle-l
Subject: Re: Storing single numbers in the=20 database


On Jun 5, 2007, at 5:45 PM, Bobak, Mark wrote:
Um,=20 Jason, what are you talking about, with regard to "...forces the = database=20 engine to silently convert the characters to into alphanumberic values = and=20 perform the sort on the converted values..."??
What=20 does that even mean?


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


 
Of=20 course, as previously discussed, if you have a non-numerical column, = and=20 compare to a numeric literal or bind value, such = as:
select * from test_sort where varchar_values =3D = '1';
 
Then=20 this will cause an implicit data conversion, and prevent a possible = index=20 access path.


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

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


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


thanks,
Jason




------_=_NextPart_001_01C7A84C.B5261988-- -- http://www.freelists.org/webpage/oracle-l