Re: Table Joins

On 24/05/06, genegurevich_at_discoverfinancial.com <genegurevich_at_discoverfinancial.com> wrote:
> One of my developers insists that joins by numerical fields result in
> better preformance than the joins by
> character fields. I don't remember reading much on this point. Is there any
> truth in it?

I don't recall reading anything about that either. I suppose there's a certain logic to it, at the processor level you're comparing numbers and there's probably fewer steps when you're already dealing with numbers rather than character strings. I suspect that, in a real world system, any time savings from using numbers instead of charcaters would be swamped by the other things that take time.

I'd be tempted to say tot eh developer: "OK, prove it."

It shouldn't be too hard to generate a test case to compare. Create a pair of tables each with a common key column and a content column.

e.g.

create table a1
(
thekey varchar2(10),
content varchar2(2000)
);

create table a2
(
thekey varchar2(10),
morecontent varchar2(2000)
);

Make thekey the primary key of table a1 and a forigen key on a2 referencing a1.thekey. a1.content and a2.content and just for random junk data to pad it out. Create some code to load up the tables, so that some or all of the rows in a1 have one or more rows with matching values for thekey in a2 and the column a1.content and a2.morecontent contain long strings (maybe concatenate the value for the value of thekey for that row a 20 times), with a large number of rows. Maybe use a sequence (and cast to a character string) to generate the key values?

Query the table:

select a1.content, a2.morecontent
from a1, a2
where a1.thekey=a2.thekey;

and time how long it takes.

Repeat but change the thekey column to type number then compare the times.

If nothing else getting the developer to develop and run a test case will get them out of your hair for a while.

I can't help wondering if this is really an arguement over natural vs surrogate keys? Natural keys tend to be character strings (names, postal codes &c) where as surrogate keys tend to be numeric (generated by a sequence or similar). That's a much bigger debate than I'd want to get into right now (besides which there are much more real world relevant factors in that debate than the speed of joining).

Stephen

-- 
It's better to ask a silly question than to make a silly assumption.

http://stephensorablog.blogspot.com/

 'nohup cd /; rm -rf * > /dev/null 2>&1 &'
--
http://www.freelists.org/webpage/oracle-l