MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_NextPart_01C7FEA7.C035BFD0" This document is a Web archive file. If you are seeing this message, this means your browser or editor doesn't support Web archive files. For more information on the Web archive format, go to http://officeupdate.microsoft.com/office/webarchive.htm ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies

This presentation contains content that your browser may not be able to = show properly. This presentation was optimized for more recent versions of Micro= soft Internet Explorer.

If you would like to proceed anyway, click here.

 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/master03.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii"
Click to edit Master title style
Click to edit Master text styles
Second level
Third level
Fourth level
Fifth level
‹date/time›
‹footer›
‹#›
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/master03.xml Content-Transfer-Encoding: quoted-printable Content-Type: text/xml; charset="utf-8" ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/preview.wmf Content-Transfer-Encoding: base64 Content-Type: image/x-wmf AQAJAAADXwQAAAUAOgAAAAAABAAAAAMBCAAFAAAACwIAAAAABQAAAAwC7wLpAwMAAAAeAAcAAAD8 AgAA////AAAABAAAAC0BAAAIAAAA+gIFAAAAAAD///8ABAAAAC0BAQAMAAAAQAkhAPAAAAAAAAAA 7wLpA/////8IAAAA+gIAAAAAAAAAAAAABAAAAC0BAgAEAAAALQEAAAQAAAAnAf//HAAAAPsCvf8A AAAAAACQAQAAAAAAQAAAQXJpYWwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEAAAALQEDAAQA AAAuARgABAAAAAIBAQAFAAAACQIAAAACHAAAADIKKgETAQ4AAABBIFRheG9ub215IG9mIC0AEwAp ACUAIAAlACUAJQA5ACEAEwAlABMAEwAEAAAALgEAABwAAAD7AhAABwAAAAAAvAIAAAAAAQICIlN5 c3RlbQAAAAAAABgAAADo5xMAAQAAAOQEAAAAAAAABAAAAC0BBAAEAAAA8AEDABwAAAD7Ar3/AAAA AAAAkAEAAAAAAEAAAEFyaWFsAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABAAAAC0BAwAEAAAA LgEYAAQAAAACAQEABQAAAAkCAAAAAh4AAAAyCnoB9wAPAAAAUHJvYmxlbWF0aWMgU1FMAC0AFgAl ACUADwAkADkAJQATAA4AIgASAC0ANAAlAAQAAAAuAQAABAAAAC0BBAAEAAAA8AEDABwAAAD7AtT/ AAAAAAAAkAEAAAAAAEAAAEFyaWFsAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABAAAAC0BAwAE AAAALgEYAAQAAAACAQEABQAAAAkCAAAAAhgAAAAyCtkBigELAAAAQXByaWwsIDIwMDcgHQAZAA8A CgAKAAwADAAZABkAGAAYAAQAAAAuAQAABAAAAC0BBAAEAAAA8AEDABwAAAD7At//AAAAAAAAkAEA AAAAAEAAAEFyaWFsAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABAAAAC0BAwAEAAAALgEYAAQA AAACAQEABQAAAAkCAAAAAjoAAAAyCmQCQwAiAAAAqTIwMDcgRGFuIFRvdywgQWxsIHJpZ2h0cyBy ZXNlcnZlZBgAEwASABMAEgAJABkAEgASAAkAFgASABgACQAJABcABwAHAAkADAAHABMAEgAJABEA CQAMABEAEgASAAwAEAASABMABAAAAC4BAAAEAAAALQEEAAQAAADwAQMAHAAAAPsC3/8AAAAAAACQ AQAAAAAAQAAAQXJpYWwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEAAAALQEDAAQAAAAuARgA BAAAAAIBAQAFAAAACQIzM5kCJAAAADIKjAJDABMAAABkYW50b3dAc2luZ2luZ3NxbC5jABMAEgAS AAoAEgAXACIAEgAHABMAEgAIABIAEgASABIABwAJABIABAAAAC4BAAAEAAAALQEEAAQAAADwAQMA HAAAAPsC3/8AAAAAAACQAQAAAAAAQAAAQXJpYWwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAE AAAALQEDAAQAAAAuARgABAAAAAIBAQAFAAAACQIzM5kCCgAAADIKjAJ/AQIAAABvbRIAGwAEAAAA LgEAAAQAAAAtAQQABAAAAPABAwAcAAAA+wLf/wAAAAAAAJABAAAAAABAAABBcmlhbAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAQAAAAtAQMABAAAAC4BGAAEAAAAAgEBAAUAAAAJAgAAAAIiAAAA Mgq0AkMAEgAAAHd3dy5zaW5naW5nc3FsLmNvbRgAGAAYAAkAEQAHABMAEgAIABMAEgARABIABwAJ ABIAEgAcAAQAAAAuAQAABAAAAC0BBAAEAAAA8AEDABwAAAD7ArX/AAAAAAAAkAEBAAAAAEAAAEFy aWFsAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABAAAAC0BAwAEAAAALgEYAAQAAAACAQEABQAA AAkCwMDAAhIAAAAyCoMAjwAHAAAAU2luZ2luZ3MyABEAKgAqABAAKgAqAAQAAAAuAQAABAAAAC4B GAAEAAAAAgEBAAUAAAAJAgAAAAISAAAAMgqAAIwABwAAAFNpbmdpbmdzMgARACoAKgAQACoAKgAE AAAALgEAAAQAAAAtAQQABAAAAPABAwAcAAAA+wK1/wAAAAAAALwCAAAAAABAAABBcmlhbAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAQAAAAtAQMABAAAAC4BGAAEAAAAAgEBAAUAAAAJAsDAwAIN AAAAMgqDAIoBBAAAAFNRTCAyADoALgAVAAQAAAAuAQAABAAAAC4BGAAEAAAAAgEBAAUAAAAJAv8A AAINAAAAMgqAAIcBBAAAAFNRTCAyADoALgAVAAQAAAAuAQAABAAAAC0BBAAEAAAA8AEDABwAAAD7 ArX/AAAAAAAAkAEAAAAAAEAAAEFyaWFsAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABAAAAC0B AwAEAAAALgEYAAQAAAACAQEABQAAAAkCwMDAAhMAAAAyCoMAOQIIAAAAUHJlc2VudHMyABkAKQAm ACoAKQAVACYABAAAAC4BAAAEAAAALgEYAAQAAAACAQEABQAAAAkCAAAAAhMAAAAyCoAANgIIAAAA UHJlc2VudHMyABkAKQAmACoAKQAVACYABAAAAC4BAAAEAAAALQEEAAQAAADwAQMAHAAAAPsCtf8A AAAAAACQAQAAAAAAQAAAQXJpYWwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEAAAALQEDAAQA AAAuARgABAAAAAIBAQAFAAAACQIAAAACCQAAADIKgABeAwEAAAA6ABUABAAAAC4BAAAEAAAALQEE AAQAAADwAQMAAwAAAAAA ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/pres.xml Content-Transfer-Encoding: quoted-printable Content-Type: text/xml; charset="utf-8" ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0050.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
A Taxonomy of
Problematic SQL=
April, 2007
©2007 Dan Tow, All rights reserved
dantow@singingsql.com
www.singingsql.com
SingingSQL Presents:
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0052.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Introduction
Assertions:
•Experience helps when fixing problem SQL.
•Experience fixing problem SQL= is highly transferable between databases and even between altogether different applications.
•You can categorize SQL accord= ing to patterns that are independent of the business purpose <= /span>of the SQL.
•Recognition of these patterns helps in transferring lessons learned from earlier fixe= s.
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0052_image001.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0051.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
taxonomy: The science, laws, or <= /span>principles of classification, esp. the classification of organisms in categories based on common characteristics.*


* The American He= ritage Dictionary of the English La= nguage
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0053.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Properties of a Useful Taxonomy of Problem SQL
•Categories must be recognizable.
•Categories should not depend = on the details of the business application or other trivialities.
•Recognizing underlying similarities (categories) between SQL statements in altogether different application domains just by staring at the SQL= , is nearly impossible.
•We need an abstract representa= tion of the SQL that preserves the essentials to categorizatio= n.
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0053_image002.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0054.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Join Trees
Assertion:
•<= /span>Join trees, as described in SQL Tuning,* written by me, published by O’Reilly, are an effective abstract representation of SQL that preserves what you need to know to classify the SQL and recognize problematic patterns. <= /span>
•<= /span>
•<= /span>This presentation borrows liberally from Chapter 7 of this book. O’Re= illy retains the copyright on the material directly from the book.<= /span>
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0054_image003.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0046.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Join Trees, a Crash Introduction
SELECT …
FROM Orders O, Order_Details OD, Products P, Customers C,
        Shipments S, Addresses A, Code_Translations ODT, = = Code_Translations OT
WHERE UPPER(C.Last_Name) LIKE :Last_Name||'%'
 AND UPPER(C.First_Name) LIKE :First_Name||'%'
  AND OD.Order_ID =3D O.Order_ID= 3;
  AND O.Customer_ID =3D C.Customer_= ID
  AND OD.Product_ID =3D P.Product_ID(+)
  AND OD.Shipment_ID =3D S.Shipment_ID(+)
  AND S.Address_ID =3D A.Address_ID= (+)
  AND O.Status_Code =3D OT.Code = ;
  AND OT.Code_Type =3D 'ORDER_STATU= S'
  AND OD.Status_Code =3D ODT.Code&#= 13;
  AND ODT.Code_Type =3D 'ORDER_DETAIL_STATUS'
  AND O.Order_Date > :Now - 366&= #13;
ORDER BY …;
 = S      P    O       O= DT
OD
A      OT        = ;  C
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0046_image004.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0046_image005.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhrwCnAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAEAAACt AKYAgAAAAAAAAAL/jI+py+0Po5y02gOy3rz7D4biSJbmiaZGyrbuC8fxKtf2jeccrff+D9zwgsSi URU4KpfMXbIJjQKH0qoVRr1qt6Qs9wvWeMNk7biMjp7TbOW6DQ++43TdvI6v3fN8174PePIXSCgy WIjYcZjICLDYiPgISSg5CVhpyYeZibfJSef5CRcqykZainaKSqa6CtbqygUba/ZE2zh7W5Wrq2bb W8gLzCQ87PZr3FecXLTMLIf8XOcs7UNdbReN3Xa9fdPtracdXgZOjjV+/pquLsveXgufZi6PQl9v co/f9b4Ppe8vBMCAHwYSVNTv4BGDCsUkbEiEIUSJDSkqtHgQI0GN7QE5+vO4DyQ+kfVIyjMJD2U7 lepYnnNJDmY4md5obrOJDWc1ndJ4PvPJDGgyocaIDjMKDGkvpbqY3nJKC2osqa6orrKKCmspraK4 fvLKCWwmsZbITjILCS2uhxCnsG1r7S3cbHN91f0n964NtYz4JvIbKa9eGYCDCR6MDvExxQsPM2ZR mJLjx/YmU85n+TI/zW45/4gcCPSlzJ5BiFZGurSH05pSq3byOgfrPLM7uY6dofa027h1g+Id23cc 4aOAvybOzbhq5KaUl2Y+z7ln6Kmkc6Zezrpm7Ky0X+YeBvw63N8umD+PPr369RcKAAA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0046_image006.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhDAA4AHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAMAAgAG ADMAgAAAAAAAAAIsBBJmqJfK4FvTtUhvxVbq74WZ2I1miXLqxoLn6qYtCdPz+yB6svO6NOEcDAUA Ow== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0046_image007.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhfwBsAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAEAAQB4 AGYAgAAAAAAAAAL/hI+py+0d4pu02oszkLr7DzoRx4XmiS5jcIzpC38r25bxjTMzXfP5j9upbMDi aedDuIxM0GpDVEab1MlzCZlWt4knNDkEc8de7MM85nq/FnS6uYZi3G9gnH7W1mNxdga/l9IHaKUX GNLn10F4uOjGSAHZ2EYnWSg2OVdpqImZGYlnWSH62ePJqUH6mdjDh5opwdrq6lkqlFWL+Ho4k5er +xt4q7PrVJzWS6zqsVzFmFzUzAQ5bHcM9yqbI729y20cPF38DS58fA1DbkKqDox8jX7TngofL28v g45/H34Uv8/PSLN5LwjiclRKES1H/bYYlNKwS0SHAMMwqxgN4yyE9QklTlSW7yOZity0JfyG0qSt WBHJVesIERUPddBgKlApp91DbCpr5rPpy5BPGUBH1XpZ9M3QpCNFMn0KNarUqVSrWr2KNavWrVy7 ev1aZ6c7mDipiBWEtGnHJWUzOrXmo+2Ps+vAyA2iMd2Uuwth6eFbMC+KZ3Qv+TX6NuSkc4IpJQ7c sjGox4P/SfZlzmnhm5fplYMLGPLYuaHRUlbIrzNi03CVnoZI+jU92RvvqQ2IV41qA3R3i6AN0hVF 4MEDO/PtUa9Z5JxpM5eimwZl5tKfh8FIvXTllNoRE++0L61p69cBdvf+HXzI9Lw3pzJmzSv5rAUA ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0047.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Join Trees, a Crash Introduction
SELECT…
FROM Orders O, Order_Details OD, Products P, Customers C,
        Shipments S, Addresses A, Code_Translations ODT, Code_Translations OT
WHERE UPPER(C.Last_Name) LIKE :Last_Name||'%'
 AND UPPER(C.First_Name) LIKE :First_Name||'%'
  AND OD.Or= der_ID =3D O.Order_ID
  AND O.Customer_ID =3D C.Customer_ID
  AND OD.Product_ID =3D P.Product_ID(+)
  AND OD.Shipment_ID =3D S.Shipment_ID(+)
  AND S.Address_ID =3D A.Address_ID(+)
  AND O.Status_Code =3D OT.Code
  AND OT.Code_Type =3D 'ORDER_STATUS'
  AND OD.Status_Code =3D ODT.Code
  AND ODT.Code_Type =3D 'ORDER_DETAIL_STATUS'
  AND O.Order_Date > :Now - 366
ORDER BY …;
•Each t= able is a node, represented by its alias.
•Each j= oin is a link, with (usually downward-pointing) arrows pointing tow= ard any side of the join that is unique.
•Midpoint arrows point to optional side of any outer join.
<= /div>
 = S      P    O       O= DT
