Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!prodigy.com!pd7cy2so!pd7cy1no!shaw.ca!pd2nf1so.cg.shawcable.net!residential.shaw.ca!sn-xit-03!sn-xit-01!sn-post-01!supernews.com!corp.supernews.com!not-for-mail
From: Daniel Morgan <damorgan@x.washington.edu>
Newsgroups: comp.databases.oracle.server
Subject: Re: domain type question/mystery
Date: Fri, 10 Oct 2003 11:54:05 -0700
Organization: ATS
Message-ID: <1065812056.429779@yasure>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax)
X-Accept-Language: en-us, en
MIME-Version: 1.0
References: <YzChb.9297$T14.6786@newssvr29.news.prodigy.com>
In-Reply-To: <YzChb.9297$T14.6786@newssvr29.news.prodigy.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Cache-Post-Path: yasure!unknown@pond178.drizzle.com
X-Cache: nntpcache 2.4.0b5 (see http://www.nntpcache.org/)
X-Complaints-To: abuse@supernews.com
Lines: 59
Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:245071

old dirtbeard wrote:

> A question for one of you 9i experts.  I have been doing some 
> performance testing:
>
> 7.5 million records
>
> 4 tables with one column defined as:
>  char(10)
>  varchar2(10)
>  number(10)
>  number
>
> On most operations, the performance was:
>
> fastest   number
>           number(10)
>           varchar2(10)
> slowest   char(10)
>
>
> Now this seemed opposite from what one might assume, given that number 
> is 38 digits, the typical performance advantage of fixed length 
> records versus variable length records, etc.
>
> My thinking then turned to that 9i probably has two "native" column 
> types, number and varchar2, and the others are just constrained 
> versions (some additional overhead) of these two types.
>
> When I looked at the the average length of the records, for the char 
> and varchar2, it was 11, but for number(10) and number, it was only 7.
>
> Clearly 9i is up to some trick if it can store 38 digit precision in 7 
> columns, and the fact that number(10) and number are the same size 
> would tend to support my theory about the two native column types.
>
> Any feedback on the above observations?
>
> best,
>
> old dirtbeard
>
My thinking is that you should go to your tables and run the following:

SELECT dump(<column_name>)
FROM ....

Oracle has never used 38 bytes to store a number. What is likely killing 
performance is the
calculation of the number of spaces to append and appending them to the 
string in the CHAR.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)