Re: Left Join on 2 Integers, 1 Float, or 1 Char

Dan Fitzpatrick wrote:
> I have 2 tables in Oracle 8i. One has 500,000 records (Table A), the
> other 5 million (Table B). I have to left join them on two integers.
> Which of the following will have the best performance (fastest
> select)?
> Or is there another option?
>
> Option 1: Join on 2 integer fields
>

 [...]
>
>
> Option 2: Create a floating point field combining the two integers and
> join on it.
>
> SELECT A.*, B.*
> FROM A, B
> WHERE A.FLOAT1 = B.FLOAT1(+)
> AND A.FLOAT1 = 1234.89
>

Besides the fact that you cannot distinct 1234.4 from 1234.40 it's not a good idea to compare floats for equallity.

>
 

> Option 3: Create a char or varchar2 field combining the two integers
> and
> join on it.
>

[...]

I don't know which one runs fastest but you should in any case index the columns on which you perform the join.

Hope that helps,
Lothar

-- 
Lothar Armbrüster       | la_at_oktagramm.de
Hauptstr. 26            | la_at_heptagramm.de
D-65346 Eltville        | lothar.armbruester_at_t-online.de