Comments in-line.
--
Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
Alan Byrne wrote in message <3992659F.F2C11E32_at_hotmail.com>...
>let's say the cust_num is not only alphanumeric, but is also always 30
>characters long, and the table needs to be able to hold 10mil rows (so a
>16-bit number would do the trick), surely there is then a performance
>benefit switching to a numeric key? A join comparison on a 16-bit number
>is sure to be faster than a comparison on a 30-character string, or am I
>missing something here?
>
Oracle holds numbers in a form of packed decimal, or BCD, which
puts 2 decimal digits per byte, and one byte for a base-100 mantissa,
so a (positive) number typically uses 1 + ceil(n/2) bytes.
Your comparison would be between 30 bytes character information
and 6 bytes numeric. Given that an index search typically does
about 3 or 4 comparisons to hit right row, the possibility of avoiding
the extra 24 bytes of comparison is unlikely to make a significant
difference.
However, there will be a significant difference in the physical sizes
of the two indexes - 10M leaf entries of 6 bytes of data plus overhead
will come to about 180 Mb; whereas the 30 byte character leaf layer
will be about 420Mb. Of course, you have to trade this benefit off
against the fact that you've added 70Mb to the table by adding the
surrogate key . . . .
BTW - if you use a reversed key index for the surrogate key,
the run-time size is likely to be much greater than the
theoretical size - possibly about double - because of the
side-effects of Oracle's implementation of b-tree block splits
>
>> IMO the comparison semantics in numbers are far more efficient than
>the comparison semantics for strings.
>
>The "comparison semantics"? What do you mean by this?
>
Does 'ABC ' match 'ABC' ?
In Oracle you have the choice.
Received on Fri Aug 11 2000 - 00:00:00 CDT
