Re: Reducing the overhead of NUMERIC data

I am not able to quickly find your numeric format, so I'll just throw
this in. MaxDB (I only mention this because the format and algorithms
are now under the GPL, so they can be reviewed by the public) uses a
nifty number format that allows the use memcpy to compare two numbers
when they are in the same precision and scale. Basically, the first
byte contains the sign and number of digits in the number (number of
digits is complemented if the number is negative), then the next N bytes
contain the actual decimal digits, where N is the number of decimal
digits / 2 (so two decimal digits per byte). Trailing 0's are removed
to save space. So,

0 is stored as {128}
1 is stored as {193, 16}
1000 is stored as {196, 16}
1001 is stored as {196, 16, 1} x{C4 10 01}
-1 is stored as {63, 144}
-1001 is stored as {60, 144}

Their storage allows for a max of 63 digits in a number, but it should
be no problem to increase the size to 2 bytes, thus allowing up to
16,385 digits.

The advantages are:
- ability to memcmp two numbers.
- compact storage (can be made more compact if you choose to
save hex digits instead of decimal, but I'm not sure you want to do
that).

The disadvantages are as follows:
- this format does not remember the database definition for the
number (that is, no precision or scale); numeric functions must be told
what they are. It would be nice if the number kept track of that as
well...
- comparing two numbers that are not the same precision and
scale means converting one or both (if both precision and scale are
different you may have to convert both)
- calculations (addition, subtraction, etc) require functions to
extract the digits and do the calculation a digit at a time.
- I do not know of any trig functions, so they would need to be
written

If any one is interested, I would be happy to discuss this further.

Mike Pollard
SUPRA Server SQL Engineering and Support
Cincom Systems, Inc