Rosetta Code

Hash join: Difference between revisions

Page Discussion

Hash join (view source)

Revision as of 14:23, 4 July 2019

258 bytes removed ,  4 years ago
Changed the whole SQL solution to illustrate better practices. Original solution was missing the key ingredient, the optimizer hint.
(Changed the whole SQL solution to illustrate better practices. Original solution was missing the key ingredient, the optimizer hint.)
Line 2,849:
 
=={{header|SQL}}==
{{works with|oracle}}
Setting up the data is a bit verbose:
<lang sql>create-- tablesetting peopleup (agethe decimal(3),test name varchar(16));data
insert into people (age, name) values (27, 'Jonah');
insert into people (age, name) values (18, 'Alan');
insert into people (age, name) values (28, 'Glory');
insert into people (age, name) values (18, 'Popeye');
insert into people (age, name) values (28, 'Alan');
 
create table nemesisespeople (nameage varcharnumber(163), nemesisname varcharvarchar2(1630));
insert into nemesisespeople (nameage, nemesis) values ('Jonah', 'Whales'name);
select 27, 'Jonah' from dual union all
insert into nemesises (name, nemesis) values ('Jonah', 'Spiders');
select 18, 'Alan' from dual union all
insert into nemesises (name, nemesis) values ('Alan', 'Ghosts');
select 28, 'Glory' from dual union all
insert into nemesises (name, nemesis) values ('Alan', 'Zombies');
select 18, 'Popeye' from dual union all
insert into nemesises (name, nemesis) values ('Glory', 'Buffy');</lang>
select 28, 'Alan' from dual
;
 
create table nemesises (name varchar2(30), nemesis varchar2(30));
Doing the join is concise. But we don't actually have control over how the join is implemented, so this might not actually be a hash join...
<langinsert sql>select * from people p joininto nemesises n on p.(name, =nemesis) n.name</lang>
select 'Jonah', 'Whales' from dual union all
select 'Jonah', 'Spiders' from dual union all
select 'Alan' , 'Ghosts' from dual union all
select 'Alan' , 'Zombies' from dual union all
select 'Glory', 'Buffy' from dual
;</lang>
 
Doing the join is trivial. Normally we would let the optimizer select the join method. However, to force a hash join, we can use an optimizer hint, USE_HASH.
<lang sql>select /*+ use_hash */ * from people join nemesises using(name);</lang>
 
{{out}}
<pre> AGE NAME NAME NEMESIS
---------- ---------------- ---------------- ----------------
27 Jonah Jonah Whales
27 Jonah Jonah Spiders
28 Alan Alan Ghosts
18 Alan Alan Ghosts
28 Alan Alan Zombies
18 Alan Alan Zombies
28 Glory Glory Buffy</pre>
 
=={{header|Tcl}}==
Anonymous user
Retrieved from "https://rosettacode.org/wiki/Special:MobileDiff/236807"