Re: domain type question/mystery

From: old dirtbeard <dirtbeard_at_pacbell.net>
Date: Fri, 10 Oct 2003 19:11:54 GMT
Message-ID: <_vDhb.9301$Eg5.4730@newssvr29.news.prodigy.com>

Hi,

Now I'm more confused -- I always thought that number was limited to 38 digit precision, but in 9i I can insert 126 digit numbers:

>
> COL1
> ----------
> 1.2346E+38
> 1.2346E+39
> 1.2346E+51
> 1.2346E+91
> 1.2346E+98
> 1.235E+108
> 1.235E+118
> 1.235E+125
>
> 8 rows selected.
>
> 12:07:34 SQL> select dump(col1) from test_long;
>
> DUMP(COL1)
> --------------------------------------------------------------------------------
> Typ=2 Len=21: 212,2,24,46,68,90,2,24,46,68,90,2,24,46,68,90,2,24,46,68,90
> Typ=2 Len=21: 212,13,35,57,79,91,13,35,57,79,91,13,35,57,79,91,13,35,57,79,91
> Typ=2 Len=21: 218,13,35,57,79,91,13,35,57,79,91,13,35,57,79,91,13,35,57,79,91
> Typ=2 Len=21: 238,13,35,57,79,91,13,35,57,79,91,13,35,57,79,91,13,35,57,79,91
> Typ=2 Len=21: 242,2,24,46,68,90,2,24,46,68,90,2,24,46,68,90,2,24,46,68,90
> Typ=2 Len=21: 247,2,24,46,68,90,2,24,46,68,90,2,24,46,68,90,2,24,46,68,90
> Typ=2 Len=21: 252,2,24,46,68,90,2,24,46,68,90,2,24,46,68,90,2,24,46,68,90
> Typ=2 Len=21: 255,13,35,57,79,91,13,35,57,79,91,13,35,57,79,91,13,35,57,79,91

and the dump command shows a length of 21. Is this liked a packed decimal? Also is the current limit on number=126?

doug
Daniel Morgan wrote:

> 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.
>
Received on Fri Oct 10 2003 - 14:11:54 CDT