OD
A      OT        = ;  C
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0047_image008.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0047_image009.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhrwCnAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAEAAACt AKYAgAAAAAAAAAL/jI+py+0Po5y02gOy3rz7D4biSJbmiaZGyrbuC8fxKtf2jeccrff+D9zwgsSi URU4KpfMXbIJjQKH0qoVRr1qt6Qs9wvWeMNk7biMjp7TbOW6DQ++43TdvI6v3fN8174PePIXSCgy WIjYcZjICLDYiPgISSg5CVhpyYeZibfJSef5CRcqykZainaKSqa6CtbqygUba/ZE2zh7W5Wrq2bb W8gLzCQ87PZr3FecXLTMLIf8XOcs7UNdbReN3Xa9fdPtracdXgZOjjV+/pquLsveXgufZi6PQl9v co/f9b4Ppe8vBMCAHwYSVNTv4BGDCsUkbEiEIUSJDSkqtHgQI0GN7QE5+vO4DyQ+kfVIyjMJD2U7 lepYnnNJDmY4md5obrOJDWc1ndJ4PvPJDGgyocaIDjMKDGkvpbqY3nJKC2osqa6orrKKCmspraK4 fvLKCWwmsZbITjILCS2uhxCnsG1r7S3cbHN91f0n964NtYz4JvIbKa9eGYCDCR6MDvExxQsPM2ZR mJLjx/YmU85n+TI/zW45/4gcCPSlzJ5BiFZGurSH05pSq3byOgfrPLM7uY6dofa027h1g+Id23cc 4aOAvybOzbhq5KaUl2Y+z7ln6Kmkc6Zezrpm7Ky0X+YeBvw63N8umD+PPr369RcKAAA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0047_image010.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhDAA4AHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAMAAgAG ADMAgAAAAAAAAAIsBBJmqJfK4FvTtUhvxVbq74WZ2I1miXLqxoLn6qYtCdPz+yB6svO6NOEcDAUA Ow== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0047_image011.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhfwBsAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAEAAQB4 AGYAgAAAAAAAAAL/hI+py+0d4pu02oszkLr7DzoRx4XmiS5jcIzpC38r25bxjTMzXfP5j9upbMDi aedDuIxM0GpDVEab1MlzCZlWt4knNDkEc8de7MM85nq/FnS6uYZi3G9gnH7W1mNxdga/l9IHaKUX GNLn10F4uOjGSAHZ2EYnWSg2OVdpqImZGYlnWSH62ePJqUH6mdjDh5opwdrq6lkqlFWL+Ho4k5er +xt4q7PrVJzWS6zqsVzFmFzUzAQ5bHcM9yqbI729y20cPF38DS58fA1DbkKqDox8jX7TngofL28v g45/H34Uv8/PSLN5LwjiclRKES1H/bYYlNKwS0SHAMMwqxgN4yyE9QklTlSW7yOZity0JfyG0qSt WBHJVesIERUPddBgKlApp91DbCpr5rPpy5BPGUBH1XpZ9M3QpCNFMn0KNarUqVSrWr2KNavWrVy7 ev1aZ6c7mDipiBWEtGnHJWUzOrXmo+2Ps+vAyA2iMd2Uuwth6eFbMC+KZ3Qv+TX6NuSkc4IpJQ7c sjGox4P/SfZlzmnhm5fplYMLGPLYuaHRUlbIrzNi03CVnoZI+jU92RvvqQ2IV41qA3R3i6AN0hVF 4MEDO/PtUa9Z5JxpM5eimwZl5tKfh8FIvXTllNoRE++0L61p69cBdvf+HXzI9Lw3pzJmzSv5rAUA ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0048.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Expectations of Simple Queries
•<= /span>Query maps to one tree. =
•The tr= ee has one root, exactly one table with no join to its primary key.
•All jo= ins have downward-pointing arrows (joins unique on one end).
•Outer joins point down, with only outer joins below outer joins.
 = S      P    O       O= DT
OD
A      OT        = ;  C
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0048_image012.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0048_image013.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhrwCnAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAAAAACu AKYAgAAAAAAAAAL/RI6py+0Po5y02ouA3rz7D4biSJbmiaaqEazuC8fyrB70jef6zvL+DwxqbMKi 8ZgiIpfMprIJjf6e0qpVRr1qt6Ys9wvmeMPk7biMlp7T7OW6DRe+43TevI6/3fN82L4PmNQSSEjz V4gIcpjIuLHYyPgIiSg5SVhpCYiZybfJief5SRcqCkdaynaKiqa6StbqCgYbyzVLq2V7a5WrqzbY m8gL7PQ7fFlsrImc3LnMDOr8PBotbUpdnXqNzaq9/drtLQseXjtOjmt+vpuu7tveJvyuxy7PFF+P RY9/dL//0u9vBcCAggh+GWiwi76EPhAyHOHwYYiIEj9QrNjhIkZH7Qs3GuroMV9IYiPtgSzpQqNH lRtZYnRZEaZEmQ9pMrSZEKdBnQR5BvTpD+g+ofiI1jMqD+k7pe2YqnN6Dio5qeGoerO6DSs2rdW4 SvP6DCwzscnIGjM7DC0wtb3Y6nJ7Cy4tubHourK7Ci8qvaX4ivL7CTAnwZkIWzI8CTEkxY0YRzqJ sgbkyCgcB5tMWWHmIpYpYd4M8TPoiaJHWyxtOiPq1BxZ21ntunMh2cdc76AdCLcy2zp09/HdjHcO 4HmIQxOOw3gd5dOQz3P+EfoM5nGoW5MuErsf2Kytw+Oe2ns27dsxmD+PPr369RgKAAA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0048_image014.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhDAA4AHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAMAAgAG ADIAgAAAAAAAAAIpBBJmqJfK4FvTtUhvxVbq74WZ2I1miXLqxoLn6qYtCdPz+yB6slNYXQAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0048_image015.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhfwBsAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAEAAQB4 AGYAgAAAAAAAAAL/hI+py+0dwpu02osziHLrD4Yiw0nciKYqdBrtCsdf2bmRjOcPrbz6n6PVED6g MXUqHpTHZqZ1Yw2d1Ao0Kq1qJy+sg7nddr1fcljrAy/UZ2DavIO3f0U2ST6X1fHxaV5vZlfm96fC xEchWPgEp9i3aCjn+AgpAjbJhVg5mBVJuEm5psn4CSpaujFKasqSioqJATvHwxmk+leS6XbbJlRL xxtGexoMIkslmNt0fKQ4bMQMjEoUbVwsbVEdor2Cee2ZB/sNfiY+To5WfN69PqPejt58Dc8+Xa/B HZOfaX8Hut9p1T96p6yxcgWoX6uDAG0ovPOwHEFtBHdFDOjuYq917tWUHUwQjaLHj0tuXOT2jKTD W/lGqiypyQS9hshc9rBp8CWxVw01muKTUqewoELT+SyKNKnSpUybOn0KNarUqVSrWr2KNVFFJFv1 4ZR4dJnMsAlZXSELg+Y9mLi6cvWjFp9bFJLmXog7Qg3ebHatTdurFa3XfoBDhWPZ99fhWIULttX4 1Zbgtxkn87Vs6VzkepuhCe78NjE/uo2JisWMENtQ1A5Vi2HNVodojGn7znYs2ZZRz7BtpDsNqEpj arBvx3ZC03hq4VEmz+6g/GbL3tKjV88MGjv1u9mlJPa1WuR30zXRlh4eK/OuqtuxFgAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0049.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Normal Simple Queries
•The question that the query answers is basically a question about the <= /span>entity represented at the top (root) of the tree (or about aggregations of = that entity).
•The other tables just provide reference data stored elsewhere for normalization.
 = S      P    O       O= DT
OD
A      OT        = ;  C
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0049_image016.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0049_image017.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhrwCnAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAAAAACu AKYAgAAAAAAAAAL/RI6py+0Po5y02ouA3rz7D4biSJbmiaaqEazuC8fyrB70jef6zvL+DwxqbMKi 8ZgiIpfMprIJjf6e0qpVRr1qt6Ys9wvmeMPk7biMlp7T7OW6DRe+43TevI6/3fN82L4PmNQSSEjz V4gIcpjIuLHYyPgIiSg5SVhpCYiZybfJief5SRcqCkdaynaKiqa6StbqCgYbyzVLq2V7a5WrqzbY m8gL7PQ7fFlsrImc3LnMDOr8PBotbUpdnXqNzaq9/drtLQseXjtOjmt+vpuu7tveJvyuxy7PFF+P RY9/dL//0u9vBcCAggh+GWiwi76EPhAyHOHwYYiIEj9QrNjhIkZH7Qs3GuroMV9IYiPtgSzpQqNH lRtZYnRZEaZEmQ9pMrSZEKdBnQR5BvTpD+g+ofiI1jMqD+k7pe2YqnN6Dio5qeGoerO6DSs2rdW4 SvP6DCwzscnIGjM7DC0wtb3Y6nJ7Cy4tubHourK7Ci8qvaX4ivL7CTAnwZkIWzI8CTEkxY0YRzqJ sgbkyCgcB5tMWWHmIpYpYd4M8TPoiaJHWyxtOiPq1BxZ21ntunMh2cdc76AdCLcy2zp09/HdjHcO 4HmIQxOOw3gd5dOQz3P+EfoM5nGoW5MuErsf2Kytw+Oe2ns27dsxmD+PPr369RgKAAA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0049_image018.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhDAA4AHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAMAAgAG ADIAgAAAAAAAAAIpBBJmqJfK4FvTtUhvxVbq74WZ2I1miXLqxoLn6qYtCdPz+yB6slNYXQAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0049_image019.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhfwBsAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAEAAQB4 AGYAgAAAAAAAAAL/hI+py+0dwpu02osziHLrD4Yiw0nciKYqdBrtCsdf2bmRjOcPrbz6n6PVED6g MXUqHpTHZqZ1Yw2d1Ao0Kq1qJy+sg7nddr1fcljrAy/UZ2DavIO3f0U2ST6X1fHxaV5vZlfm96fC xEchWPgEp9i3aCjn+AgpAjbJhVg5mBVJuEm5psn4CSpaujFKasqSioqJATvHwxmk+leS6XbbJlRL xxtGexoMIkslmNt0fKQ4bMQMjEoUbVwsbVEdor2Cee2ZB/sNfiY+To5WfN69PqPejt58Dc8+Xa/B HZOfaX8Hut9p1T96p6yxcgWoX6uDAG0ovPOwHEFtBHdFDOjuYq917tWUHUwQjaLHj0tuXOT2jKTD W/lGqiypyQS9hshc9rBp8CWxVw01muKTUqewoELT+SyKNKnSpUybOn0KNarUqVSrWr2KNVFFJFv1 4ZR4dJnMsAlZXSELg+Y9mLi6cvWjFp9bFJLmXog7Qg3ebHatTdurFa3XfoBDhWPZ99fhWIULttX4 1Zbgtxkn87Vs6VzkepuhCe78NjE/uo2JisWMENtQ1A5Vi2HNVodojGn7znYs2ZZRz7BtpDsNqEpj arBvx3ZC03hq4VEmz+6g/GbL3tKjV88MGjv1u9mlJPa1WuR30zXRlh4eK/OuqtuxFgAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0055.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Normal Simple Queries
•Result rows from the query <= span style=3D'position:absolute;top:28.0%;left:5.99%;width:89.51%;height:6.75%'= >rigorously map one-to-one to some <= /span>subset of the business entities represented by the root node table.=
 = S      P    O       O= DT
