MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_NextPart_01C77DF3.B9156320" 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_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Getting SQL Right the First Try

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_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/master08.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii"
Click to edit Master title style<= /div>
Click to edit Master text styles
Second level
Third level
Fourth level
Fifth level
‹date/time›
‹footer›
‹#›
------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/master08.xml Content-Transfer-Encoding: quoted-printable Content-Type: text/xml; charset="utf-8" ------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/preview.wmf Content-Transfer-Encoding: base64 Content-Type: image/x-wmf AQAJAAADfgQAAAUAOgAAAAAABAAAAAMBCAAFAAAACwIAAAAABQAAAAwC7wLpAwMAAAAeAAcAAAD8 AgAA////AAAABAAAAC0BAAAIAAAA+gIFAAAAAAD///8ABAAAAC0BAQAMAAAAQAkhAPAAAAAAAAAA 7wLpA/////8IAAAA+gIAAAAAAAAAAAAABAAAAC0BAgAEAAAALQEAAAQAAAAnAf//HAAAAPsCyP8A AAAAAACQAQAAAAAAQAAAQXJpYWwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEAAAALQEDAAQA AAAuARgABAAAAAIBAQAFAAAACQIAAAACNwAAADIKLAF2ACAAAABHZXR0aW5nIFNRTCBSaWdodCB0 aGUgRmlyc3QgVHJ5ICwAHwAPAA8ADQAfAB8AEAAkACwAHgAQACgADQAeAB8ADwAQABAAHwAeABAA IQANABMAGwAPAA8AIwATABsADwAEAAAALgEAABwAAAD7AhAABwAAAAAAvAIAAAAAAQICIlN5c3Rl bQAAAAAAABgAAAAcsRMAAQAAAOQEAAAAAAAABAAAAC0BBAAEAAAA8AEDABwAAAD7Asj/AAAAAAAA kAEAAAAAAEAAAEFyaWFsAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABAAAAC0BAwAEAAAALgEY AAQAAAACAQEABQAAAAkCAAAAAiQAAAAyCm4BCgETAAAAKE1vc3Qgb2YgdGhlIFRpbWUhKQATAC8A HwAbABAADwAfAA8AEAAQAB8AHgAPACIADQAuAB4AEAATAAQAAAAuAQAABAAAAC0BBAAEAAAA8AED ABwAAAD7AtT/AAAAAAAAkAEAAAAAAEAAAEFyaWFsAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA BAAAAC0BAwAEAAAALgEYAAQAAAACAQEABQAAAAkCAAAAAhgAAAAyCtkBeQELAAAATWFyY2gsIDIw MDcAJQAYAA8AFgAZAAwADQAYABkAGQAYAAQAAAAuAQAABAAAAC0BBAAEAAAA8AEDABwAAAD7At// AAAAAAAAkAEAAAAAAEAAAEFyaWFsAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABAAAAC0BAwAE AAAALgEYAAQAAAACAQEABQAAAAkCAAAAAjoAAAAyCmQCQwAiAAAAqTIwMDcgRGFuIFRvdywgQWxs IHJpZ2h0cyByZXNlcnZlZBgAEwASABMAEgAJABkAEgASAAkAFgASABgACQAJABcABwAHAAkADAAH ABMAEgAJABEACQAMABEAEgASAAwAEAASABMABAAAAC4BAAAEAAAALQEEAAQAAADwAQMAHAAAAPsC 3/8AAAAAAACQAQAAAAAAQAAAQXJpYWwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEAAAALQED AAQAAAAuARgABAAAAAIBAQAFAAAACQIzM5kCJAAAADIKjAJDABMAAABkYW50b3dAc2luZ2luZ3Nx bC5jABMAEgASAAoAEgAXACIAEgAHABMAEgAIABIAEgASABIABwAJABIABAAAAC4BAAAEAAAALQEE AAQAAADwAQMAHAAAAPsC3/8AAAAAAACQAQAAAAAAQAAAQXJpYWwAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAEAAAALQEDAAQAAAAuARgABAAAAAIBAQAFAAAACQIzM5kCCgAAADIKjAJ/AQIAAABv bRIAGwAEAAAALgEAAAQAAAAtAQQABAAAAPABAwAcAAAA+wLf/wAAAAAAAJABAAAAAABAAABBcmlh bAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAQAAAAtAQMABAAAAC4BGAAEAAAAAgEBAAUAAAAJ AgAAAAIiAAAAMgq0AkMAEgAAAHd3dy5zaW5naW5nc3FsLmNvbRgAGAAYAAkAEQAHABMAEgAIABMA EgARABIABwAJABIAEgAcAAQAAAAuAQAABAAAAC0BBAAEAAAA8AEDABwAAAD7ArX/AAAAAAAAkAEB AAAAAEAAAEFyaWFsAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABAAAAC0BAwAEAAAALgEYAAQA AAACAQEABQAAAAkCwMDAAhIAAAAyCoMAjwAHAAAAU2luZ2luZ3MyABEAKgAqABAAKgAqAAQAAAAu AQAABAAAAC4BGAAEAAAAAgEBAAUAAAAJAgAAAAISAAAAMgqAAIwABwAAAFNpbmdpbmdzMgARACoA KgAQACoAKgAEAAAALgEAAAQAAAAtAQQABAAAAPABAwAcAAAA+wK1/wAAAAAAALwCAAAAAABAAABB cmlhbAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAQAAAAtAQMABAAAAC4BGAAEAAAAAgEBAAUA AAAJAsDAwAIMAAAAMgqDAIoBAwAAAFNRTAAyADoALgAEAAAALgEAAAQAAAAuARgABAAAAAIBAQAF AAAACQL/AAACDAAAADIKgACHAQMAAABTUUwAMgA6AC4ABAAAAC4BAAAEAAAALQEEAAQAAADwAQMA HAAAAPsCtf8AAAAAAACQAQAAAAAAQAAAQXJpYWwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAE AAAALQEDAAQAAAAuARgABAAAAAIBAQAFAAAACQLAwMACEwAAADIKgwA5AggAAABQcmVzZW50czIA GQApACYAKgApABUAJgAEAAAALgEAAAQAAAAuARgABAAAAAIBAQAFAAAACQIAAAACEwAAADIKgAA2 AggAAABQcmVzZW50czIAGQApACYAKgApABUAJgAEAAAALgEAAAQAAAAtAQQABAAAAPABAwAcAAAA +wK1/wAAAAAAAJABAAAAAABAAABBcmlhbAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAQAAAAt AQMABAAAAC4BGAAEAAAAAgEBAAUAAAAJAgAAAAIJAAAAMgqAAF4DAQAAADrmFQAEAAAALgEAAAQA AAAtAQQABAAAAPABAwADAAAAAAA= ------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/master02.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii"
<= /div>
<= /div>
<= /div>
‹header› = ;
<= /div>
<= /div>
<= /div>
‹date/time› = ;
<= /div>
<= /div>
<= /div>
‹footer› = ;
<= /div>
<= /div>
<= /div>
‹#› = ;
------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/pres.xml Content-Transfer-Encoding: quoted-printable Content-Type: text/xml; charset="utf-8" ------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0424.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Getting SQL Right the First Try
Getting SQL Right the First Try (Most of the Time!)
March, 2007
©2007 Dan Tow, All rights reserved
dantow@singing= sql.com =
www.singingsql.com
SingingSQL Presents: <= /span>
------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0392.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Getting SQL Right the First Try
Getti= ng SQL Right the First Try
•Introduction
•Code what you know
•Know what you code
------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0392_image001.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0413.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Getting SQL Right the First Try
= Getting SQL Right the First= Try - Introduction=
•This presentation deals with writing the initial SQL, before it is even tested the fi= rst time.
•If the initial SQL is func= tionally right, SQL tuners can take it as a clean spec for the row= s the application requires at that point in the flow of control, and they don’t need an understanding of the application or even the tables.
------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0413_image002.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0414.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Getting SQL Right the First Try
= Getting SQL Right the First= Try - Introduction=
•To write that clean initia= l SQL, the developer absolutely does need a pre= cise understanding of the application requirements and the tables.
•There is no point in writing initial SQL that is not a clean, correct spec for what the application needs at that stage in its flow of= control!
•Iteration, or trial and error= , is a terrible way to write the initial SQL.
------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0414_image003.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0415.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Getting SQL Right the First Try
= Getting SQL Right the First= Try - Introduction=
•Clean initial SQL, wri= tten by a developer who fully understands the functional requirements, is likely to get a good execution plan from the <= /span>optimizer without manual tuning.
•Even if clean initial SQL does not perform well, as a clean, transparent, correct spec for the <= /span>functional requirements of the application, it is much easier to tu= ne than unclean SQL, and it avoids wasting time tuning something that isn’t even functionally sensible.
=
------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0415_image004.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0393.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Getting SQL Right the First Try
Code what you know
Know what you code <= /b>
First principles:
If you don’t really underst= and what the tables and/or views represent,= the SQL will likely perform poorly,… =
------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0393_image005.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0422.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Getting SQL Right the First Try
Code what you know
Know what you code <= /b>
First principles:
If you don’t really underst= and what the tables and/or views represent,= the SQL will likely perform poorly,… =
and even if it doesn’t, it = will likely be functionally wrong!
------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0422_image006.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0394.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Getting SQL Right the First Try
Code what you know
Know what you c= ode =
First Principles:
If you don’t really underst= and what the SQL is supposed to accomplish, = it will likely perform poorly,…
------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0394_image007.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0416.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Getting SQL Right the First Try
Code what you know
Know what you c= ode =
First Principles:
If you don’t really underst= and what the SQL is supposed to accomplish, = it will likely perform poorly,…
and even if it didn’t, it w= ill likely be functionally wrong!
------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0416_image008.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0417.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Getting SQL Right the First Try
Code what you know
Know what you c= ode =
First Principles: <= /b>
If you don’t really understand what the = SQL is supposed to accomplish, it will likely perform poorly,…
and even if it didn’t, it will likely be functionally wrong…
And even if it isn’t, it will be hard to understand and maintain!
------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0417_image009.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0395.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Getting SQL Right the First Try
Code what you know
•What set of entities does each table represent?
•What is the complete primary = key to each table?
•What set of entities does each view represent?
•What is the virtual primary k= ey of each view?
•Roughly how many rows = in production will there be in each table or view?
------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0395_image010.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0396.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Getting SQL Right the First Try
Know the objects
If you don’t know enough to…
Fully understand the tables and <= /b>views…
You don’t know enough to write the SQL!=
------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0396_image011.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0425.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Getting SQL Right the First Try
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&= #13;
WHERE UPPER(C.Last_Na= me) 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 =       ODT <= span style=3D'font-family:Arial;font-size:83%;color:#333399;mso-color-index:5; mso-special-format:lastCR;display:none'>
OD
A      OT        = ;  C <= /b>
------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0425_image012.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlh/wEEAHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAIAAAD7 AQQAgAAAAAAAAAItjI+py+0Po5y02ouz3rz7D4biSJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh5AC ADs= ------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0425_image013.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_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0425_image014.gif Content-Transfer-Encoding: base64 Content-Type: image/gif R0lGODlhDAA4AHcAMSH+GlNvZnR3YXJlOiBNaWNyb3NvZnQgT2ZmaWNlACH5BAEAAAAALAMAAgAG ADMAgAAAAAAAAAIsBBJmqJfK4FvTtUhvxVbq74WZ2I1miXLqxoLn6qYtCdPz+yB6svO6NOEcDAUA Ow== ------=_NextPart_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0425_image015.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_01C77DF3.B9156320 Content-Location: file:///C:/296B5EB9/HotsosSQLTuning1stTry_files/slide0426.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Getting SQL Right the First Try
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_Na= me) 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 …; = ;
•Each table is a node, represented by its alias. •Each join is a link, with (usually downward-pointing) arrows pointing = toward any side of the join that is unique. •Midpoint arrows point to optional side of any outer join.