Re: Table Joins

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Thu, 25 May 2006 09:41:36 +0300
Message-ID: <6e49b6d00605242341x2e8cdf84o@mail.gmail.com>

I'd say that for such a comparison you probably don't even need anything to join.
For my very very simple test below you can see that difference for comparison of 5'877'112 rows two identical number columns needed mostly 01.02 sec but for the same comparison for the same values of varchar columns needed 01.03 sec.

BUT as you can imagine the worst case is to compare number to varchar2 because it takes 2 times more 02.04 sec.

So that's the real comparison that shoul be avoided.

Of course you can use Kyte's nice tool runstats to get more precise numbers for more indicators and get better picture of all that.

Gints Plivna

SQL> create table big as select rownum rn1, rownum rn2, to_char(rownum) rn3, to_char(rownum) rn4   2 from dba_source;

Table created.

Elapsed: 00:00:06.04

SQL> insert into big select * from big;

734639 rows created.

Elapsed: 00:00:04.09
SQL> / 1469278 rows created.

Elapsed: 00:00:09.06
SQL> / 2938556 rows created.

Elapsed: 00:00:19.04
SQL> select count(*) from big where rn1 = rn2;

  COUNT(*)

---

   5877112

Elapsed: 00:00:02.01
SQL> /   COUNT(*)

---

   5877112

Elapsed: 00:00:01.02
SQL> /   COUNT(*)

---

   5877112

Elapsed: 00:00:01.02
SQL> /   COUNT(*)

---

   5877112

Elapsed: 00:00:01.02
SQL> select count(*) from big where rn3 = rn4;

  COUNT(*)

---

   5877112

Elapsed: 00:00:01.03
SQL> /   COUNT(*)

---

   5877112

Elapsed: 00:00:01.03
SQL> /   COUNT(*)

---

   5877112

Elapsed: 00:00:01.03
SQL> /   COUNT(*)

---

   5877112

Elapsed: 00:00:01.03
SQL> select count(*) from big where rn1 = rn3;

  COUNT(*)

---

   5877112

Elapsed: 00:00:02.05
SQL> /   COUNT(*)

---

   5877112

Elapsed: 00:00:02.04
SQL> /   COUNT(*)

---

   5877112

Elapsed: 00:00:02.05
SQL> /   COUNT(*)

---

   5877112

Elapsed: 00:00:02.04

2006/5/25, genegurevich_at_discoverfinancial.com <genegurevich_at_discoverfinancial.com>:

> 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

Received on Thu May 25 2006 - 01:41:36 CDT