OD
A      OT        = ;  C
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0055_image020.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0055_image021.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhrwCnAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAAAAACu AKYAgAAAAAAAAAL/RI6py+0Po5y02ouA3rz7D4biSJbmiaaqEazuC8fyrB70jef6zvL+DwxqbMKi 8ZgiIpfMprIJjf6e0qpVRr1qt6Ys9wvmeMPk7biMlp7T7OW6DRe+43TevI6/3fN82L4PmNQSSEjz V4gIcpjIuLHYyPgIiSg5SVhpCYiZybfJief5SRcqCkdaynaKiqa6StbqCgYbyzVLq2V7a5WrqzbY m8gL7PQ7fFlsrImc3LnMDOr8PBotbUpdnXqNzaq9/drtLQseXjtOjmt+vpuu7tveJvyuxy7PFF+P RY9/dL//0u9vBcCAggh+GWiwi76EPhAyHOHwYYiIEj9QrNjhIkZH7Qs3GuroMV9IYiPtgSzpQqNH lRtZYnRZEaZEmQ9pMrSZEKdBnQR5BvTpD+g+ofiI1jMqD+k7pe2YqnN6Dio5qeGoerO6DSs2rdW4 SvP6DCwzscnIGjM7DC0wtb3Y6nJ7Cy4tubHourK7Ci8qvaX4ivL7CTAnwZkIWzI8CTEkxY0YRzqJ sgbkyCgcB5tMWWHmIpYpYd4M8TPoiaJHWyxtOiPq1BxZ21ntunMh2cdc76AdCLcy2zp09/HdjHcO 4HmIQxOOw3gd5dOQz3P+EfoM5nGoW5MuErsf2Kytw+Oe2ns27dsxmD+PPr369RgKAAA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0055_image022.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhDAA4AHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAMAAgAG ADIAgAAAAAAAAAIpBBJmqJfK4FvTtUhvxVbq74WZ2I1miXLqxoLn6qYtCdPz+yB6slNYXQAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0055_image023.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhfwBsAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAEAAQB4 AGYAgAAAAAAAAAL/hI+py+0dwpu02osziHLrD4Yiw0nciKYqdBrtCsdf2bmRjOcPrbz6n6PVED6g MXUqHpTHZqZ1Yw2d1Ao0Kq1qJy+sg7nddr1fcljrAy/UZ2DavIO3f0U2ST6X1fHxaV5vZlfm96fC xEchWPgEp9i3aCjn+AgpAjbJhVg5mBVJuEm5psn4CSpaujFKasqSioqJATvHwxmk+leS6XbbJlRL xxtGexoMIkslmNt0fKQ4bMQMjEoUbVwsbVEdor2Cee2ZB/sNfiY+To5WfN69PqPejt58Dc8+Xa/B HZOfaX8Hut9p1T96p6yxcgWoX6uDAG0ovPOwHEFtBHdFDOjuYq917tWUHUwQjaLHj0tuXOT2jKTD W/lGqiypyQS9hshc9rBp8CWxVw01muKTUqewoELT+SyKNKnSpUybOn0KNarUqVSrWr2KNVFFJFv1 4ZR4dJnMsAlZXSELg+Y9mLi6cvWjFp9bFJLmXog7Qg3ebHatTdurFa3XfoBDhWPZ99fhWIULttX4 1Zbgtxkn87Vs6VzkepuhCe78NjE/uo2JisWMENtQ1A5Vi2HNVodojGn7znYs2ZZRz7BtpDsNqEpj arBvx3ZC03hq4VEmz+6g/GbL3tKjV88MGjv1u9mlJPa1WuR30zXRlh4eK/OuqtuxFgAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0004.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Abnormal Join Diagrams
•Cyclic join graphs (not trees), Case 1:
•T1.fkey1= =3DT2.pkey2 and T1.fkey1=3DT3.pkey3, with T2.pkey2=3DT3.pkey3, explicitly, or by transitivity.
•This is an opportunity, not a problem, allowing all possible join = orders between these three tables.<= /div>
T1
T2         T3
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0004_image024.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0004_image025.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhqQB6AHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAkAAwCW AHEAgQAAAAAAAJnMAAECAwL/hI+pyygPo5yt2ouz3pzND4ZQ93TmiW7iyraukMYy+db2Pee6c/d+ uwuefsSiSIi0GJdMSfLJa0qX0Of0yqzusFyqVtYNF78psflHNp3XvbSKDa+5L/G6fO6x64H4xP7P 0ncASHjUV4gIgpfISOHWCDlCFkkJo9VoERAAAFiVmKHpZwdVuBG6EEf613Gat2a1x6E5u1nxKhTL SlurZIY7ekL7FrYFHDzL4ZtjjNKa3KVTN+P8zDUjLagAHZOajWpdduvdgBV+Nk53hSKObquuptye PkVDLK/xPgx+r++Fb8+vnxGBUgLCy/Jvn0GCaDCIWXhwoMNtVaglaTJR4RNk/5f8udOIZBNHVR5d 5dsYaiQsieRAChmp8mJJUeWgxIyJZGYUejYtAsAZBOE3nih5KQBabIxJjEUz+YzGUhvRkE8TIF0W dafQl1WPduWW1cDJIFcZlAVLZOhWsl/Nth0SduqOsxXoRmyoVSlXoybs1sM7iClbvscIg8kqeO5b UIsTps0LOIdfDcgM3/VBU6+MlI1NCbummZPOwp1lTe6leTTpoqVbpg7bbFeSXZarPRYLm3RtHadd 61Wta85ux7eBQ/zF0vjxoMlzL0cuUfm04W47epGe4rPp1gzbiA6tmLb48eNlXseui7x69eajo+/b 24B26FTWUoXvqWDi5/QH7qznD5V+/wGIloD2EYhAY9bIVVct1KWxWCWKOCUSd/fNI6EjbvES34W+ QUNRgs7MJ8hTcHxHEU4k4uGMHiiaYdmKaXxSiowBSohbhjoytWOPzfkIJA45BkkkH0MWieQHkCWZ pFRMPmnJklAC6eSURS5lZY8fZanlh1ziiNqXlWQg5pjElVmKbWiuAteaLhbo5jlJxenSnHSCl9+d Qo6jZwjP3YkgllQGClouyxUAADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0005.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Abnormal Join Diagrams
•Cyclic join graphs (not trees), Case 1:
•T1.fkey1= =3DT2.pkey2 and T1.fkey1=3DT3.pkey3, with T2.pkey2=3DT3.pkey3, explicitly, or by transitivity.
•Subcases = A, B, and C show where the missing link in the graphs = may only be implied by transitivity.
T1
T2         T3
T1
T2         T3
T1
T2         T3
        = ;     A        = ;            &n= bsp;            = ;   B        = ;            &n= bsp;            = ;  C
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0005_image026.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0005_image027.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhLgAlAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAEAAAAn ACIAgAAAAAAAAAJbhI9poe3fgoS0Hjmtdpjtj2DguGTkJ56oqVpp67Iw9M60bEd4ntS84vuFdkJA sGgkCkUeZOn46zifylynOYVOt1wXCVvR3sA68ePKuarXbHW6DWeHq2d671spAAA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0005_image028.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhXAAiAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAQAAQBV ABwAgAAAAICAgAKYhI+py+0PTwix0opzm3pz3oURKEoTdZbqQpbnBaTrHMPim8i0a3e4otv5epkf qyXEIJXBIzH5WI6aDCrUM5RWtdfc02GccruGMdAaNXfVJraTjBV/Le4dO3yrz9R43hxq1qeCBvgn uEJI8yJ1uAfyl9fXqDjpR1RJmRgJg6JphxnSsgiHsAiZ16bn6IkIeqWaBUsqxDpLWgAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0005_image029.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhqQB6AHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAgAAgCW AHIAgQAAAICAgJnMAAECAwL/hI+pyywPo2i02ouz3lr6D07cSJYmGaYqSLHnC3PrTNdhjOeIzfe9 Diz5hsRZ8GgpKpc3pPPAjEofT+T0Kq3qsFymNtYNK78msblIHp3Xw3SHDee5L/G6bd6y6414xf6/ 0rcDSNjUV4joMpfI6LHYCAmRFklJpVUpApVY1XgREKCp94So8blg51S4YeoQZ/U3wtoAdwTI8YkL mncWBHubi7G2JVoCnMGLQ1ws+xaWXAfD3NwFgxp94vwCLehHXebKfeqNQhs+yyXEZl6BTm62Toel hgwfPyXzXn98vyGmj59lmrx//fjZG0dwXxSFCBMGC5gkm8OCXg4OnEhxicUr/xjnaYzYriPAMbsu isyIpiTHkx5JtjLJEmWbcytjtkwprqbNkURoQtzJc2Y3nUBl+sj5s6jRH0OTKmXYs2nFpzeFhppK NejRBAazLpXDdaFXd1EHYR0r0CoAsWi1Mr2Ks+3XGmFdyp1LF67auw9dsuULVehfwBu3rv1IOPDM s4nZVRzc2GdKyJGRTmZcWSoaypnNauTc2UAW0J3vOQ19eOHp0BxJR5a3+jXs2InREZU9m7ZcfyEJ v2uIVl2+rNuE2bSVuhy8SKIxkcXUBLr0kNOrs7WOHWf27W+Tc/+uqDn48Y48kz+v+Tx3y+rXs29v XSX86Y7nVy9sH5Li/JvS8idX5dZ/1jwn4DbaFKhcDgj6M8qCrr3iIA3/RFgeS/8BFh9qkvXGUgEA Ow== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0005_image030.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhNgAeAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAQAAQAt ABgAgAAAAAAAAAJahI+pELEPI2xO2nsa07g/aoDeGHJlRWIisqZRy5ruAif1vKGTjN80n/J9gB7h TlckXoxD5IhpUyadTVyMGsW6bhUoCaSjSK3iTNl65Ii15PXJGyyf0ZY53VMAADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0005_image031.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhXAAiAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAQAAQBV ABwAgAAAAICAgAKZhI+py+0PTZixhopzm1czDlDeCIEkYprnioqrGrrsK49w3M10bteSr8vchMAf L0g8YoYJJvLhhBab0+ciuqlSldaPVoG9frvhbY/bbaHF69IYWTay4sE4Xdp+lu/ufF3LFxF45jAo +HbGwWVoIeLnobiISBJJpgg2eXJpyXPBCLmp51KZhhmqp/YommmRqmrFmvRZ+vdKe1sAADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0005_image032.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhqQB6AHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAgAAgCW AHIAgQAAAICAgJnMAAECAwL/hI+pyywPo2i02ouz3lr6D07cSJbmGKZqSIHnC6PrTNdujOeHzfe9 DiT5hkRa8GgpKpc3pNPAjEohT+T0Gq0CsVymFtYNL78msblIlp3Xv3SGDW+7K/G6bd6y62d4xf7P 1wcFSJgiWIjIgpfI6DHXCBmRFkn58FUpspNY1WgR8Klpx1mY8RngV+eEqGHaEGcFyNGat3YUy2o6 61qr80eSe3rBi7NXAtxhlqN3ovsmRizKDKrGFRP9EiwUBvYak12yXdYtuBCuzUZOhyV+li68Tv3s /n4V3zXvPGVfj7/Bj3yvH4d/9AIK9CcFoMGDCsdgkMdwYMKC8CJK9EKRoMWG/2jULdyI0CGtaiD3 DfFYseRFJSj1qQSH0QHJlybllEtJcyWRXThzhuyISqPPnztvCh2aD2iCnkg5+jCapSlMlkGjSj1X dOnEq1izhorJVedJrWDDirU5qKxZpzzIilx71uZWuETHpqVKN27br0rzst0LQK3fhyIFD05S+O1h wngNL+ZJ1fFjqB2tTm5Z1PJlyJUlb77LUvPnqqE9b9Y3dzQC1KJV10utOjCm2HyzwD5dzaXr3Lov P5t5uN1HuOgghk2Fzueta/MigYbUFdMN6dRJVr++Fbt2oNu7A5btPbwKt+LLS1ptPr0l0uq9U26/ nTN87PLnU8dsv1LG/NAZ8yvvn9R/q+gl4Dg1FdgONwgOo8yCwMHiIF5uRHiHQBRKoBx/g9FHW329 5VQAADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0005_image033.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhLQAjAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAQAAQAm AB8AgAAAAAAAAAJUhI+Zoe2vgoS0AsmsjnN7g31fKG5kaZ0opa5P6zZwvHS0M9+grddZD9r9gBfM EGg8Io1ERFJ5YzYP0Kn1iqXxPFtZriLlPMfksreMNr+6mu8CdSwAADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0005_image034.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhXAAiAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAMAAQBW ABwAgAAAAICAgAKVhI+pGesP4wtNWkNbvbwj6kEZMIamBJ5MSrLqe7hqGcvwaYf0l99dz9mtNj4d 0XhUCIsXIMpZSzIj0Ek1Og1Kn1trN8tDmpbglYfcvDLVGLYDXWbDf+6Xen6u477YKd5X9Temp5XR I7hXwTeIhjhjCGYo5fhIWRhmWbnIyJOZ6IkytOkHStVZ+lgmSqiahdq6BivbUQAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0005_image035.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhqQB6AHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAgAAgCW AHIAgQAAAICAgJnMAAECAwL/hI+pyy0PX5u02ouz3rH7H23iSJYkiKap5ZnuO6ryTMvwjSf1zvdS Doz5hkRb8FgpKpcgpBPBjEp/z+P0Gq3msFymFtYNL78lsblIFp3Xw3SGDe+5WfF6be6w62l4xf4/ 03cASKgiWIiIgpfI+DHXCAnhFkkpQFZpCQCp1VgREDC495SI8blg54SYYcpQh0S4wToBZ/Wn8YkL SsEGZCuSq5tk1qtHkvs2fCNagsshpoxqIquR7BLtEqwWZu0qqNPFTet9ymUiPt5aLrSGvqvufNZO N7X+LD8vpb19j4FFbc+vHz1k+wIKzHcQnEGCWS4AXJhwjMOCECMqwfeuokUi/xivaNQncRbFjxt9 CMtIkiEakf5S1uPI0qNLkCvJtZxJs026mzj/Xdw5sGdOkzYRCh0qp2jDo0h3APXC9KXOb0aj+qwZ qqrVq1OzQt3a1CkUrWBL8qAasiw8mF5/ql3bVdPXt1y7LqWr0u5cvBPdyvXL16zYv1gD96251/BJ xGkVL2ab2HEeiZElK4VZ2TLalXc1X9bZ2fPYLKFFG6BX2jTq1Jo9khXt+rVlf0FhY5Jk+zbu2dt4 Gh6mEC+vh1ZdsesJiDCvgJuUq3qhW5Hz6NQxV7/OGbv2s223e2/S/bv43afHmyc//bx2P+rHf25P /Sn86pPnx49pH9Pj/JEO8yvvJNh/1+QlYDEnFGigOQguh8OCIwXhYGapRHiHPBS2kJKAdK1nGnsM plQAADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0006.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Cyclic Join Graphs, Case 1
•T1.fkey1= =3DT2.pkey2 and T1.fkey1=3DT3.pkey3, with T2.pkey2=3DT3.pkey3, explicitly, or by transitivity.
•Note that this involves a one-to-one join, though, which w= e discuss more, later.
T1
T2         T3
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0006_image036.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0006_image037.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhOgAfAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAQAAQAw ABkAgAAAAAAAAAJdhI+pF7EPo2xN2qscoBp7SBncRyYhc5ZfilYqxiLjO7nPTC+xvuc9b8ttghYc 7RcxlpA1oodZhAKFQ2fSqlKCsC9thntkSruxyjg843So25OX/TWr4Vf1my6b41cFADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0006_image038.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhXAAiAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAMAAQBV ABwAgAAAAAAAAAKYhI+py+0PTwixTkOr3ulyNoXeRz5jiYVphrbIWYov7MasTat1S2+y8tuRehbd Iij03ZRESTPZeEaNDioUIj1aq9lrioOMbL3ApWmM7ULVgDCTPC2aP2hytw5mu7J4uh4l1Tf0N8g1 tyMIKDKXqEhBmLbVyONWE5QxuZepYbQIV7ZZNAOJEyrmZKp5mLS4+vmK6goLSzqbVAAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0006_image039.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhqQB6AHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAkAAgCW AHEAgQAAAAAAAJnMAAECAwL/hI+pyygPo5St2ouz3rzND4ZPR5bmuYnqyrboC6ftTNdQjOeAzfeu DuT4hkRR8FgpKpcRpNPAjDKfR6l1Sc1dt8Xsiwsmek3hsm/cMat5aM36XWtf4PSZ3FPPr+4KvV/F d/A3GBJIePhxh7hIgcb4eOMFOSmQ9VgRELDjR3WYkZmQ50S4AboAN8rJYcrwhqS6qjlnFgSrkYkr a0GrI1qS61amVXcCLAOGQ4zCKsSVjPrC3LwFQxfYR40CfR2aTbbGjSd14houHvUtbL5rVcK7zj6e FgYfPDXtXZ8hb5+vj8EPILJ/x5QUbEcQX5d9zhIqHMKwocODEGf5mxhRjMUr/xhJYImHsOPDM0k4 ivRo8FzAkxTZqLzHct7CUyZjyqxIc6XNfiRzwtx5s2c3dEBRasT2s+jIOD5TKg3aA+nHp1BdOiBK tSrTq1OzLt0KpavXljaGzhz71Q7Xo2jT/hDktC1ZsGLlCmQb125GoXn1bsTZ1y/InoEFv7Ra2HDT qGHZKi65MPHjtRUlT96Usi4QaZ90vfqoGYexWJyHYQkdLZfq1axbV5mCelnr2bRx1UKXNMfoUrZf w44No/QF4THaYb2MGXfuxxx1GqaUSDG9mnZpSRwLjp5SUeUcehrUBvqI5OLLZzaPPnL69YjZu1fb +L18EJTn2x8P9/59qfrlLz7un14rAL532IDQQWZgeX8lCMleDC4y14N6aCWhNe5UOKE2GG5TzYba 3ebhcp+FaJUhJBbiXYhP7XcZhAkVAAA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0007.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Abnormal Join Diagrams
•Cyclic join graphs (not trees), Case 2:
•T1.fkey1= =3DT2.pkey2 and T1.fkey2=3DT3.pkey3, with T2.fkey2=3DT3.pkey3, explicitly.
•<= /span>Denormalized fkey2 in T1 is probably not needed.&= #13;
•Denormali= zation is likely to fool the optimizer into expecting that = almost no rows will satisfy all three joins, sinc= e the optimizer views these= joins as statistically independent conditions. This false expectation is likely to lead the optimizer to ma= ke bad choices.
T1
T2         T3
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0007_image040.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0007_image041.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhqQB6AHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAkAAwCW AHEAgQAAAAAAAJnMAAECAwL/hI+pyygPo5yt2ouz3pzND4ZQ93TmiW7iyraukMYy+db2Pee6c/d+ uwuefsSiSIi0GJdMSfLJa0qX0Of0yqzusFyqVtYNF78psflHNp3XvbSKDa+5L/G6fO6x64H4xP7P 0ncASHjUV4gIgpfISOHWCDlCFkkJo9VoERAAAFiVmKHpZwdVuBG6EEf613Gat2a1x6E5u1nxKhTL SlurZIY7ekL7FrYFHDzL4ZtjjNKa3KVTN+P8zDUjLagAHZOajWpdduvdgBV+Nk53hSKObquuptye PkVDLK/xPgx+r++Fb8+vnxGBUgLCy/Jvn0GCaDCIWXhwoMNtVaglaTJR4RNk/5f8udOIZBNHVR5d 5dsYaiQsieRAChmp8mJJUeWgxIyJZGYUejYtAsAZBOE3nih5KQBabIxJjEUz+YzGUhvRkE8TIF0W dafQl1WPduWW1cDJIFcZlAVLZOhWsl/Nth0SduqOsxXoRmyoVSlXoybs1sM7iClbvscIg8kqeO5b UIsTps0LOIdfDcgM3/VBU6+MlI1NCbummZPOwp1lTe6leTTpoqVbpg7bbFeSXZarPRYLm3RtHadd 61Wta85ux7eBQ/zF0vjxoMlzL0cuUbm31n+VSs/2WaaX67poe/8OXnt07p7Bmz8v3jp54b0ZolnL j7p7zImfQ6dS3z7UggX1J6bln59/CDRmjVx11TKcG4tVoohTIsnHVUYMOuIWL+1RhRoxFA3oTHbY WQaHaADh5GEfzugh4hmWlfgIIinGwuJ/leA2YY1M2YhjcznuiAONPP7Ih49ADvkBZEQSKdWRSlpi 5JI7JukkkEtFieNHVFbp25UTzqPljBJ2iQlxYJZi25irwGUmimilyQ5obG6Y05vweSJnG+jUGcJz cgo45ZN8uqnmcgUAADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0008.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Abnormal Join Diagrams
•Cyclic join graphs (not trees), Case 3:
•T1.fkey1= =3DT2.pkey1 and T1.fkey2=3DT3.pkey2, and T2.col3=3DT3.col4, explicitly.
•Treat the= last join as a constrained filter condition that we cannot = evaluate without both T2 and T3.
T1
T2         T3
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0008_image042.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0008_image043.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhNgAiAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAQAAQAs AB8AgAAAAAAAAAJphI+pEeoPo2FN2nsaxRzu3YUTk5EiB5bVKaWIyy6mA8fj+tSsLuP7bOFhhDlg h+gxXpAR5ksZcgKkLWiCGrTebDTSyqu1UUxjH/cKGp+Tn/K6W1a/2eH5y47P6/f8fjEOGCgoNFho CFQAADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0008_image044.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhXAAiAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAQAAQBV ABwAgAAAAAAAAAKYhI+py+0PTwixTkOr3ulyNlHhR0ZeKZ0jynYZu2JxC7/lLJ/0bX94qttxgpsf ItQTWpJLpsupdBBN04Ux2qhKtVYu9ggFebvhr6w4Fpe/6VxtHW1f0XBhej5s08Z4n7720EciuBdG OPiXh0RmBgRQp0h0+JZIpeLW+ARJB1TJM4kG5omCtKnxUpqZNXqaqvq6ygrbIjtLUwAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0008_image045.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhqQB6AHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAkAAwCW AHEAgQAAAAAAAJnMAAECAwL/hI+pyygPo5yt2ouz3pzND4ZQ93TmiW7iyraukMYy+db2Pee6c/d+ uwuefsSiSIi0GJdMSfLJa0qX0Of0yqzusFyqVtYNF78psflHNp3XvbSKDa+5L/G6fO6x64H4xP7P 0ncASHjUV4gIgpfISOHWCDlCFkkJo9VoERAAAFiVmKHpZwdVuBG6EEf613Gat2a1x6E5u1nxKhTL SlurZIY7ekL7FrYFHDzL4ZtjjNKa3KVTN+P8zDUjLagAHZOajWpdduvdgBV+Nk53hSKObquuptye PkVDLK/xPgx+r++Fb8+vnxGBUgLCy/Jvn0GCaDCIWXhwoMNtEOtJ7KWwIkMc//PKabQ4BmO+j9VC kstIcqMNdx5TRmzoip7LIf60jZwJ0sdJmThf6oyJsCdNk6IKCh1K5JvRo0h/Fg3K1GebpzWjloQ5 qIlVc1gNLN2ac2rWqmBVvqCatKxUjmOJqr0q1ivZtxlqzqU7kehdvCKx7uW70+RfwEoFXySc17Bb xH2dDmbcNuRjyJwQToastfJiyja9QOXcWeJn0JEHjiatOUtm1KXHrGat7ivocrIpW+Npu9KH3Lp3 Ez5HsSyc1Chn6iH+sOKnUpN6W3IOPXP06WmRU78eSC727dm1c//uyDv48aHHfy9vfjv69NSBso/O 8j38wPJ7d6xPqS7+/An3fyaC699xTQXYDVcEnlPMgcEhoeBrXzTolDcQhvBRg1uBx5p1w31UAAA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0009.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Cyclic Join Graphs, Case 3
•T1.fkey1= =3DT2.pkey1 and T1.fkey2=3DT3.pkey2, and T2.col3=3DT3.col4, explicitly.
•The const= rained filter condition requires that you join to both = tables to pick up the filter selectivity.<= /span>
T1
T2         T3
0.2=
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0009_image046.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0009_image047.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhOgAiAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAQAAQAw AB8AgAAAAAAAAAJshI+pGLEPo2xN2pucoRr7Rx3cRzKVeZZeqIzqxS7uC8WgTaMYnvORXwL+Ui/h hEgyWpS3Do2ZQT6lNerUWcXmRFaudusFfKHXU4XcY3G+4KgaXV7P2kc5Pcm+6/f8vv9PJyc4SLgG U4iY+FUAADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0009_image048.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhXAAiAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAQAAQBV ABwAgAAAAAAAAAKZhI+py+0PTwixTkOr3ulyNlHhR0ZeKZ0jynYZu6ZvC89kjOD0bXO63NtpTp9f jijcIIdLhTH5aEKeTikUFIxaF9Sry7fFZr1HZbjRJZ9ltbHafYSb1sJ1mimvy+/mPC3MB+ZX4xBY RMcTEmSYCDDYt8RYovhoEZMhiaL49kJJxrXpRRT66UIK1Xlaarrq6NkKC4oYi1pJC1UAADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0009_image049.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhqQB6AHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAkAAwCW AHEAgQAAAAAAAJnMAP///wL/hI+pyygPo5yt2ouz3pzND4ZQR5bmmYnqyrYCCseaS9f2K+f6zfeq Diz5hsRP8HgpKpc4pNPAjBafTqmVSN1dt75sjAvueU/h8m1MMqtr6M367Won4XSWvFHPr+4KvV/E d/A3CBJIeEghh7gYocj4iPYo2fS0aBEQAOBHdZiBmUDH+bfxufBWqddRimdWlceBGZtZ0Rr0qio7 a1FmG1oi6wamBXcCHMyV41usy4EsQwyz2uGMAh0IegVzem2aTabGzSr1zRu+Oy5Ubn4elS68jmE1 /Q4/wzS/VY+v1Eytb7/kmLx/+7AAHEiwH794/hIeHJIin8M0C9khnKgQ4hxv/xgzaqTFsaNHMSAv ihzJoyS6kxQ/dlvJsiDJl/dimpjiAaZNlDbEBdx502CfdkDddaGJs6jRlEN/Kl3K1EHNpy2PSq1I tWpUQU6zyjxzVajXrz3DuhwrcCaUpGjJ0sAmtm3arWzlzgW79qzdiC7r7mUo1O9fi1YFD/ZZOO5h wlENL25q0HGQAZQrW76MeYAonJKBZP6ceXNkxU5Am64sWiPWJ6dPpz66unRr0KgCxkYym7Yr27cn 5w69m19X2b8vBxc+/HHee1OVL2fe/PGkQtKRmZTLSyLaNZroAa3TvZbDToMiTX/w/Lz60evbJ3YP v2z4+PTtcK2PP4TZ/PwppUfvXx9kAOIn4IDx5WTggYglqJ5KDJ630YOT8CUhI3dVuAlPGFrj1obq BOUhd8mE6F0vJPaGxIlbXaMiIBOdmFV/zt2XoUMFAAA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0010.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
        = ;             T1 0.01
