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