Re: domain type question/mystery

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_at_x.washington.edu
(replace 'x' with a 'u' to reply)