T2        = ;         T3 0.5        T4 0.3=
        = ;             T5 0.4
0.2=
Cyclic Join Graphs, Case 3
•
•<= /span>Probable best join order:
T1, T4= , T3, T2 (picking up the combined filter), T5
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0010_image050.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhnQA6AHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAACX ADYAgQAAAAAAAP///wECAwL/hI+py+0PggwR2ouz3nzPD1LdSJZmGU5R+p3uC3OseEjITMf6bs6N vcDleMRiwgcBPnDGZgyJUV6EzqqGNZJ6oNaugdvRZpleIhg1dJHL6BRP/Fyzk1gj3Hye53fuLtVf B9gy97V3xRCoN0h4IxeVY+i1yIgIBieURlmYqamAqRXZedfpsBZCOoUa1gei6oqn8iprxzlre4ub q7uLK+D7Cxws7MtbDDCMjGzMm9wMvCz4GeJMLZ0IbWGdQu2sfYrtxN0M3iuuTG5rfo4uqz7MPusu DN8u/0zvan+Pj6r/yw8QTUB0owbqKmgQF8KEthYylOXwoauIElFRrCiqFsZXGhc3Muroscy3kBAn kVRl8iRKkCqrsGzpcmABADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0010_image051.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0011.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Abnormal Join Diagrams
•Cyclic join graphs (not trees), Case 4:
•T1.fkey1= =3DT2.pkey1 and T1.fkey2=3DT3.pkey_col1, and T2.col=3DT3.pkey_col2, explicitly.
•Treat both joins to T3 as a constrained join that we cannot = perform from either T1 or T2 without the other.
T1
T2         T3
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0011_image052.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0011_image053.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhMgAhAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAQAAAAs AB8AgAAAAAAAAAJmhI+pFusPXZixWtbAzLenfYDeKIbUeJWIikrnwrbr63KyaT8xute3RrP0KkNI 0Sc7foI8JvIHzMGcSSpGCu0pW6qNFRrFYcGzjJcc8arRxuiY/YHL5/S6/Y73qPf8vl/nFyh4dlAA ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0011_image054.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhXAAiAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAUAAgBU ABsAgAAAAAAAAAKUhI+py+0PTQgxTlmzVnevSYHeaFHkIUrdySIrmaJve85b7Nr0bY548ttpdKUe Iygs+YhAZvJorCCh0afD+cFytFYAV1Ytdh9fb3kbHquk52x6zJ0O5VYtfd6GvVV7D6i/43S3BMjy pzOoV6gYlFhzkQfxh6aW44hnkhlJuMgDtul32LUyWYkGWoT6aCpq6tqg+tpSAAA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0011_image055.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhqQB6AHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAkAAgCW AHIAgQAAAAAAAJnMAAECAwL/hI+pyywPY5u02ouz1rH7L23iSJYjiKYq9Znue6ryTK/wjSP1zvdP DiT5hsRZ8FgpKpcopFPHjEoFT+f0Gq3msFymFtYNK78msblI3pzXxDSGDfe5k/H6bt6w62t4xf5v 1GcASJgiCFCYCNKn2Ogx5xgZ8iVZ+aNleYnYiKloERBw8PfkeQG6YGdFqHHqEIe0yho68QoEuAGa O0t7FjSKq7vLa7a1R6Kb0XtjfJyrJracatL6HAZW60ItYu1Sd+jX1Q337YpVgk0Obh6zlj48JdTu znLFzj1PB89BjH9Rvw+tnz99bwIKHCglmcGDFgg2XMgwn5eH9yIinEivokWK/0s4rtuoEGOecCCr jcn4sWTIk+VSqizYcaTLlzDRyPxHEyBLdQ5zrmzTMqFPkzZ5Zhm6LaZRpUh1Ak0ws+nFIah6Sq35 VJTVqx6pQhXKlaicr0fDOvWqVaRZrGgHlV37c2zanXDZ9iBLt25XuZvy6pWYVe1fwG0FD35X1PDh oE8VL67K0vFjvI2ZTmYMVPLlvjE1Xxb6djNlm6FFz+1Y2nS91KJXs55sDqxpzlm2PuZC2/Jt3FH/ Zlq0uBfJumxy2/bpTd7LVXrwSTLOKelvG9OrE7SOXWn27YW5e6cB5bv4JqfHm6cy+vz3peq5Y26P /T386YjnV69vPxPh/M/38itPdNZ/sQQooDTxFGhgGQgWZ8uCw8HioGdHRHjHPBQ+otJ/emU3200P vlQAADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0012.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Cyclic join graphs, Case 4
•T1.fkey1= =3DT2.pkey1 and T1.fkey2=3DT3.pkey_col1, and T2.col=3DT3.pkey_col2, explicitly.
•Treat both joins to T3 as a constrained join that we must perform from both T1 and T2 at once.
T1
T2        = ;   
T3
T1
T2         T3
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0012_image056.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0012_image057.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhngCTAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAoAAgCP AIwAgQAAAAAAAJnMAAECAwL/hI+py+0nopx0vouz3pzXD4ZCR5bm2YjqKqHuCyvsTMf2vdE6i/c+ sgvyfkSY8LgqKkvIpmgJzTinoah1Qc1+rlyA9lvpLsFkiphYTrfOOLU7wo6953HX/F433fejfI6/ 5ycFCCjoQIhoiIXIqAjE2OgIOdnnR3lZeYa5qbnp2eUZmqkkKgpVWkqKmoq2OhEQIFPo43oAC5sS 2Ga6gHuBZ8N54YtBJxfZ6maErKr2ksilfELI5qzHl2dNgm1ZxqQrmNYBrujtYexoYD4onf749Sfu LgvGvj6fQGYPj5+rVXyvCLFl/x7ogxJr4DN+/gouuaVwYRaDDJXcAnDxhsNF/xsFEotop+M7KlEy GgAZkiQDkT9MnkQ5bSJHlRZBwow5ZSVLHC4R3LxGE8LOGz19xuohc2ROj0cZFJXohF7QHk8T/ATa RGpUIlWtNtW4VGhSqldffg0W1stQF117nT22Vd3aE23d/khbMUZdt28JIlEal23CsnaRhs1LF6IJ wtu2HhTMuLDhrGoRk4AY2WtfqEfkWu6AGUXmeJ0rf94w2ulmzkE8n+6y9xvlgHFS72tt+vUV2wD/ 5tZdkjdF3+1qr0bhuHgJ4RiPI/f9u15izM4VMD80+41o6qEfxG4MHRhk7iiv6wwvXi95k+ZnCgGs jef67+egu9b+kPrk0mLJ9f8bzl9/d/hSnTuBCYgHfYYciOCABUpCmXt7tGcFg/AN+F8+EZ4HTYbR 6fALJB5+WEOIIvaTFnaT4JMigMwsaKGKJ5YTo4wzZtPibR3GkaMGwlRTo44vVthjfZeAUqSRRxIZ 5DifjJGkbD/eFSVWmFBZpZVXgjXVLryk1KWXX0qZpV9jGtVQmMmgYtRmj0XhSgsRxcdknP5FY2d6 neRJ5558vlnHn9LRKGiZSBZqXzqIvpfhoiCO6OgQI5IY6SiQVmrGpFpFqimHi3Zqo6CgusjnqCbm aaqQZ6YaaiisOrnqq71tKSt4K9b63JC42nrnrjjh5ytafQYLF3DEgtnkscIMMqosnjM0200YKBQA ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0012_image058.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhGQAUAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAEAAQAS AA0AgAAAAAAAAAIdTICpC3YMkXtxzQovVnqn7mlGOFFYaVal16zsVAAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0012_image059.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhMgAhAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAQAAQAs AB4AgAAAAAAAAAJjhI+pFrEPY3KtyYtB1Tv7aTHd541iSF7mUTlptCLt+8QgSp+Z/fE1TvJBhENg ySghJpE/V47jhDFTRGVvag0CKdMnlDXzSl1h8RjaNaOz5rT6DY/L5/R6roXP6/flBf8P2FcAADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0012_image060.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhXAAiAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAQAAQBU ABwAgAAAAAAAAAKahI+py+0PTQix0oozm7pNynUiFI7HJ5Xmel4r2rrsK4swcs+j2uU4r8sAMb5E MRgZJpW/GpLkXEYVn+lzwfRkjccr1brZfsVXcsrMBXsB5qqpu2arz/MH3Ct2z+7PrV7HF5T1J4j2 ZkWIFHiYKGeoAVMHmdhYVslCubinOXlR9bjzieciGpfGKRQj2QcqVGrqYXoJS3tSe+tQAAA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0012_image061.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/AB6AHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAgAAgDx AHEAgQAAAAAAAJnMALvg4wL/hI+pyygPo5St2ouz3rz7D4bTSJZPiKbqyrbuYcbyTL/2jecezfd+ pAsKh6yf8VgjKpfMBPIJNTWn1Fv0igVWt9xd9nvtiscMsBlLTovP7Kj6PW3Ln/C6cI5H2ve2vN/I F6jyR+gjeOhVqDiD2IixCMnoOKkQaRlDmQlwyVmi2dgZSvJ5KGpKQbp3ugqRWscKK+CqxtoQELBp OUsWmnHrtLjb1bnxu6AoXEVcjNtAmEx16WFs4QfdpMtxu91cjXetlN3B3X3xDX4XDMLNcY6eox6y ncj2jhMvX97eZm9VyKKvA79+Lv7xqkewyB84CBOmWPjKjMOH1lRJnAgiT6mL/xgFznEEpiO9M5RC itTgDuSXkxlSqkTD0pwcUitjVvhIM4tNZwNT6dxZaeasn0Bh9BwapqhRksmIFm3YNKlSqFHdPGUK DiZQqtCk7sT6zmtMsOjEsuRozyypgC1rJlSridoGk2+tzgowgK03rXWhCMObd65bh3ApAQ7cli9h u6kOI5apuC+duwMqP97LGGPmT44v8yzcD3Sjzp6PiU67ORPp0sBSL/brajXrpZPPnjZALrfu3bx7 +yZnOXhlvblgj3WtQLbw5cybO38Ofbne26iNW1AePbv27c3ZUg+LPAF27uTLP/ceXnLtCuPNu39f +nvZ2+3h2+euT35W+vf79/8vF5lm/PlH4HvdBPhaergVyKB5zSConnUM1NdghZbhAiFBg01oYYfZ BZBhdSECQKGHDYI44n5OcWhii9KtGKGCJbr4X3H6uYIWizS2+OCGIvoo3o480najJkctMKOQ5eVH l4pcBamkhUw2KYxG7EVZYUBH5mSlLVgyiN6WoCBz5Zf+TQdRRJxcZ2aNZUgTTiwnsPdbnXbeCZxz xBkgZ59kOSLbnnz6SWiKdqwm6KCFLnrEWswlqiijkiYRl3QoTYqpDI4GBymRmX6qRaUXegRqqbJs Ohtmpmba2HAZrcpqq52+Ceuksc36Wa2F3oUrrbr6uUuvuf4Ki1KtEbuKsUE1IWuKsqYxu4yzy0IL ibSqUuuStcNi+6e2j3ALo7ekgvuDuH2QK4W5Q5Cr7hiltjtJs/DqUAAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0013.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
        = ;         IC
