<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">
<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>Indexed or Seq?</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'>One other question, you don’ say if
there are any additional items in the master which might match further segments
in the index of the detail set, if there are you might have to specify the
segments explicitly as below,<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 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>access invoiced_master in hisdb & <br>
link (order_no) viaindex
ORDER_KEY_ID<font color=navy><span style='color:navy'> </span></font>to order_no
of invoiced_detail in hisdb<font color=navy><span style='color:navy'><o:p></o:p></span></font></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'><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>fernando.olmos@hpa.com.au<br>
<b><span style='font-weight:bold'>Sent:</span></b> 11 August 2005 00:49<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>
</div>
<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'>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>
</div>
</body>
</html>