RE: Table Joins

No.

Oh, you want a more complete answer than that? ;-)

Ok, what happens when you do a join?

• Buffer gets, these need to happen irrespective of column datatypes.
• Block parsing, this needs to happen irrespective of datatypes.
• Comparison of table or index values to table or index values in a different table or index, again that has to happen irrespective of datatype.

But the ultimate test is, as always, prove it! Benchmark it, show a case where a numerical datatype is faster than an alpha datatype. What about DATE datatype? Are those faster or slower than numbers? Internally, ultimately, they're all numbers. Not to mention the argument that you ALWAYS want to make the datatype of a column appropriate to the semantics of the column. If the column is a date, *don't* use a varchar2, use a date type! If the column is really a number, use a number, not a varchar! The more expressive your datamodel is about your data, the more helpful it will be to the optimizer.

Also, I'm sure Tom addresses this on AskTom, but I did a quick search and couldn't find a reference to it....I'm sure it's there somewhere.

Just my two cents,

-Mark

--

Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of genegurevich_at_discoverfinancial.com
Sent: Wednesday, May 24, 2006 5:01 PM
To: oracle-l
Subject: Table Joins

Hi all:

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?

thank you

Gene Gurevich

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Wed May 24 2006 - 16:31:29 CDT