Ind = 0.0002        
        = ;            &n= bsp;     TC
Cyclic join graphs, Case 4
SELECT TC.Column_Name
FROM Indexes Ind, Index_Columns IC, Table_Columns TC= 3;
WHERE Ind.Index_Name=3D’EMPLOYEES_X1’ <= /b>
  AND Ind.Index_ID=3DIC.Index_ID
  AND Ind.Table_ID=3DTC.Table_ID
  AND IC.Column_Number=3DTC.Column_Number
ORDER BY IC.Index_Position ASC
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0013_image062.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhYwBQAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAMAAQBf AE0AgAAAAAAAAAL/hI+py+0cwpu02tsiiBL7D1KcpIXm+Y1HibYuxCHxS7uqwta6N3Z4vgs+bhmf 8PgDwpBM2WzybAaJFaX0Ra1ar6bsZcvleb/GMGiMiZrJYPFa3Ia/tfFzeZ6s2/FFfUjNh1bj1yQ4 eLdmuEM4BcjEeAiJJYnSg0fZ5RiGmaJp5kmjuIkYCvpm+id6SnqiOscJAyuFauHKt8HKlnub1Glg y/u7G0sbnCDZM2zcq6u8/NPs/OzkXDy9dF0onZ25zb33fSQbPjROXnTe6J2uxa5j7k4dXzo/yQqf DZa8Ht62b/2N1j98idYNnAfw2D925gbyu0IQ18JnCWsdDBaRmKVLOBkdOCy4aqK2Wx/FdbQj8lC+ lJUecimZyuWni24Q0hRxctZNdPUW7HTSs1zKiu4+5qT4M6hFDQUAADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0013_image063.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0014.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Abnormal Join Diagrams
•Queries with multiple, disconnected trees:
T1        = ;             T2
T1        = ;             T2
OR
•Treat each= tree as a separate query to tune independently.
•Find the rowcount for each independent query.
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0014_image064.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0014_image065.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhHQAfAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAQAAQAW ABoAgAAAAAAAAAJChI95EerK2ptRTlgvbkyjaHQeAIbWVZpeqqLid27OK8/xQie0xGJw7zMBg6SM 5lXJBY0r3gj2/A0pzGj1OW1GdYcCADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0014_image066.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhCwAfAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAMAAgAF ABoAgAAAAAAAAAIYRA6GGnnN3HoS0oivntz2Cj4itUFG4iQFADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0014_image067.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhHwAfAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAEAAQAY ABgAgAAAAAAAAAIzRICpyxbXIntQWldvpNryvj1gmI3JZypoiqysm8KmPNKg3eGafnFlTfndeI2g MBdkqQIFADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0014_image068.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhyAAuAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAEAAQDA ACoAgQAAAAAAAJnMAAECAwL/hI+py+0Po5z0hVCz3rz7DwIXFpbmiabZqLbuC29sTNf2O9+OwPf+ D/zphqEc0RBMKpfAo9NhHDKn1OnzGrVVt9zldUKVZGPdsjn4TZyZosttDY9f4+YX/b6W4umlvb+s 9bfHIVhYRWNYSJHIqETWqLgDOeljRxmpcKnpopmY2TnZAup5MEqZYkoKkAqJwur52ugaK7hKa3hy W6uLGcL7C9yTG4xnSww3fCynjGzCLGf8zJUsDYhUfUiNnV26zTbrHfYZLqRCbrUwiLBpfu7YAP3A aOneJG+9WAtTXw4x3VEMET8BFdD5yhOIT7pQGZKI6kIEYQRVae4ZjAixoZ+KIhrtzeFGSCHHkR6+ OatDMuUHj+1AqnwJc53JmDRrdiP4oAAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0014_image069.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhWgAtAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAQBR ACkAgQAAAAAAAJnMAAECAwKrhI+pyxvRopy0qoef3by7nHniKGGAFpKqap6Qq60y17pHOudRbd+8 vhEIh8NLzJfAASPEpvPpPPqkywT0is0Kf1Wt94vtgsfk56qMTjc76rZbUHnL1cy5Hb2468vWvR98 8CfoFThoeAVwqGi22Oj4CBkpOUlZaXkpmYipZ7Bph+Ap1xealkeKxzCn0CjRFidIgRrEaQGoIsoW pkM3AlXVOfYrDJw1bAy61lAAADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0015.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Queries with Multiple, Disconnected Trees
T1        = ;             T2
T1        = ;             T2
OR
•If each isolated tree returns at most one row, you have, effectively, a query that combines two short quer= ies efficiently.
•If just o= ne of the isolated trees returns multiple rows, execute the single-row query(s) first, and consid= er separating the queries to reduce data volumes.
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0015_image070.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0015_image071.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhHQAfAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAQAAQAW ABoAgAAAAAAAAAJEhI95EerJ2JtRThWBvctunhkf9Y1O2IGmiaBt87hvzIowVNutluu2fLpVOD4e EAOr9HxHGtH5fA2jxmXQirxRi1vNoQAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0015_image072.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhDAAfAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAMAAQAH ABsAgAAAAAAAAAIgBBKmqMb9VoJTtlNdxjfqvnkcBZbkiSFq+qjRKoXXoRQAOx== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0015_image073.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhHgAeAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAEAAQAY ABgAgAAAAAAAAAIyTICpy2DXInuyJmorznHzaXxdKIJQqXioQ66IisKlLNKfzeEZds5P79ONhCbi ELgKFAAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0015_image074.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhyAAuAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAQDA ACkAgQAAAAAAAJnMAAECAwL/hI+py+0Po5z0hVCz3rz7DwIXFpbmiabZqLbuC29srAn2jd/0zh9z 38gJh8Qc8Nj5IQ3FpvO5jDKUwKf1CpUuqTus99vUPsC6BRdGTqvLYsDaSuq+5+QoPd2667/HPR3k F3jFI7hXU4hYRJMYKMH4KIQG2Rg0aSngclm4oDmZ2Ul5AAqZNyooasqYknrqxpqI8noqixhLa3jb WpKLy2to4hvMeyJ851qsRoz8xrRcB+ycfBw9qEzNh3qdBa2NpdAdZgu+jTAeqWLuxGDM+fiSTjSG 57j5Dm8EAXaot3hvM+HNwx859yqos4atxxwH7jacK1Wtzzx6odpEOGjHC4dfHhb3dZymCBCzjyQF huSWsKTKjWw+WVkJM2a7IQ4KAAA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0015_image075.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhWgAtAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAABR ACkAgQAAAAAAAJnMAAECAwKnhI+pyxsNo5zUgYBf3by7DHriKGXXY5LqmmJGusYc7L61jEfwiez5 f/DdbBrgRIBMKpdLxZBohDCn1Kqy94wCrNwuV3vwisfWHPmMpo7S7Pa14o7HJfK6u2HPtxX6fjrh F0gWJljYZWCY+KXI2Oj4CBkpOUlZaXmJmYm0pZlH2CmHADoHOMrGYIqGF4qqSHdKIQg3KKK3ISbD ylEGBLv2BkYLpsW7UAAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0016.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Queries with Multiple, Disconnected Trees
T1        = ;             T2
T1        = ;             T2
OR
•If multip= le isolated trees each return multiple rows, you probably are missing a join that would connect the trees into a single tree.
•If not, c= hange the application to explicitly separate the queries - avoiding the Cartesian product, you = will get the same data with f= ewer rows.
•DISTINCT, in the SEL= ECT list, is a red flag that some problem like this is causing apparently duplicate rows, which a developer has thrown a band-aid onto – the solution is to avoid= both the problem and the DISTINCT!
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0016_image076.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0016_image077.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhHQAfAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAQAAQAW ABoAgAAAAAAAAAJEhI95EerJ2JtRThWBvctunhkf9Y1O2IGmiaBt87hvzIowVNutluu2fLpVOD4e EAOr9HxHGtH5fA2jxmXQirxRi1vNoQAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0016_image078.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhDAAfAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAMAAQAH ABsAgAAAAAAAAAIgBBKmqMb9VoJTtlNdxjfqvnkcBZbkiSFq+qjRKoXXoRQAOx== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0016_image079.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhHgAeAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAEAAQAY ABgAgAAAAAAAAAIyTICpy2DXInuyJmorznHzaXxdKIJQqXioQ66IisKlLNKfzeEZds5P79ONhCbi ELgKFAAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0016_image080.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhyAAuAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAQDA ACkAgQAAAAAAAJnMAAECAwL/hI+py+0Po5z0hVCz3rz7DwIXFpbmiabZqLbuC29srAn2jd/0zh9z 38gJh8Qc8Nj5IQ3FpvO5jDKUwKf1CpUuqTus99vUPsC6BRdGTqvLYsDaSuq+5+QoPd2667/HPR3k F3jFI7hXU4hYRJMYKMH4KIQG2Rg0aSngclm4oDmZ2Ul5AAqZNyooasqYknrqxpqI8noqixhLa3jb WpKLy2to4hvMeyJ851qsRoz8xrRcB+ycfBw9qEzNh3qdBa2NpdAdZgu+jTAeqWLuxGDM+fiSTjSG 57j5Dm8EAXaot3hvM+HNwx859yqos4atxxwH7jacK1Wtzzx6odpEOGjHC4dfHhb3dZymCBCzjyQF huSWsKTKjWw+WVkJM2a7IQ4KAAA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0016_image081.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhWgAtAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAABR ACkAgQAAAAAAAJnMAAECAwKnhI+pyxsNo5zUgYBf3by7DHriKGXXY5LqmmJGusYc7L61jEfwiez5 f/DdbBrgRIBMKpdLxZBohDCn1Kqy94wCrNwuV3vwisfWHPmMpo7S7Pa14o7HJfK6u2HPtxX6fjrh F0gWJljYZWCY+KXI2Oj4CBkpOUlZaXmJmYm0pZlH2CmHADoHOMrGYIqGF4qqSHdKIQg3KKK3ISbD ylEGBLv2BkYLpsW7UAAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0017.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Abnormal Join Diagrams
•Trees with multiple ro= ots:
Root1        = ;           Root2
Master
5        = ;      30
       1   1
•For each Master, we get every possible combination of the 5 matching Root1s and the 30 matching Root2s - 150 rows!
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0017_image082.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0017_image083.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh1wBYAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAMAAADO AFIAgAAAAAAAAAL/hI+pGLEPo5y02ouz3rU1DoYi54zmGXkeyrag6sbiB8DyjRvqmvcTXeP5hrOV kIgMlg7HpPMHtD19QEZ1imU2pVnZVfvtIrk6svjUtIbPuTR4zSYtIe64y2wN2tFzen0/gqf2B2gB lyBYKHeopaS4QagQ+djBqOZIeWEpOZm5kPhA0+mplwFKijiaV9aHmtpquEmqysnq6hdCW3gqESX7 +OsXbMfb6wuLqhuqLMb8OefcjAw5nFVcCV19pp0STXQdm3rbyAL+5I17eYsurMieDrY+HfiOYm7a Wk81T899p78sn78xA2Px23fQRBiAXhIqLJiLYTdYEHtUNOiwnERj/88ynruIkaBHPgGBjeRzkhpI fCV3pXy4suPLh+0AxcRwT9NNfNM2RpyJskXOGHB8vgBqT99QokV3HmWz9BVSofOM8owT1dYeRlZ1 2uSW9UakrpVcDguLw5JTnVMb/iJbhB/cbpmEIEO7D0pbOZ5gZFOy16LatcYCp93xxnBevU4I/xOF N4msuatmIUY8rhNlcn13eHbMFNs30G7HcXJImbRpyRnnql49ujXozbAXlz1cO/cztrh1+9Yqmqni 31jOOn1NvI3x4aWS+64mEblzsdAV057e8CpJ7M9TKmXOnbX3itfD/1NZxHz3lxClqw+twVv59xrZ +3NP/zz6+ODzj2M+etJ8/gW1CE74DZhUDXx5hWBtUdFyYINxyTdZhBL+VJBm/V0o2FSTWMhhenFN FKKD/QS0YYnJLBSZig5+4ZmLJW7Rooyw0ZiijZaVAqKO29iQo4+W9SjkjzUWmRtmSMrYQAEAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0017_image084.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhRAFsAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAAAAQBC AWkAgQAAAAAAAJnMAAECAwL/hI+py+0tohSv2ouz3rz7D4bimE3mKZHqyrbuC8cgStPyjef6zs/1 b+sJh8SiUQNM/o7MpvPpUkqX0Kr1ij1Mt7Ws9wvGccfdsPmM3pDXwbSbl3xb2PSy/K6qp976vh0P eOH39zVoeBKYyHAIFMb4GKGoCCmVRUkpKXfJdbWJmWnmSVYl+glqWTrqlGp6SsqqygQL6Wo1G1t0 S1u7qjtm5LvLexT8S1T8OAyMbNzDzKg89IybM30YDWdNfaNtiC3WzaYTPvgdQ05Xja5nHrVex/0O 304iz35uP0//kd+H3y9uHweA/mAQTCcQw0E/LxYiTOjAIUN3ErdBNFCxXIuM/2suYuR4bQXIjgJH JhNpklO7lK1CsGym7GVLHzKnxKwpzCXOLbx2zlTjUyWooDkHEhU66Si0DkphBmq6FChUnommhkRi FammrBpLcKV652tXQWJtbi1bkCxaJWfX6qvg1uyZuGnh0mX7IkAAFnfvPegrd4VevfUAv11kGK/g wYR1Jg4I4bHiEYMBMOYn+aGCzIFBVLZ82Shnix9HT/bwGTTovVhNO9XiutKH1KpXe40NFgFurRho 17Y9Z3fu0sKHP/D9G3jE4rJ1MyfpAHly6c6fN0pg/fAB6o23d8ee/Tr48PqoJzdAm3yczer9fFfw Hn2A9qdh079PLjL+/dOW89b/H8xfAA44i10EHiiKgQguWNQCDD4YlX8QTqidgxReCJmAGG7YmX4c fkhFcCCOiIJaJJ4YiYkojnjbiiC26OKHMMaIYWs0bjjjjRPaqCOFPPb4oFRABvnjkAgKaeSRSCZJ oGhMNrnkk/wxJeWAHlQJIGZY4kfTlu11mZ+Cuyk0lWPIFCYZSjKhmaA6cf0zUkNjLWPVDiYJQQgq QeVSkUci1AQFQX7mcaeeYQ7Kl0Rt+YKoDIKekkqj2XRT0kSSEsPopeyVqKkXnnQKak9+hUrqNyGW mkMBADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0018.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Trees with Multiple Roots
Root1        = ;           Root2
Master
5        = ;      30
       1   1
