<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns="http://www.w3.org/TR/REC-html40"
xmlns:ns0="http://www.exclaimer.co.uk">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]-->
<title>Message</title>
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
p
{mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman";}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:Arial;
color:navy;}
@page Section1
{size:612.0pt 792.0pt;
margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>the index in question is made up of nine
segments, surely all B rows will be returned where there is a 1<sup>st</sup> segment
match with the A row 1<sup>st</sup> segment ?<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>e.g. would not the code below return all B
rows with value “literal” in seg1 <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>access A link seg1 to seg1 of B<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>choose seg1 “literal”<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>rep all<o:p></o:p></span></font></p>
<div>
<p><font size=2 color=navy face=Arial><span style='font-size:10.0pt;font-family:
Arial;color:navy'>Regards, Joe.</span></font><font color=navy><span
style='color:navy'> <o:p></o:p></span></font></p>
</div>
<div>
<div class=MsoNormal align=center style='text-align:center'><font size=3
face="Times New Roman"><span style='font-size:12.0pt'>
<hr size=3 width="100%" align=center tabindex=-1>
</span></font></div>
<p class=MsoNormal><b><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma;font-weight:bold'>From:</span></font></b><font size=2
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'>
powerh-l-bounces+atla38=dsl.pipex.com@lists.sowder.com
[mailto:powerh-l-bounces+atla38=dsl.pipex.com@lists.sowder.com] <b><span
style='font-weight:bold'>On Behalf Of </span></b>Deskin, Bob<br>
<b><span style='font-weight:bold'>Sent:</span></b> 11 August 2005 14:06<br>
<b><span style='font-weight:bold'>To:</span></b> fernando.olmos@hpa.com.au;
powerh-l@lists.sowder.com<br>
<b><span style='font-weight:bold'>Subject:</span></b> RE: Indexed or Seq?</span></font><o:p></o:p></p>
</div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<div>
<div>
<p class=MsoNormal><font size=2 color=blue face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:blue'>There are two issues here. First is how
QUIZ deals with UNIQUE indexes and second the fact that this is relational.</span></font><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> <o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=2 color=blue face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:blue'>When linking to a UNIQUE index, QUIZ
assumes that there is only one record/row to retrieve. So in the statement </span></font><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> <o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=2 color=blue face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:blue'>ACCESS A LINK TO B</span></font><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> <o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=2 color=blue face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:blue'>where B is retrieved via a unique index,
for each A, QUIZ will attempt to retrieve only one B record/row. And of course
it is the first such record/row that you see in your record complex.</span></font><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> <o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=2 color=blue face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:blue'>Now on to relational. While QUIZ knows
that the index is unique from the database schema, it retrieves the data as if
it was an indexed file as opposed to generating a SELECT with a JOIN. As well,
the individual SELECTs are passed to the database and it is the database that
determines how to retrieve the data and which row to return. If you specify
VIAINDEX in the linkage, we'll pass down an ORDERBY so that you get the first
row in the index, however that would only be meaningful with a repeating index
and not in this case (sorry for the brief tangent).</span></font><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> <o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=2 color=blue face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:blue'>It is well known that using cursors is
typically more efficient that straight linkage. In this case I suggest you do a
performance comparison between the ACCESS statement that you have and a DECLARE
CURSOR using the appropriate JOIN.</span></font><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> <o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=2 color=blue face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:blue'>Bob</span></font><o:p></o:p></p>
</div>
<blockquote style='margin-top:5.0pt;margin-right:0cm;margin-bottom:5.0pt'>
<p class=MsoNormal style='margin-bottom:12.0pt'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>-----Original Message-----<br>
<b><span style='font-weight:bold'>From:</span></b>
powerh-l-bounces+bob.deskin=cognos.com@lists.sowder.com
[mailto:powerh-l-bounces+bob.deskin=cognos.com@lists.sowder.com] <b><span
style='font-weight:bold'>On Behalf Of </span></b>fernando.olmos@hpa.com.au<br>
<b><span style='font-weight:bold'>Sent:</span></b> August 10, 2005 7:49 PM<br>
<b><span style='font-weight:bold'>To:</span></b> powerh-l@lists.sowder.com<br>
<b><span style='font-weight:bold'>Subject:</span></b> Indexed or Seq?</span></font><o:p></o:p></p>
<p><font size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'><!-- Converted from text/rtf format -->Hi
everyone,</span></font> <o:p></o:p></p>
<p><font size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>I've
been given the task of speeding up record complexes for a master/detail set of
tables, where the detail table</span></font> <font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>has</span></font> <font size=2
face=Arial><span style='font-size:10.0pt;font-family:Arial'>over 3 million
records. Don't worry, I am not going to ask the obvious, but I was wondering if
someone can help me determine how QUIZ looks</span></font> <font size=2
face=Arial><span style='font-size:10.0pt;font-family:Arial'>up</span></font> <font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>data for
where one the lookup field is the first field in the UNIQUE index set.</span></font><o:p></o:p></p>
<p><font size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>Here
are the QSHOWs for the two tables in question ...</span></font> <o:p></o:p></p>
<p><font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:
"Courier New"'>
Record:
INVOICED_DETAIL</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
of Database: #####</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
Organization: RELATIONAL</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
Null Values Allowed:No</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
Type:
ORACLE</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
Hold Cursor: Unspecified</span></font>
<br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
Open:
#####</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
Record Size: 558 Bytes</span></font> <o:p></o:p></p>
<p><font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:
"Courier New"'>-- Record Contents --</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
Item
Type
Size Occ Offset</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>r
ORDER_NO
INTEGER SIGNED 8
0</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>r
PRIMARY_ID
CHARACTER 2
8</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>r
SECONDARY_ID
CHARACTER
2 10</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>r
PROD_LVL0
CHARACTER 2
12</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>r
PROD_LVL1
CHARACTER 2
14</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>r
PROD_LVL2
CHARACTER 2
16</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>r
PROD_LVL3
CHARACTER 2
18</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>r
PROD_LVL4
CHARACTER 2
20</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>r
PRODUCT_CODE
VARCHAR
16 22</span></font>
<br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>r
EVENT_ID
INTEGER SIGNED
4 38</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>..</span></font>
<br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>etc</span></font>
<br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>..</span></font>
<o:p></o:p></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<p><font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:
"Courier New"'>-- Index Contents --</span></font> <o:p></o:p></p>
<p><font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:
"Courier New"'> ** ORDER_KEY_ID is a UNIQUE index **</span></font>
<o:p></o:p></p>
<p><font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:
"Courier New"'>
Segment
Type
Size Ord Offset</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
ORDER_NO
INTEGER SIGNED 8
A 0</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
PRIMARY_ID
CHARACTER 2
A 8</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
SECONDARY_ID
CHARACTER 2
A 10</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
PROD_LVL0
CHARACTER
2 A 12</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
PROD_LVL1
CHARACTER
2 A 14</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
PROD_LVL2
CHARACTER
2 A 16</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
PROD_LVL3
CHARACTER
2 A 18</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
PROD_LVL4
CHARACTER
2 A 20</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
EVENT_ID
INTEGER SIGNED 4
A 22</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
PRODUCT_SEQ_NO
INTEGER SIGNED 4
A 26</span></font> <o:p></o:p></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<p><font size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>Other
indexes are all REPEATING and do not reference any of the above indexes</span></font>
<o:p></o:p></p>
<p class=MsoNormal style='margin-bottom:12.0pt'><font size=3
face="Times New Roman"><span style='font-size:12.0pt'><o:p> </o:p></span></font></p>
<p><font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:
"Courier New"'>
Record:
INVOICED_MASTER</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
of Database: #####</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
Organization: RELATIONAL</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
Null Values Allowed:No</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
Type:
ORACLE</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
Hold Cursor: Unspecified</span></font>
<br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
Open:
#####</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
Record Size: 591 Bytes</span></font> <o:p></o:p></p>
<p><font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:
"Courier New"'>-- Record Contents --</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
Item
Type
Size Occ Offset</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>r
ORDER_NO
INTEGER SIGNED 8
0</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>..</span></font>
<br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>etc</span></font>
<br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>..</span></font>
<o:p></o:p></p>
<p><font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:
"Courier New"'>-- Index Contents --</span></font> <o:p></o:p></p>
<p><font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:
"Courier New"'> ** ORDER_NO_IM is a UNIQUE index **</span></font>
<o:p></o:p></p>
<p><font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:
"Courier New"'>
Segment
Type
Size Ord Offset</span></font> <br>
<font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:"Courier New"'>
ORDER_NO
INTEGER SIGNED 8
A 0</span></font> <o:p></o:p></p>
<p><font size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>Again,
there are other repeating indexes, but they are not relevant to my query.</span></font>
<o:p></o:p></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<p><font size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>If
I link the two tables using a simple direct link, like the following ...</span></font>
<o:p></o:p></p>
<p><font size=1 face="Courier New"><span style='font-size:7.5pt;font-family:
"Courier New"'>access invoiced_master in hisdb &</span></font> <br>
<font size=1 face="Courier New"><span
style='font-size:7.5pt;font-family:"Courier New"'>link order_no to order_no of
invoiced_detail in hisdb</span></font> <o:p></o:p></p>
<p><font size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>Will
QUIZ use the UNIQUE index defined in INVOICED_DETAIL to look its records
directly, or because the ORDER_NO column is not<b><span style='font-weight:
bold'> explicitly</span></b> defined as a primary key (not unique of course),
will it use sequential searching?</span></font><o:p></o:p></p>
<p><font size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>I
hope I made myself clear here. If not please ask.</span></font> <o:p></o:p></p>
<p><font size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>PS:
this email contains rich text formatting. Please forgive me if it appears in
your mailbox all over the place!</span></font> <o:p></o:p></p>
<p><font size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>Thanks
guys.</span></font> <o:p></o:p></p>
</blockquote>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> <o:p></o:p></span></font></p>
</div>
<div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> </span></font><font size=2
face=Arial><span style='font-size:10.0pt;font-family:Arial'>This message may
contain privileged and/or confidential information. If you have received
this e-mail in error or are not the intended recipient, you may not use, copy,
disseminate or distribute it; do not open any attachments, delete it
immediately from your system and notify the sender promptly by e-mail that you
have done so. Thank you.</span></font> <o:p></o:p></p>
</div>
<div>
<p><font size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'>
</span></font><o:p></o:p></p>
</div>
</div>
</body>
</html>