Fragmentation usage
This is a multi-part message in MIME format.
------=_NextPart_000_0014_01C3D9F2.8168FC00
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Blank
Hi All,
Please somebody help me on this.......
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_0014_01C3D9F2.8168FC00
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> </DIV>
<DIV><SPAN class=3D390441109-09012004>Hi All,</SPAN></DIV>
<DIV><SPAN class=3D390441109-09012004></SPAN> </DIV>
<DIV><SPAN class=3D390441109-09012004><SPAN=20
class=3D640415910-13012004><STRONG> &n=
bsp;=20
Please somebody help me on this.......</STRONG></SPAN></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_0014_01C3D9F2.8168FC00--
sending to informix-list
Vijay wrote:
> This is a multi-part message in MIME format.
Not content with wasting our time with a 156-line HTML post, you had to do
it twice?
> ------=_NextPart_000_0014_01C3D9F2.8168FC00
> Content-Type: text/plain;
> charset="iso-8859-1"
> Content-Transfer-Encoding: 7bit
>
> Blank
> Hi All,
>
> Please somebody help me on this.......
> 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_0014_01C3D9F2.8168FC00
> 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> </DIV>
> <DIV><SPAN class=3D390441109-09012004>Hi All,</SPAN></DIV>
> <DIV><SPAN class=3D390441109-09012004></SPAN> </DIV>
> <DIV><SPAN class=3D390441109-09012004><SPAN=20
> class=3D640415910-13012004><STRONG> &n=
> bsp;=20
> Please somebody help me on this.......</STRONG></SPAN></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_0014_01C3D9F2.8168FC00--
>
> sending to informix-list
--
"C'est pas parce qu'on n'a rien ''' dire qu'il faut fermer sa gueule"
- Coluche
Obnoxio The Clown <obnoxio@hotmail.com> wrote in message news:<bu0k9o$c9i1b$2@ID-64669.news.uni-berlin.de>... > Vijay wrote: > > > This is a multi-part message in MIME format. > > Not content with wasting our time with a 156-line HTML post, you had to do > it twice? ... and then someone comes along and regurgitates it to us (tch) ;-) Andy
On Tue, 13 Jan 2004 06:00:06 -0500, Vijay wrote:
PLEEEAASSEE do not post HTML/MIME. Doing so triples the bandwidth occupied
by your posting.
> Hi All,
>
> Please somebody help me on this.......
> 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
If the values for the filter on the fragentation column are not know at
optimization time (in this cast that's when the SPL is compiled) then
fragment elimination decisions are deferred to OPEN time, however the engine
still ONLY searches the fragments that it needs to, assuming PDQPRIORITY is
at least '1' at SPL compile time when the query plan for the query is
developed.
> 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
Exactly, because the filter values (20, 30) are know at optimization time.
> 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
Because again the filter values are not known at optimization time (note that
the variables live in the SPL program and are not passed to the optimizer.
They are only provided to the OPEN, EXECUTE, or FOREACH statement at runtime,
EVEN if their values are set using constants in the SPL itself.
> help on this is highly appreciated... Thanks in Advance
You're welcome.
Art S. Kagel
> Regards,
> Vijay Kumar.R
Related threads
- Conversion to differeent characters sets
- Problem in changing locale via dbexport/dbimport
- RE: openlink error "Unable to load locale categories"