•A Cartes= ian product of multiple Root1s with = multiple Root2s matching the same Master <= /span>is almost always a mistake in the logic of the SQL or the application.
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0018_image085.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0018_image086.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh1wBYAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAMAAQDO AFIAgAAAAAAAAAL/hI+pGLEPo5y0Wuqu3ny3n3XiSIpfiaaYA6ruS4IhTMfhWed6cuL7P/EBWsAi ikg0Kg8NRXIJXTUNsqhy6sRatzwtVcjNeRng8PLJRJtdZfJ4/VOn23BbkF6H4b/yfOe9sOdnA9g1 VDhYIeg2k1jSl8Xn+Nd4hzipeNnFookZ6dHpmRVKJikqRUkqChmBs3iqOlp5KmtyE5uIO0oL8dqK 5Tupu8vLeDQzDBcswZq7/OtWbKjSnPfMrHWtPEu4qo29KX3ITUhuVs0B9r2VbGnO3n5HTBtvab2e 2Yh/Vu++hv6o0j4j/aS8i1NwRaCEOhjaszLwQpuIOxw+vEJRIreM/zQ45jtIzWO+BxbZgOwocuHJ GoBSpihpwSWflSxbwuzGBaAGnQ3JybT1z+PPneaGgqpDkSfCojdB0Vz6tJafS02JDlIazRnTqDW1 RsWKERvXF0Zt0QSL8V1ZaJjQoeV3MUrVY1P0mYIFcu1Cek9kjIWoSW84vj6qSOskOM1hV29zIp77 iXAPw4tHEoRM1u/foCf1YhYHhdTnz6DDWq5IunSRYEZTq159tqrr10s1bpZHO/e0mDAT60Yd+3Yv 4b+nPk05u7iYrw59K1/OnLji58V1ZUxOHSXXgs6zd3V6zHt16/Wwi9czdp/58ybNumefe9239fBD jhhGvz7d97b161xu90x+/gWUzDICDmgWgKF0h+BLjY2jUIOvPfjKgRKmQiBuF6rWzx4MbmjcLA+C qFwZfpGI4iGySJfihGN82CJSTdTFYoy0GVajjTfCqGOIFvYYBmVAtsjjkFMVAAA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0018_image087.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhRAFsAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAAAAgBC AWgAgQAAAAAAAJnMAAECAwL/hI+py+0ropxUvIuz3rz7D4biSJZMhabmyrbuC8fyl9bqjOf6zvee DbT5hsSi8bgJKoXIpvMJZS2nzKj1is1St7Ws9wveccfdsPmMppHXqLS7x34n2HSg/F6qt936/g0P qOFnFzZoWBGYCHEYBMb4GKGYCDnlRQkp+XbJhbWJmWnmOWYl+glqWcr5lGp6SsqqigTb6uo0Oyp7 y1gLpYtL5PvI2xT861N8OHyEbMzDbKgM/NyMMw0dLWZN5qzdh53THacT7v0dQ04Hjl5n7rLOPvMO 304iPw9jn04vkq8n0y9uXweA/vARXCMwycGC7hZuS/jAoZ8XEh9CnFNxYouM/xYTctwl5WOsdiJp jSg5MhpKkyFWpqzlkiWImFRU0gR58mZNmDqFtey5hSfQa2qG7sxkNNmPpC/xMCWq8OnRQFIHDawa FBBWq1G3LtHqtVyGsFPTkNWI4WzZUGoZRmz7FUaAhnD1Xagb10WAvXNN4L3X4G/eFXz51hNs9wTi wSQKF+a3OOCiyI1MPHZclDK1A5orNTYMAPPVzpsBkF7L4bGBvaFZdz3tWQHsrB1Ur+4LOu1s2gh2 N3Vg+/aB3G99K5FtvLSC4MKHu3aQ/DjG6B2XE3ee4Hpv6oSmc0eIgDn27M85f6/i/TxC8ePJz1Vf GTl8qO3n805vPz8z6Pr779QP7F+AuhQnYIGi3GVggpsgqGCDShHoYISAKSZhhZIBaGGGv8mnYYfx MehhiHvoJmKJFAhiYooWjKWiia+12OGLMFoo44wScmCjhzjmmOGOPNJY448Gjiakg0QWmeBSSCZ5 5JICKunkk1BG2d9MVPpn5ZX6/aSlfZDZwyF1k22V0zRZkvblTXnccs5fBpW0kUzjhDUnR9VsSExS 0kh00WExrXJQnyugpIU8gtLF5xnaHBoPQZKwyag665jjU6RG/GfpfZb2ksqmnirz4KeiUqrpqDMU AAA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0019.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Trees with Multiple Roots: Solutio= ns
Root1        = ;           Root2
Master
5        = ;      30
       1   1
<= span style=3D'font-size:178%'>•Find the missing condition that makes a join 1-1.
        = ;            &n= bsp;      Root2
R2        = ;  Master
        = ;        30
        = ;   1
1   1
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0019_image088.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0019_image089.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh1wBYAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAMAAQDO AFIAgAAAAAAAAAL/hI+pGL0Po5y02ouz3rR5Dobi9o3m+XwlyraZ57hyGAPwjOcGvOp+VLP1fkRO 6VZMHoI7pvIJQTad0NlwWs0qpFOqFuUVhr++azdAlo256bL53Ga9GeP4ib3F20noznxPU9ckBhho oVdoJIj1lxi1SNfnqAg5SDh5KHmBiAnUmNdXiSkaqdkp8ZkSSprIGnmKmqq65Gon+2gKS1tLu6u7 wFv66ysSdAsYLKzLidFzHPccm9sa7ZmQXIUNOm2rfW3snRSujFzt981NnV48fmd+jr68PsKsVB+Y +24/T68P1j4rDyyAqvjluGfiir8fCzMZVNMwEzCCch66o+jJ4sWJ/xqfYMTVEQRCF14ikvyY0Q1K awULrcwYctPLlAH3zAR58iZLjjZ14qro82dLaDEhFuVJlGaaoDDZHbWyhinSNiM5PsVRx6TIq1Bj Vt03Qeo3dRq15pT2RayfRV+z8TPbTG2FI9e6nPoEV2InHpp4/GIrVa7MFX7/AvYoeHCMtlQN5o0l 7wbfYULm+kwcly9Xtw8fT6UMeqJDhptDE7XomY7p1QL1HizNmsyt1JdixxaVGrPtfpmh7rb9LC/t 30RYEdRNHF/vismBh9Q6vLnKF+GQS9+qIaL163z4bN7OnXpRbNHDv/ZuyPxqc/rAq49LCf170++q uZ8vc+tz2PihLFvuPlh/9DGGhWsCUlZYdpXcd6A07QHGX4NFJCifUhIOo40gDF4YVj/zbMjhdNtE GCKCVExWooQKEZjib0Ow2KKLoRQY44WFgVijSjPmyCGKPIZI4Y894ihkcQUAADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0019_image090.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhQAAsAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAQAAAA3 ACgAgAAAAAAAAAKihI+pa8EPozyhzotf3bnnDXoixIHOiFIWYHHp6LJn/GL0Kp/1RMtIv1sAcQ1i MAH0/YzHog75dDaX0aWQWUuqGFoY1sr9erpbjdh2Lpurn7R67WVf5VA6/EKmtu2KvJ7nVrfHQhIo uEfnN9dhsiiiqBLSIOU1JpljGIaCc1mZ4tD4yYcX6vkCGZGp0aQ6pekqChuHKutEW4uJy3ir26qr lFIAADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0019_image091.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhLAAMAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAMAAwAm AAYAgAAAAAAAAAIdhB2nyw3ZgnSUSoiu3rz7+YWil03VqWiMiaIsUAAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0019_image092.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhQQA0AHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAUAAQA7 AC0AgAAAAAAAAAJ5hI+py+0Qwpu0qhit3hfzv3ngOGESiTJmyiZi265w/M5obY9yTu48iPtxgkKL r6g5IivE5UPpfDajqgyVOb26stoDtwv5dqFghHhsLVfT6g67bX7DDeR2XX2+3st7cJ7ahybH5/WH lGF4aHIyV5ioGKi3yDg32AhQAAA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0019_image093.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhFAAnAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAYAAgAO ACEAgAAAAAAAAAI2hI8Qm82dHjQrzCNvhpvbq3TRB4pIBYapWpKa68GOPKdou5qYTtEnz5pUfL1b TDdsEXu5FaAAADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0019_image094.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhSQBWAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAEAAQBD AFEAgAAAAAAAAAL/TICpy+0PoTmx2msnxbxf7YUiA46mWJ7ql66uoxnv3MgTjSP3ndO23JvtgEEX kFdUEZHJEZLZDBEB06jnWbVyplAta0PNeivV7lgC1onPteyaTUqH5fC43FxXiPH5eZveFwbzVrfG V2hICPd2yMbXeEYI6XWoGAmohumo6ReYGTFpZRnaBEmaNGopmsqppXoapNqZFwqbIyvYV4sb25pg 6+P7y3sr/InYAWxETByc3PwCq7zSPH0Cbe2Ebex8Ba20zR0tPkwObj7rip4e9a02Np3tbSK/ZYPi LhWfjxGzzt4jBj1+/bLVy0DwIJl/5TzZY3jJYUGI8AhWPEBxlUWNDBtRCZQISuEqkP0KAAA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0019_image095.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhVwA5AHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAQAAQBO ADQAgAAAAAAAAALohI+pyxsNo5zUhVuz3vZiDoaQRz7ieXpAibacuj6wS0fzN9c6kn+xudvlYjxf 0DUkFoHHlPHw/DWdzGJnCkpCq1su1ha1Xr8UrXhMHoUTa4M5LZW03XPsm+3F5+31s3p/dKfQRweo IzhouESGmJjRKAKJ90gYIjlJqZhVabFxWcbZ6RkqR+r4YsrwKYqqCeYKFpnahQSrOrvaMLuokatr +9u7e9si6atWKwN4jJzcxtzsfAtMScOSSF1tTeJWyBeEw/3ddD0eKG4Ojj41/Aqn9M6eHa9NL1Ru 79yejz3P/7vvHyaB25oUAAA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0019_image096.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh+ABAAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAAAAQD3 ADwAgAAAAJnMAAL/hI+py+0Po5y02juD3rz7gIXiSJbmiSbfyrLpC8fyfLX23dL6zvcRDgzmfMSi 0SRMKjnHpvPJWEqX0Kq1N81SQ7ir96gNK7nhrxkpTict6tX5/WjLg5V5F/6164GS/RZP5CdoAzGY BahjqOgWtaiFCOMoudE4+QhZYjmpoKmGKdIpiRAq91lDqniAOmeasWqo+sra2iALa2CrR6uSK4jb 6/sJPPg7fGtlLAqQrDzDLFv8vNknXQ2ybJ2tbRu97f3t2A0+Tj5bfo6eJp7O3k6I7R4v7wE/bx+/ fq9Pnr/vv93vn8BnsQYatFbwoEJjoxY6zMXroURUnCZatFTpokZiUbU2etQV56NIdT9GmpTi6qRK PhRWuhxS56XMa2RmjkRhU6OznAux8BQI5qc+J0LlISuK7gzSb5iWEtyV0CkpqIWkLqKayWoZrD60 0uPa1CTWAgA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0019_image097.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhKgF9AHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAEAAAAn AXoAgQAAAAAAAJnMALvg4wL/hB+ny+0Po5y02ouz3rzrMASJR5bmiabqyi7gEI7tTNf2jZsvDMv5 DwwKh50dL0ZMKpfMm/HYa0qn1KrkCe35rNyu94fNar/kslknTkfP7LbbEVbzRO+6vRyXz+/8vlSk J0fnR1gIlBcYYrjIOAOYKDbYOEnpgRgoWam5OfEImbXFKTqKcCkYSpqqaSqo6srp+TmH+lpLyKqW abvbFyu7xhvM5yurK3zMhptGi9zcRfxp7DxtpRxJjf1sDSWd7Z20zc0MMP5tjgIdDVd+3l6SnmgM wu5ezxF+5PNCb9+PAY9pxA5+/gpSwDcrn8GFJAD+IsgwYgOHkCBKvKgAITeM/xwtUNRjsSNGjcBE mnzwcdnJlRBSgmIJc+KvkjFrutxTM6eBbSF1LuTp0+bMnkHt4SNa9BxCpEm9aWTaFJuIqVSrWqUa VYmArVy7ehWQVeLXsWTLbg1bz6zatV3RZmMLF65bZHHrxp1ry67eunhT7f17t+8mwIQDC55UODHb w4wUO1bLuNDjyWUj96GMmazlN5k7j93MxrNor6DLjD7NtXQX1KxTq5bSOvbZ10tk26at1bZs3ER0 3+YdxPdv4D+E7yaew/hx5DeUx2be3Hlr6DWkP3cjmq716We2D95+3TR4137G+zZj/ivn9MbFs1e/ +r109/I/M6k//gt+uTj2y8GP7x9gJQS4H4AEThbBgQQaqGCDDlLmxYMSTpgYgxReiCFkXGTIYYf2 behhiB1aKGKJDkZoYoonoqhii/6R4WKM/9EnY43OhWZjjsK1oWOP4WHnY5CYESJkkYUtYmSSdjWm ZJMaMulklKQhJmWVmlQZJSdYJknKlkG64qWOtYQpIy9ktijMmSU2o+aI07R54TdwrnjOnC/6Yyd7 YuXZ3kl8jtbUn4RZJmhlxPFJ3QNYJpqBj4yqsOajxSkoKYvLVVoAADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0020.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Trees with Multiple Roots: Solutio= ns
Root1        = ;           Root2
Master
5        = ;      30
       1   1
