Hi,
Oracle version 11g (11.2.0.1.0)
I am looking for suggestions on how to construct 2 tables that shows visible difference between USE_MERGE and USE_NL or USE_HASH join hint.
I have created 2 tables customer and order with customer having 26 records and order table with 300,000 records. Both the table are joined on custnum column, which has unique index on customer table and non-unique index on order table.
As I read documentation that if outer table is smaller than USE_NL performs better than USE_MERGE. but following join statement gives better performance for USE_MERGE hint.
set timing on;
spool c:/sgarg/tmp/ojoin11g_um.out;
select /*+ USE_MERGE(t0,t1) */
t0.custnum, t0.name, nvl(t1.ordernum,-1), nvl(t1.carrier,-1) from customer t0 left outer join order_ t1 on (t1.custnum = t0.custnum);
/
set timing off;
set spool off;
Output file shows as
288546 rows selected.
Elapsed: 00:00:04.29
-AS COMPARED TO-
set timing on;
spool c:/sgarg/tmp/ojoin11g_um.out;
select /*+ USE_NL(t0,t1) */
t0.custnum, t0.name, nvl(t1.ordernum,-1), nvl(t1.carrier,-1) from customer t0 left outer join order_ t1 on (t1.custnum = t0.custnum);
/
set timing off;
set spool off;
288546 rows selected.
Elapsed: 00:00:04.39
why USE_MERGE is performing fast. How the 2 tables should be constructed to show USE_NL performs better than USE_MERGE.
Thanks,
Sachin