Re: Hash Tables
This is a multi-part message in MIME format. ------=_NextPart_000_004C_01C3E5F3.B6FDB600 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable A hash join (or any join for that matter) is between two tables - if you = look at the explain plan for a query it will show (well, if you can = decipher it properly) pairs of tables being joined. So if you are = joining three tables, the optimizer will look at your query, figure out = which two of the three (or 18) should be joined first, which of those = two is 'smaller' and use that to build the hash table, it will then = probe with the second table. The results of that join will be used to = build the next hash table - which will then be probed by the next table = in the chain and so on...... cheers j. ----- Original Message -----=20 From: Wilkinson, Marcus=20 To: 'informix-list@iiug.org'=20 Sent: Wednesday, January 28, 2004 7:36 PM Subject: Hash Tables Hi, Could anyone explain to me exactly what happens when you perform a = hash join between multiple tables. The Informix doc I have read only = references a two table join. It says that a hash table is built of the = driving table and matched against hash values of the second table. = What happens if there is a third and fourth table? Would more than one hash table be built? How can I determine how big the hash table(s) will be and how much = memory they will consume. Thanks for your help. Marcus Wilkinson=20 ------=_NextPart_000_004C_01C3E5F3.B6FDB600 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2800.1226" name=3DGENERATOR> <STYLE>@page Section1 {size: 595.3pt 841.9pt; margin: 72.0pt 90.0pt = 72.0pt 90.0pt; } P.MsoNormal { FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman" } LI.MsoNormal { FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman" } DIV.MsoNormal { FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman" } A:link { COLOR: blue; TEXT-DECORATION: underline } SPAN.MsoHyperlink { COLOR: blue; TEXT-DECORATION: underline } A:visited { COLOR: purple; TEXT-DECORATION: underline } SPAN.MsoHyperlinkFollowed { COLOR: purple; TEXT-DECORATION: underline } P { FONT-SIZE: 12pt; MARGIN-LEFT: 0cm; MARGIN-RIGHT: 0cm; FONT-FAMILY: = "Times New Roman" } SPAN.EmailStyle17 { COLOR: windowtext; FONT-FAMILY: Arial } DIV.Section1 { page: Section1 } </STYLE> </HEAD> <BODY lang=3DEN-GB vLink=3Dpurple link=3Dblue bgColor=3D#ffffff> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>A hash join (or any join for that = matter) is=20 between two tables - if you look at the explain plan for a query it will = show=20 (well, if you can decipher it properly) pairs of tables being = joined. So=20 if you are joining three tables, the optimizer will look at your query, = figure=20 out which two of the three (or 18) should be joined first, which of = those two is=20 'smaller' and use that to build the hash table, it will then probe with = the=20 second table. The results of that join will be used to build the = next hash=20 table - which will then be probed by the next table in the chain and so=20 on......</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>cheers</FONT></DIV> <DIV><FONT face=3DArial size=3D2>j.</FONT></DIV> <BLOCKQUOTE dir=3Dltr=20 style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; = BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px"> <DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV> <DIV=20 style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: = black"><B>From:</B>=20 <A title=3Dmwilkinson@doubleclick.net=20 href=3D"mailto:mwilkinson@doubleclick.net">Wilkinson, Marcus</A> = </DIV> <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A = title=3Dinformix-list@iiug.org=20 href=3D"mailto:'informix-list@iiug.org'">'informix-list@iiug.org'</A> = </DIV> <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Wednesday, January 28, = 2004 7:36=20 PM</DIV> <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> Hash Tables</DIV> <DIV><BR></DIV> <DIV class=3DSection1> <P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN=20 style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">Hi,</SPAN></FONT></P> <P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN=20 style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial"></SPAN></FONT> </P> <P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN=20 style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">Could anyone explain to = me exactly=20 what happens when you perform a hash join between multiple = tables. =20 The Informix doc I have read only references a two table join. = It says=20 that a hash table is built of the driving table and matched against = hash=20 values of the second table. What happens if there is a = third and=20 fourth table?</SPAN></FONT></P> <P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN=20 style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial"></SPAN></FONT> </P> <P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN=20 style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">Would more than one hash = table be=20 built?</SPAN></FONT></P> <P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN=20 style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial"></SPAN></FONT> </P> <P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN=20 style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">How can I determine how = big the=20 hash table(s) will be and how much memory they will = consume.</SPAN></FONT></P> <P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN=20 style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial"></SPAN></FONT> </P> <P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN=20 style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">Thanks for your=20 help.</SPAN></FONT></P> <P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN=20 style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial"></SPAN></FONT> </P> <P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN=20 style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial"></SPAN></FONT> </P> <P style=3D"MARGIN: 0cm 0cm 0pt"><FONT face=3D"Times New Roman" = size=3D3><SPAN=20 style=3D"FONT-SIZE: 12pt">Marcus Wilkinson <BR><BR></SPAN></FONT></P> <P class=3DMsoNormal><FONT face=3D"Times New Roman" size=3D3><SPAN=20 style=3D"FONT-SIZE: = 12pt"></SPAN></FONT> </P></DIV></BLOCKQUOTE></B