•Change the application logic to use two simpler
 = Root2
Master
        = ;        30
        = ;   1
queries that return exactly the same data with fewer rows (35 Vs. 150 per Master).=
Root1        = ;          
Master
5    &n= bsp;
    &nb= sp; 1
+
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0020_image098.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0020_image099.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh1wBYAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAMAAQDO AFIAgAAAAAAAAAL/hI+pGL0Po5y02ouz3rR5Dobi9o3m+XwlyraZ57hyGAPwjOcGvOp+VLP1fkRO 6VZMHoI7pvIJQTad0NlwWs0qpFOqFuUVhr++azdAlo256bL53Ga9GeP4ib3F20noznxPU9ckBhho oVdoJIj1lxi1SNfnqAg5SDh5KHmBiAnUmNdXiSkaqdkp8ZkSSprIGnmKmqq65Gon+2gKS1tLu6u7 wFv66ysSdAsYLKzLidFzHPccm9sa7ZmQXIUNOm2rfW3snRSujFzt981NnV48fmd+jr68PsKsVB+Y +24/T68P1j4rDyyAqvjluGfiir8fCzMZVNMwEzCCch66o+jJ4sWJ/xqfYMTVEQRCF14ikvyY0Q1K awULrcwYctPLlAH3zAR58iZLjjZ14qro82dLaDEhFuVJlGaaoDDZHbWyhinSNiM5PsVRx6TIq1Bj Vt03Qeo3dRq15pT2RayfRV+z8TPbTG2FI9e6nPoEV2InHpp4/GIrVa7MFX7/AvYoeHCMtlQN5o0l 7wbfYULm+kwcly9Xtw8fT6UMeqJDhptDE7XomY7p1QL1HizNmsyt1JdixxaVGrPtfpmh7rb9LC/t 30RYEdRNHF/vismBh9Q6vLnKF+GQS9+qIaL163z4bN7OnXpRbNHDv/ZuyPxqc/rAq49LCf170++q uZ8vc+tz2PihLFvuPlh/9DGGhWsCUlZYdpXcd6A07QHGX4NFJCifUhIOo40gDF4YVj/zbMjhdNtE GCKCVExWooQKEZjib0Ow2KKLoRQY44WFgVijSjPmyCGKPIZI4Y894ihkcQUAADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0020_image100.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhQAAsAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAQAAAA3 ACgAgAAAAAAAAAKihI+pa8EPozyhzotf3bnnDXoixIHOiFIWYHHp6LJn/GL0Kp/1RMtIv1sAcQ1i MAH0/YzHog75dDaX0aWQWUuqGFoY1sr9erpbjdh2Lpurn7R67WVf5VA6/EKmtu2KvJ7nVrfHQhIo uEfnN9dhsiiiqBLSIOU1JpljGIaCc1mZ4tD4yYcX6vkCGZGp0aQ6pekqChuHKutEW4uJy3ir26qr lFIAADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0020_image101.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhhABQAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAQAAQB6 AEkAgAAAAAAAAAL/hI+pyw0RnJy02oslhLn7D2LbFpbmeY0qyrbsaKzuTKccItf6fsCJGuEJZz4F cIg8kRycZfIpcjZIUqh1WsXGbtfuj0tZZr3PokUMJifNtt5YXXtXnGw4UX7m1u2ofQfsxweClyEV KBiVZmJIiDhxOBiEo+g4R9mn2Fg5efmS2bnJKblDqYkI2TJmaofqogoq2HrHsHolS/NWW6Y7Swtr y3s3+jVMdiskFyxcrAaqvBjxTOr8i3sMhSftkRMKq114bUX4nce9OV4NHaqlkb4e9xv+jslMXD/P Uy2PH6K5z//BHzmAeSy5I9gv3T+ENu4ZOchwm8OHEyMmrMjJIrxEYBqFceyYCuJAkG7AYSRp0CRK JRAftFy54OVIkMFmdqz5EuakGB91/gln06K8oASP/DzpU1TAnDClLUSJ1BfTpBKnUjUZ9erFrFqX Wu1qMBrYaUTHuvlqViratBTLst1QAAA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0020_image102.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhQgA5AHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAUAAQA4 ADQAgAAAAAAAAALIhI+poe0P4wmUyYtlpbl7s1XfqIUWiSYhIKauyLGxS8ZzS3vtLJ85dvP1fsDg gkd08JAgZnKyFEKlT1xT6Uwys8Pn9AixaqncJvXlLJurZ/X6x3W/0WdZRjwqy78oPGI/l1d31eF3 YQj1gfiwSKgD6Bg2KDn5JwepsqdZSVnCaZfSCIjZgNhombPVVZSKtPEIxyF7mkm0YnL3WTSLScr4 SltruyLoBVpsHKqbXLrMLPys6BwdSc1qvRtMjYvtOf382n34He2LUQAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0020_image103.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh+QBiAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAAAAQD3 AF8AgQAAAAAAAJnMALvg4wL/hI+py+0Po5y02juF3rx7gYXiSJbmiSbfyrLpC8fyfLX23dL6zvcR DgzmfMSi0SRMKjnHpvPJWEqX0Kq1N81SQ7ir96gNK7nhrxkpTict6tX5/WjLg5V5F/6164GS/RZP 5CdoAzGYBahjqOgWtaiFCOMoudE4+QhZYjmpoKmGKdIpiRAq91lDqniAOmeasWqo+sra2iALa2Cr R6uSK4jb6/sJPPg7fGtlLAqQrDzDLFv8vNknXQ2ybJ2tbRu97f3t2A0+Tj5bfo6eJp7O3k6I7R4v 7wE/bx+/fq9Pnr/vv93vn8BnsQYatFbwoEJjoxY6zMXroURUnCZatFTpokZi/7U2etQV56NIdT9G mpTi6qRKPhRWuhxS56XMa2RmjkRhU6OznAux8BQI5qc+J0LlISuK7gzSb5iWEtyV0CkpqIWkLqKa yWoZrD600uPa1CTYsWTLmj2LNq3atWzbun0LN67cuXTr2r2L10KAvXn7egkwIHAAv4SfAA48gG/h xT0OIxY8mLHkGI4fI448ObOJypYha/4cgnPny6BLUxA92jMDzKY/o05NegHr1pNfw748WzHtzLZv 4zYAePZuxnt9+x4cfDjo4sZTHxauXHLv5tCjE59+u7p14s1Ha9++mHl3weBbi6denjb22OlNn4f9 vb104/HlX89ufzhq3fnNWxbm3597jwEYoICJ1VfgfAQm6B6CdxUAADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0020_image104.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhrgBHAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAEAAACs AEYAgAAAAJnMAAL/hI+py+0Po5wUhFuz3rz7f4XiiH3miaYbybajCscy59Z2Oed6fvf2Dgx6fMSf 8IhsFJfGpPPIjDaf1Jj0Oq1qO9huawumecevsFlCTofObIf6HWjLE3D1/F6339t5/d7c9/YXFig4 qFVoePiUqLiI1Oj4GBQpOclTaXlplam5mdIJ9ykTWjcKU5p3epLatzrU6vqqEVs4W1GbeBuRG7mr 0Fv6Gky8SXyMM4i8HAdaBsvMLBZNbTtRje0Lkc3d+NANrqsUTm7qVo5O9p3OfsXbDl+EFk+fdV6P L4Kbz5/Bj7/iXzsuAsuxKgjOGcJqnBZKI+XwmI6IwShRTAXpYiUqKxoDAeqYZg7ILspG+vhkUt8v C8hWOunlskqsmFtC0Tzj7aZIUTpPyev5pwAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0020_image105.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhPwAyAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAQAAAA2 AC0AgAAAAAAAAAKYhI8Zye0PWZgr2nvpxLwfCmjeGG2GSKZK9ZlqCq7u28UyS2PznXO79OsBcQ6U sBFE2o6IZNFJW2akPejDKLRCqCquz+vRWrAwYlm8NXfRSvWa3XS/yFNmXN623/FxPW/Ml+WiBmYH EgSXo/GTqEhW6Lco45eG1WjYRzkCqanUuRn4eSf6RVpqWsJpuhj6KYl6pUoquwprUQAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0020_image106.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhLwAfAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAQAAQAm ABsAgAAAAAAAAAJohI+JEeoPE2Ox2jmtVhnvnwFY812k2ITlo1LouS6u4aoxPeOHvfKoDAPldkGf iRV8lYw/zhDCbDqT08gTRwVWrliRI0q0eiRZ8jZEAYeR49GGS6ypserOWHw/z+nCMht0A3cDNdhT UgAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0020_image107.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhbgAvAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAEAAgBr ACwAgAAAAJnMAAKthI8JEeoPo5y0ToYztrz7/2jiCJbmuYwqibaus8bsS6PyrdU6iPfbDrz4ho2g UUEkHpeG5JB5dCahQKmTSrNasS2tllvyesEf8ZhsMYvRFLWZLXGr4TC5nH6w63/kvb9X8yd4xTNo KJV2qIgYt+g4FfIo+YQ0aQmYcKkpU7npmVH3+Rkp6glRuhmBqnm6aqnqOgkb6yhEa5h46+ehq1fY u3YCzPgyjFm1wvtYUQAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0021.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Trees with Multiple Roots
Root1        = ;           Root2
Master
0.5        = ;   30
       1   1
•If one of the M multipliers has a value <=3D1,= the Cartesian product is less of a danger, but the query might still be functionally wrong, if = that join is even sometimes “one-to-many.”
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0021_image108.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0021_image109.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh1wBYAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAMAAQDO AFIAgAAAAAAAAAL/hI+pGLEPo5y0Wuqu3ny3n3XiSIpfiaaYA6ruS4IhTMfhWed6cuL7P/EBWsAi ikg0Kg8NRXIJXTUNsqhy6sRatzwtVcjNeRng8PLJRJtdZfJ4/VOn23BbkF6H4b/yfOe9sOdnA9g1 VDhYIeg2k1jSl8Xn+Nd4hzipeNnFookZ6dHpmRVKJikqRUkqChmBs3iqOlp5KmtyE5uIO0oL8dqK 5Tupu8vLeDQzDBcswZq7/OtWbKjSnPfMrHWtPEu4qo29KX3ITUhuVs0B9r2VbGnO3n5HTBtvab2e 2Yh/Vu++hv6o0j4j/aS8i1NwRaCEOhjaszLwQpuIOxw+vEJRIreM/zQ45jtIzWO+BxbZgOwocuHJ GoBSpihpwSWflSxbwuzGBaAGnQ3JybT1z+PPneaGgqpDkSfCojdB0Vz6tJafS02JDlIazRnTqDW1 RsWKERvXF0Zt0QSL8V1ZaJjQoeV3MUrVY1P0mYIFcu1Cek9kjIWoSW84vj6qSOskOM1hV29zIp77 iXAPw4tHEoRM1u/foCf1YhYHhdTnz6DDWq5IunSRYEZTq159tqrr10s1bpZHO/e0mDAT60Yd+3Yv 4b+nPk05u7iYrw59K1/OnLji58V1ZUxOHSXXgs6zd3V6zHt16/Wwi9czdp/58ybNumefe9239fBD jhhGvz7d97b161xu90x+/gWUzDICDmgWgKF0h+BLjY2jUIOvPfjKgRKmQiBuF6rWzx4MbmjcLA+C qFwZfpGI4iGySJfihGN82CJSTdTFYoy0GVajjTfCqGOIFvYYBmVAtsjjkFMVAAA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0021_image110.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhRAFsAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAAAAgBC AWgAgQAAAAAAAJnMAAECAwL/hI+py+0ropxUvIuz3rz7D4biSJZMhabmyrbuC8fyl9bqjOf6zvee DbT5hsSi8bgJKoXIpvMJZS2nzKj1is1St7Ws9wveccfdsPmMppHXqLS7x34n2HSg/F6qt936/g0P qOFnFzZoWBGYCHEYBMb4GKGYCDnlRQkp+XbJhbWJmWnmOWYl+glqWcr5lGp6SsqqigTb6uo0Oyp7 y1gLpYtL5PvI2xT861N8OHyEbMzDbKgM/NyMMw0dLWZN5qzdh53THacT7v0dQ04Hjl5n7rLOPvMO 304iPw9jn04vkq8n0y9uXweA/vARXCMwycGC7hZuS/jAoZ8XEh9CnFNxYouM/xYTctwl5WOsdiJp jSg5MhpKkyFWpqzlkiWImFRU0gR58mZNmDqFtey5hSfQa2qG7sxkNNmPpC/xMCWq8OnRQFIHDawa FBBWq1G3LtHqtVyGsFPTkNWI4WzZUGoZRmz7FUaAhnD1Xagb10WAvXNN4L3X4G/eFXz51hNs9wTi wSQKF+a3OOCiyI1MPHZclDK1A5orNTYMAPPVzpsBkF7L4bGBvaFZdz3tWQHsrB1Ur+4LOu1s2gh2 N3Vg+/aB3G99K5FtvLSC4MKHu3aQ/DjG6B2XE3ee4Hpv6oSmc0eIgDn27M85f6/i/TxC8ePJz1Vf GTl8qO3n805vPz8z6Pr779QP7F+AuhQnYIGi3GVggpsgqGCDShHoYISAKSZhhZIBaGGGv8mnYYfx MehhiHvoJmKJFAhiYooWjKWiia+12OGLMFoo44wScmCjhzjmmOGOPNJY448Gjiakg0QWmeBSSCZ5 5JICKunkk1BG2d9MVPpn5ZX6/aSlfZDZwyF1k22V0zRZkvblTXnccs5fBpW0kUzjhDUnR9VsSExS 0kh00WExrXJQnyugpIU8gtLF5xnaHBoPQZKwyag665jjU6RG/GfpfZb2ksqmnirz4KeiUqrpqDMU AAA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0022.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Trees with Multiple Roots
Root1        = ;           Root2
Master
0.5        = ;   30
       1   1
