Re: Table Joins
- From: "Gints Plivna" <gints.plivna@xxxxxxxxx>
- To: genegurevich@xxxxxxxxxxxxxxxxxxxxx
- Date: Thu, 25 May 2006 09:41:36 +0300
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@xxxxxxxxxxxxxxxxxxxxx
<genegurevich@xxxxxxxxxxxxxxxxxxxxx>:
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
