Re: Table join performance & datatypes

srikant <oracle_user_at_my-dejanews.com> wrote:
>Is there any major performance difference between
>1) joining varchar2 columns from 2 tables.
> AND
>2) joining number columns from 2 tables.

IMO, on a scale of 1 - 100, this issue probably features at about 98 when designing a database. ie. it makes very little difference and there's far more important things to worry about.

However, here's the things to consider. Essentially, index blocks contain each distinct value of the indexed column followed by a list of rowids having that value. If you have long key values and a lot of distinct values ( obviously true for primary keys and unique indexes ), then you'll get a relatively small number of index values per block, and so reading the index will require more physical I/O. Numeric keys are obviously much shorter than character keys and so you'll pack more index values per block, making reading the index quicker.

Also, I suspect most operating systems can perform numeric comparisons slightly more efficiently than character comparisons when locating the start and end points of the range to be searched in the index. But we're talking picoseconds here.

So, the bottom line is that numeric keys are very slightly more efficient than character keys, but the effect is only noticeable for long keys. For keys of 10 or 20 characters or less ( choose your own threshold ), the difference will be marginal.

Personally, I think you should also consider maintenance. Why bother maintaining additional numeric keys when you've got perfectly good character keys? Your application SQL may become less understandable if it's all based on numeric keys.

Dave.
--
Remove the no-spam bit from my email address to reply. Received on Fri Oct 23 1998 - 04:34:24 CDT