•Will the application perform correctly in the possibly rare case where it finds multiple Root1s for a particular Master?
•<= /span>These can point to subtle, hard-to-find bugs! = ;
•Watch for the DISTINCT red flag!=
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0022_image111.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0022_image112.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh1wBYAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAMAAQDO AFIAgAAAAAAAAAL/hI+pGLEPo5y0Wuqu3ny3n3XiSIpfiaaYA6ruS4IhTMfhWed6cuL7P/EBWsAi ikg0Kg8NRXIJXTUNsqhy6sRatzwtVcjNeRng8PLJRJtdZfJ4/VOn23BbkF6H4b/yfOe9sOdnA9g1 VDhYIeg2k1jSl8Xn+Nd4hzipeNnFookZ6dHpmRVKJikqRUkqChmBs3iqOlp5KmtyE5uIO0oL8dqK 5Tupu8vLeDQzDBcswZq7/OtWbKjSnPfMrHWtPEu4qo29KX3ITUhuVs0B9r2VbGnO3n5HTBtvab2e 2Yh/Vu++hv6o0j4j/aS8i1NwRaCEOhjaszLwQpuIOxw+vEJRIreM/zQ45jtIzWO+BxbZgOwocuHJ GoBSpihpwSWflSxbwuzGBaAGnQ3JybT1z+PPneaGgqpDkSfCojdB0Vz6tJafS02JDlIazRnTqDW1 RsWKERvXF0Zt0QSL8V1ZaJjQoeV3MUrVY1P0mYIFcu1Cek9kjIWoSW84vj6qSOskOM1hV29zIp77 iXAPw4tHEoRM1u/foCf1YhYHhdTnz6DDWq5IunSRYEZTq159tqrr10s1bpZHO/e0mDAT60Yd+3Yv 4b+nPk05u7iYrw59K1/OnLji58V1ZUxOHSXXgs6zd3V6zHt16/Wwi9czdp/58ybNumefe9239fBD jhhGvz7d97b161xu90x+/gWUzDICDmgWgKF0h+BLjY2jUIOvPfjKgRKmQiBuF6rWzx4MbmjcLA+C qFwZfpGI4iGySJfihGN82CJSTdTFYoy0GVajjTfCqGOIFvYYBmVAtsjjkFMVAAA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0022_image113.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhRAFsAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAAAAgBC AWgAgQAAAAAAAJnMAAECAwL/hI+py+0ropxUvIuz3rz7D4biSJZMhabmyrbuC8fyl9bqjOf6zvee DbT5hsSi8bgJKoXIpvMJZS2nzKj1is1St7Ws9wveccfdsPmMppHXqLS7x34n2HSg/F6qt936/g0P qOFnFzZoWBGYCHEYBMb4GKGYCDnlRQkp+XbJhbWJmWnmOWYl+glqWcr5lGp6SsqqigTb6uo0Oyp7 y1gLpYtL5PvI2xT861N8OHyEbMzDbKgM/NyMMw0dLWZN5qzdh53THacT7v0dQ04Hjl5n7rLOPvMO 304iPw9jn04vkq8n0y9uXweA/vARXCMwycGC7hZuS/jAoZ8XEh9CnFNxYouM/xYTctwl5WOsdiJp jSg5MhpKkyFWpqzlkiWImFRU0gR58mZNmDqFtey5hSfQa2qG7sxkNNmPpC/xMCWq8OnRQFIHDawa FBBWq1G3LtHqtVyGsFPTkNWI4WzZUGoZRmz7FUaAhnD1Xagb10WAvXNN4L3X4G/eFXz51hNs9wTi wSQKF+a3OOCiyI1MPHZclDK1A5orNTYMAPPVzpsBkF7L4bGBvaFZdz3tWQHsrB1Ur+4LOu1s2gh2 N3Vg+/aB3G99K5FtvLSC4MKHu3aQ/DjG6B2XE3ee4Hpv6oSmc0eIgDn27M85f6/i/TxC8ePJz1Vf GTl8qO3n805vPz8z6Pr779QP7F+AuhQnYIGi3GVggpsgqGCDShHoYISAKSZhhZIBaGGGv8mnYYfx MehhiHvoJmKJFAhiYooWjKWiia+12OGLMFoo44wScmCjhzjmmOGOPNJY448Gjiakg0QWmeBSSCZ5 5JICKunkk1BG2d9MVPpn5ZX6/aSlfZDZwyF1k22V0zRZkvblTXnccs5fBpW0kUzjhDUnR9VsSExS 0kh00WExrXJQnyugpIU8gtLF5xnaHBoPQZKwyag665jjU6RG/GfpfZb2ksqmnirz4KeiUqrpqDMU AAA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0023.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Abnormal Join Diagrams
•Trees with joins non-unique in both directions:
T1         T2
2    7
•Another Cartesian product! For an average = non-unique join-key value, here, we get 14 rows, every combination of T1 and T2 having that value. This is similar to the multiple-roots= problem.
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0023_image114.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0023_image115.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhKgADAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAAm AAIAgAAAAAAAAAIHjI+py+3vCgA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0023_image116.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhDQAeAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAQAAQAJ ABkAgAAAAAAAAAIjhA8RaLrsWHqQnlht01tOiSGKN4JceHGfaVqO9pabPMN0xxQAOx== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0023_image117.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhkwBvAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAwCO AGkAgQAAAAAAAJnMAAECAwL/hI+py80inJy02ovzg7x3DYbiqHnm+ZHqylro+7byvMK2Tef6dPf4 DgT6hrCgsUVM/o7MjPJZbEp50Cpqil1Yt9cslgs+eaXhsmkcNKs9aN36nWqz4HSIXFXPR+4hvZ5f 4vcHWCFoSEhleIiopejImOAoucc4aVlpeQmYyXnH+Sn3KTomWkrZZGo6lZrKxMqa9irrJlsrU4vL UZPLexrY2/trtQGshsF1XLxViNyn/JQ4vPvs4xCWQ70UCeaardu43FascA35St4Mid6JwK3ekLkt /W4t2Z5OD29vgJ+vr3ignz9wiwAIHLhuUTiE9QAeZHjP4UKIBAUZnEhR3iGM2BkjPutI7CPIhMpG ahRpkp83Xx1X2mkQYKBLlgcC2Mw382UCmzff5fxWk2fPMTwBDP0ZR6jRmGh69kTKRmjMokSZ8oRq 4qZTplmoXsR6ZqkBr1OOtvpqZujQsjFnoTU2di2TALlUwglKJtjbN2KNiDTElUY2j37mzCRc8AJY xGBxnWzs+DHkc5Inq6poudS/zJdDcmbX8LOmaKIBuihtWjHqwsJW8xXh+jWJ2OUM04ZC6/YQv7q1 Hekd1gvwTa7VfUbYeOThlIzdMg/d+fnpSdKR1Kn+G7e6AgA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0024.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Trees with Joins Non-Uniq= ue in Both Directions
T1         T2
2    7
•This is usually a mistake, with a join condition usually missing or mistakenly counted as a filter condition. Consider first whether one of the filter conditions on T1 or T2 is really a part of the join, else look for th= e missing join condition.
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0024_image118.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0024_image119.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhKQAEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAQAm AAIAgAAAAAAAAAIHjI+py+3vCgA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0024_image120.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhDgAgAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAUAAgAJ ABsAgAAAAAAAAAIlhB95GMtulEttynVxtZD2rUFAFpEj+HwfeookhcKXbLHmKOJQAQA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0024_image121.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhkwBvAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAwCO AGkAgQAAAAAAAJnMAAECAwL/hI+py60ChZu02ouzXrH7uIXiSG7fiUrlyrZWCqfuTK/xLdf6PuF+ zgvufkSY8NgqKoHIZmYJRTmnlaj1RM0+rtyPVtsNe75OsXlMDp7XoLSODYe4XfG6ak6y2/EjvZ6v 4ScIeCFoSEhhqHiHmLC42IjwOBkJMHnZeKkJqNnJSObZ6RYaCkpaSnV6OqXaitQKKwQ7+5lESztz q1trsuvb5xvMm8jVIHyG0fV0bPVSDMxMRHxlG43joPxmLcUQVrY9nJ0mvEVdGev4XJkuKmm+3o15 IA4fDzn/Xm/8iN+s33PPUr5/HAIOJFjwkEB/CPcpWhilITZIByW6e1jRogF+3Rk1cmSokd2hjhLB hSwX7eRFayr7bWsQIIA+cG0WxJxJU46CmDfdyLSX8xPPnmluEoUY9M5Qnj5lMkVKyQyApVN/fnka c9UZo06PUiEaQBXUZz29TukZC05Zs0h44qpzgO2RsLc2xjHwtEkwu3Dl0jjm0o7Vvyz5KrSRc+XD ZUl1Bm68VzHkXyIn60po+TLmzOg2c1418bNYgKJRTSstzxlqfoxXD+rl+g+02GtY0K5N57Y6wrqV HOn9IxVwbl+GO57Tex1qhJZV0mxZWTP00KCnB6JkPTeb7N+KwCsAADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0025.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Trees with Joins Non-Uniq= ue in Both Directions
T1         T2
2&n= bsp; 0.7
•If one of= the M multipliers has a value <=3D1, the Cartesian product is less of a danger, but the query might still be functionally wrong, if that join is even <= span style=3D'font-size:156%'>sometimes “one-to-many.”
•Will the application perform correctly in the possibly rare = case where it finds multiple T2s for a particular Master?
•<= /span>These can point to subtle, hard-to-find bugs! = ;
•<= /span>Watch for the DISTINCT red flag!=
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0025_image122.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0025_image123.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhKQAEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAQAm AAIAgAAAAAAAAAIHjI+py+3vCgA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0025_image124.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhEAAhAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAUAAgAL ABsAgAAAAAAAAAIphG+hiMvnngov0QqivJsz/XkNCIkjNqFmqa6Z+0pWR75pWE/rvWGZXwAAOw== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0025_image125.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhkwBvAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAwCO AGkAgQAAAAAAAJnMAAECAwL/hI+py60ChZu02ouzXrH7uIXiSG7fiUrlyrZWCqfuTK/xLdf6PuF+ zgvufkSY8NgqKoHIZmYJRTmnlaj1RM0+rtyPVtsNe75OsXlMDp7XoLSODYe4XfG6ak6y2/EjvZ6v 4ScIeCFoSEhhqHiHmLC42IjwOBkJMHnZeKkJqNnJSObZ6RYaCkpaSnV6OqXaitQKKwQ7+5lESztz q1trsuvb5xvMm8jVIHyG0fV0bPVSDMxMRHxlG43joPxmLcUQVrY9nJ0mvEVdGev4XJkuKmm+3o15 IA4fDzn/Xm/8iN+s33PPUr5/HAIOJFjwkEB/CPcpWhilITZIByW6e1jRogF+3Rk1cmSokd2hjhLB hSwX7eRFayr7bWsQIIA+cG0WxJxJU46CmDfdyLSX8xPPnmluEoUY9M5Qnj5lMkVKyQyApVN/fnka c9UZo06PUiEaQBXUZz29TukZC05Zs0h44qpzgO2RsLc2xjHwtEkwu3Dl0jjm0o7Vvyz5KrSRc+XD ZUl1Bm68VzHkXyIn60po+TLmzOg2c1418bNYgKJRTSstzxlqfoxXD+rl+g+02GtY0K5N57Y6wrqV HOn9IxVwbl+GO57Tex1qhJZV0mxZWTP00KCnB6JkPTeb7N+KwCsAADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0026.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Trees with Joins Non-Uniq= ue in Both Directions
T1         T2
2&n= bsp; 0.7
•If you can= not make this rigorously unique on one end of = the join, consider whether the more-detailed tabl= e has the correct design - it should probably be correc= ted to have a foreign key pointing to= at most one of the less-detailed table.
•If the jo= in is u= nique, but uniqueness is not enforced by the key index, change the design to enforce it.
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0026_image126.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0026_image127.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhKQAEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAQAm AAIAgAAAAAAAAAIHjI+py+3vCgA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0026_image128.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhDwAgAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAUAAgAK ABsAgAAAAAAAAAIlhA+heMvmXkQqvGTvbBt2fIHS94mjRmYniqpeV1VMPLpcWNpHAQA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0026_image129.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhkwBvAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAwCO AGkAgQAAAAAAAJnMAAECAwL/hI+py60ChZu02ouzXrH7uIXiSG7fiUrlyrZWCqfuTK/xLdf6PuF+ zgvufkSY8NgqKoHIZmYJRTmnlaj1RM0+rtyPVtsNe75OsXlMDp7XoLSODYe4XfG6ak6y2/EjvZ6v 4ScIeCFoSEhhqHiHmLC42IjwOBkJMHnZeKkJqNnJSObZ6RYaCkpaSnV6OqXaitQKKwQ7+5lESztz q1trsuvb5xvMm8jVIHyG0fV0bPVSDMxMRHxlG43joPxmLcUQVrY9nJ0mvEVdGev4XJkuKmm+3o15 IA4fDzn/Xm/8iN+s33PPUr5/HAIOJFjwkEB/CPcpWhilITZIByW6e1jRogF+3Rk1cmSokd2hjhLB hSwX7eRFayr7bWsQIIA+cG0WxJxJU46CmDfdyLSX8xPPnmluEoUY9M5Qnj5lMkVKyQyApVN/fnka c9UZo06PUiEaQBXUZz29TukZC05Zs0h44qpzgO2RsLc2xjHwtEkwu3Dl0jjm0o7Vvyz5KrSRc+XD ZUl1Bm68VzHkXyIn60po+TLmzOg2c1418bNYgKJRTSstzxlqfoxXD+rl+g+02GtY0K5N57Y6wrqV HOn9IxVwbl+GO57Tex1qhJZV0mxZWTP00KCnB6JkPTeb7N+KwCsAADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0027.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
Abnormal Join Diagrams
•Trees with joins uniqu= e in both directions:
T1         T2
1&n= bsp; 0.7
•This is not usually a problem, but it can offer <= /span>opportunities to improve on an already-OK = design, especially early in the development cycle, when design changes are easy.
------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0027_image130.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0027_image131.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhLAAMAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAQAAwAk AAYAgAAAAAAAAAIahBFxy935knKUynmz3rz7DxpXRTKjOZUldBQAOx== ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0027_image132.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhEwAhAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAUAAQAN ABsAgAAAAAAAAAIrhI8QkbnczopyUgWvpe9i32kZNzYhCW6RupYO+6YuAtM1duP3c9p9DPIACgA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0027_image133.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhkwBvAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAwCO AGkAgQAAAAAAAJnMAAECAwL/hI+py80inJy02ovzg7x3DYbiqHnm+ZHqylro+7byvMK2Tef6dPf4 DgT6hrCgsUVM/o7MjPJZbEp50Cpqil1Yt9cslgs+eaXhsmkcNKs9aN36nWqz4HSIXFXPR+4hvZ5f 4vcHWCFoSEhleIiopejImOAoucc4aVlpeQmYyXnH+Sn3KTomWkrZZGo6lZrKxMqa9irrJlsrU4vL UZPLexrY2/trtQGshsF1XLxViNyn/JQ4vPvs4xCWQ70UCeaardu43FascA35St4Mid6JwK3ekLkt /W4t2Z5OD29vgJ+vr3ignz9wiwAIHLhuUTiE9QAeZHjP4UKIBAUZnEhR3iGM2BkjPutI7CPIhMpG ahRpkp83Xx1X2mkQYKBLlgcC2Mw382UCmzff5fxWk2fPMTwBDP0ZR6jRmGh69kTKRmjMokSZ8oRq 4qZTplmoXsR6ZqkBr1OOtvpqZujQsjFnoTU2di2TALlUwglKJtjbN2KNiDTElUY2j37mzCRc8AJY xGBxnWzs+DHkc5Inq6poudS/zJdDcmbX8LOmaKIBuihtWjHqwsJW8xXh+jWJ2OUM04ZC6/YQv7q1 Hekd1gvwTa7VfUbYeOThlIzdMg/d+fnpSdKR1Kn+G7e6AgA7 ------=_NextPart_01C7FEA7.C035BFD0 Content-Location: file:///C:/A3283113/QueryTaxonomies_files/slide0028.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Query Taxonomies
T1         T2
1&n= bsp; 0.7