domain type question/mystery

From: old dirtbeard <dirtbeard_at_pacbell.net>
Date: Fri, 10 Oct 2003 18:07:52 GMT
Message-ID: <YzChb.9297$T14.6786@newssvr29.news.prodigy.com>

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 Received on Fri Oct 10 2003 - 13:07:52 CDT