Fragmentation usage
This is a multi-part message in MIME format.
------=_NextPart_000_0007_01C3D9EC.A9EDF780
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Blank
Hi All,
I have a stored procedure which uses a table which has millions of
records & hence fragmented on a particular integer column.
Now my question is,
Create procedure test
Select * from million_rec_table
Where fragmentation_col IN (select col from lookup_table)
End procedure
The above query doesn't use the fragmentation query & hence it uses
a ALL fragmentation scan.
----------------------------------------------------------------------------
-
Suppose if the above query is modified as below
Create procedure test
Select * from million_rec_table
Where fragmentation_col IN (20, 30)
End procedure
This particular procedure uses a fragmentation & scans only the fragment
which has record in 20 & 30...
PLease let me know why this happens.. Is there any way i can give a subquery
& get the benefit of the fragmentation... Also i tried with replacing the
values 20 & 30 in a variable, even this it uses full fragment scan.... Any
help on this is highly appreciated... Thanks in Advance
Regards,
Vijay Kumar.R
------=_NextPart_000_0007_01C3D9EC.A9EDF780
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<TITLE id=3DridTitle>Blank</TITLE>
<STYLE>BODY {
MARGIN-TOP: 25px; FONT-SIZE: 10pt; MARGIN-LEFT: 25px; COLOR: #000000; =
FONT-FAMILY: Arial, Helvetica; BACKGROUND-COLOR: #ffffff
}
P.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; =
FONT-FAMILY: Helvetica, "Times New Roman"
}
LI.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #ffffcc; =
FONT-FAMILY: Helvetica, "Times New Roman"
}
</STYLE>
<META content=3D"MSHTML 6.00.2722.900" name=3DGENERATOR></HEAD>
<BODY id=3DridBody style=3D"BACKGROUND-COLOR: #ffffff" bgColor=3D#ffffff =
background=3D"">
<DIV><FONT face=3DTahoma size=3D2></FONT> </DIV>
<DIV><FONT face=3DTahoma size=3D2><BR><BR></FONT> </DIV>
<DIV><SPAN class=3D390441109-09012004>Hi All,</SPAN></DIV>
<DIV><SPAN class=3D390441109-09012004></SPAN> </DIV>
<DIV><SPAN =
class=3D390441109-09012004> I=20
have a stored procedure which uses a table which has millions of records =
&=20
hence fragmented on a particular integer column.</SPAN></DIV>
<DIV><SPAN =
class=3D390441109-09012004> =20
Now my question is,</SPAN></DIV>
<DIV><SPAN class=3D390441109-09012004></SPAN> </DIV>
<DIV><SPAN =
class=3D390441109-09012004> =20
Create procedure test</SPAN></DIV><DIV><SPAN=20
class=3D390441109-09012004> &nbs=
p; =20
Select * from million_rec_table</SPAN></DIV><DIV><SPAN=20
class=3D390441109-09012004> &nbs=
p; =20
Where fragmentation_col IN (select col from lookup_table)</SPAN></DIV>
<DIV><SPAN =
class=3D390441109-09012004> =20
End procedure</SPAN></DIV>
<DIV><SPAN class=3D390441109-09012004></SPAN> </DIV>
<DIV><SPAN =
class=3D390441109-09012004> =20
The above query doesn't use the fragmentation query & hence it uses =
a ALL=20
fragmentation scan.</SPAN></DIV>
<DIV><SPAN class=3D390441109-09012004></SPAN> </DIV>
<DIV><SPAN=20
class=3D390441109-09012004>----------------------------------------------=
-------------------------------</SPAN></DIV>
<DIV><SPAN class=3D390441109-09012004></SPAN> </DIV>
<DIV><SPAN class=3D390441109-09012004> Suppose if the =
above=20
query is modified as below</SPAN></DIV>
<DIV><SPAN class=3D390441109-09012004>
<DIV><SPAN =
class=3D390441109-09012004> =20
Create procedure test</SPAN></DIV><DIV><SPAN=20
class=3D390441109-09012004> &nbs=
p; =20
Select * from million_rec_table</SPAN></DIV><DIV><SPAN=20
class=3D390441109-09012004> &nbs=
p; =20
Where fragmentation_col IN (20, 30)</SPAN></DIV>
<DIV><SPAN =
class=3D390441109-09012004> =20
End procedure</SPAN></DIV>
<DIV><SPAN class=3D390441109-09012004></SPAN> </DIV>
<DIV><SPAN class=3D390441109-09012004>This particular procedure uses a=20
fragmentation & scans only the fragment which has record in 20 & =
30...</SPAN></DIV>
<DIV><SPAN class=3D390441109-09012004></SPAN> </DIV>
<DIV><SPAN class=3D390441109-09012004>PLease let me know why this =
happens.. Is=20
there any way i can give a subquery & get the benefit of the=20
fragmentation... Also i tried with replacing the values 20 & 30 in a =
variable, even this it uses full fragment scan.... Any help on this is =
highly=20
appreciated... Thanks in Advance</SPAN></DIV></SPAN></DIV>
<DIV><SPAN class=3D390441109-09012004></SPAN> </DIV>
<DIV><FONT face=3D"Comic Sans MS" color=3D#808000>Regards,</FONT></DIV>
<DIV><FONT face=3D"Comic Sans MS" color=3D#808000>Vijay =
Kumar.R</FONT></DIV>
<DIV>
<DIV><FONT face=3D"Comic Sans MS" =
color=3D#808000></FONT></DIV></DIV></BODY></HTML>
------=_NextPart_000_0007_01C3D9EC.A9EDF780--
sending to informix-list