| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> performance of joins
Hi,
I have trying to analyse the performance of non-equi joins on Oracle.
For this purpose, I tried different techniques : simple nested loop
join, sort-merge join, nested index join. I am pasting the query I used
and the summary of traces for each of these techniques below (after
these points). All the queries are executed on 2 randomly generated
datasets of size 10k each. What I could not understand are :
(a) Why does the nested index join have a similar performance as the nested loop join even though it uses the index. Probably index range scans are bad? Since we are dealing with only the attribute in the index, shouldnt it be a lot faster compared to the naive nested loop? OR plainly the reason is very high selectivity and may be the indexes do not prove to be that helpful?
(b) Similarly sort-merge join exhibits a bad performance. Isnt the sorting-based join supposed to be the more optimal of these techniques? I tried changing the sort_area_size, but does not seem to alter the performance.
I would really appreciate if any one can help me out with these problems.
regards,
Derek Smith.
select /*+ ORDERED USE_NL(f1 f2)*/count(*)
from
test2 f2, test1 f1 where f1.a < f2.a
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 2 23.46 22.94 0 380038 0
1
total 4 23.46 22.95 0 380040 0
1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 58
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=380038 pr=0 pw=0 time=22947227 us)
49995000 NESTED LOOPS (cr=380038 pr=0 pw=0 time=149985122 us)
10000 TABLE ACCESS FULL TEST2 (cr=38 pr=0 pw=0 time=30086 us)
49995000 TABLE ACCESS FULL TEST1 (cr=380000 pr=0 pw=0 time=50239901
us)
select /*+ ORDERED USE_NL_WITH_INDEX (f1 tmpindex1) NO_USE_HASH(f1 f2)
NO_USE_MERGE(f1 f2)*/count(*)
from
test2 f2, test1 f1 where f1.a < f2.a
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 3 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 2 26.87 26.29 25 126280 0
1
total 4 21.87 21.29 25 126283 0
1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 58
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=126280 pr=25 pw=0 time=21296040 us)
49995000 NESTED LOOPS (cr=126280 pr=25 pw=0 time=149985217 us)
10000 TABLE ACCESS FULL TEST2 (cr=38 pr=0 pw=0 time=20089 us)
49995000 INDEX RANGE SCAN TMPINDEX1 (cr=126242 pr=25 pw=0
time=50074000 us)(object id 52623)
select /*+ ORDERED USE_MERGE (f1 f2)*/count(*)
from
test2 f2, test1 f1 where f1.a < f2.a
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 3 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 2 34.14 38.68 0 76 0
1
total 4 34.15 38.69 0 79 0
1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 58
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=76 pr=0 pw=0 time=38688180 us)
49995000 MERGE JOIN (cr=76 pr=0 pw=0 time=150017715 us)
10000 SORT JOIN (cr=38 pr=0 pw=0 time=36761 us)
10000 TABLE ACCESS FULL TEST2 (cr=38 pr=0 pw=0 time=10071 us)
49995000 SORT JOIN (cr=38 pr=0 pw=0 time=50077082 us)
10000 TABLE ACCESS FULL TEST1 (cr=38 pr=0 pw=0 time=10065 us)
Received on Wed Jun 01 2005 - 23:10:33 CDT
 |
 |