Re: a couple of quick fragmentation questions
Thanks very much Jpreanaut.
You are right, I did want to avoid the full INIT.
You're second scheme definitely seems worthy of looking into and playing with.
I've got 3 huge tables that've gotten away from me in this way, so I'll be interested in finding the best solution.
Thanks again for your input and help.
========================
-<<Floyd Wellershaus>>-
Database Administrator
Unix Administrator
email: fwellers@yahoo.com
Home: 703-430-0805
Cell: 703-477-6045
========================
http://www.one.org/
----- Original Message ----
From: "jprenaut@yahoo.com" <jprenaut@yahoo.com>
To: informix-list@iiug.org
Sent: Wednesday, April 4, 2007 1:55:54 PM
Subject: Re: a couple of quick fragmentation questions
On Apr 4, 8:00 am, Floyd Wellershaus <fwell...@yahoo.com> wrote:
> I have a table that's fragged by expression, but the last expression has gotten too big. I want to add more dbspaces to it.
> It's our hugest table with over a 1.1 billion rows in it.
> The current schema is :
>
> create table "sentrycf".stsubhst
> (
> scsublog_token integer not null ,
> s_token integer not null ,
> stprofil_token integer,
> stsubdtl_token integer not null ,
> stsubaddr_token integer not null ,
> cert_dt_na date not null ,
> term_beg_dt date not null ,
> term_end_dt date not null ,
> rpt_sublog_token integer not null ,
> rpt_token integer not null ,
> rec_type char(1) not null
> ) with rowids
> fragment by expression
> (stprofil_token <= 6000000 ) in sthst_f01 ,
> ((stprofil_token > 6000000 ) AND (stprofil_token <= 12000000
> ) ) in sthst_f02 ,
> ((stprofil_token > 12000000 ) AND (stprofil_token <= 19000000
> ) ) in sthst_f3 ,
> ((stprofil_token > 19000000 ) AND (stprofil_token <= 26000000
> ) ) in sthst_f4 ,
> ((stprofil_token > 26000000 ) AND (stprofil_token <= 33000000
> ) ) in sthst_f5 ,
> (stprofil_token > 33000000 ) in sthst_f6
> extent size 2097132 next size 2097132 lock mode page;
>
> create unique index "sentrycf".stsubhst_x01 on "sentrycf".stsubhst
> (scsublog_token,s_token) using btree in sthst_x1 ;
> create index "sentrycf".stsubhst_x02 on "sentrycf".stsubhst (stprofil_token)
> using btree ;
> create index "sentrycf".stsubhst_x03 on "sentrycf".stsubhst (stsubdtl_token)
> using btree ;
>
> The last fragment has between stprofil_token 33,000,000 and 60,000,000, so I think it's time to change that.
> What I want to do is this:
>
> alter fragment on table stsubhst
> add ((stprofil_token >33000000) AND (stprofil_token <= 40000000 ))
> in sthst_f7;> add ((stprofil_token >40000000) AND (stprofil_token <= 47000000 ))
> in sthst_f8;
> add ((stprofil_token >47000000) AND (stprofil_token <= 54000000 ))
> in sthst_f9;
> add ((stprofil_token >54000000) AND (stprofil_token <= 61000000 ))
> in sthst_f10;
> add ((stprofil_token >61000000) AND (stprofil_token <= 68000000 ))
> in sthst_f11;
>
> My questions are:
>
> 1) Is this the correct syntax ?
> 2) Does this just move the data from rows where stprofil_token > 33,0000 , or does it revisit all the rows in the table ?
> If the former, then it should be much quicker, because it's only moving half the rows into the new spaces.
> 3) What will happen to indexes 2 and 3, will they adjust accordingly, following the new fragments ?
>
> Thanks.
> Floyd
>
> ========================
> -<<Floyd Wellershaus>>-
> Database Administrator
> Unix Administrator
>
> email: fwell...@yahoo.com
>
> Home: 703-430-0805
>
> Cell: 703-477-6045
> ========================
>
> http://www.one.org/
1) No that syntax will not work. It doesn't appear that the add
portion of the alter fragment syntax allows for multiple adds at a
time. It also doesn't appear that the modify syntax allows for 1
fragment to be modified such that it would be broken in to multiple
fragmens, as you appear to be trying to do. I can think of 2
different options (I thought of a 3rd but it's not possible because
your table is defined with system rowids). The 2 options will act
differently.
Option 1 would be to do an alter fragment on stsubhst init fragment by
expression {put in your tables complete fragment expression list}.
This option isn't particularly appealing as it will completely rebuild
the entire table. As basically you are saying I want to change my
complete fragment expression list, even though what you really want to
do is just change 1 fragment and break it up.
Option 2 I believe would be more to your liking. It's a multi step
proccess:
step 1: alter fragment on stsubhst add remainder in <somedbs> * you
need to create a remainder dbspace to hold rows as you add new
fragment expressions *
step 2: alter fragment on table stsubhst modify sthst_f6 to
((stprofil_token >33000000) AND (stprofil_token <= 40000000 )) in
sthst_f7;
I believe that will scan your current sthst_f6 fragment and move all
the rows where stprofil_token > 40000000 to your remainder fragment
and the rest to the newly created fragment in sthst_f7.
step 3: alter fragment on table stsubhst
add ((stprofil_token >40000000) AND (stprofil_token <= 47000000 )) in
sthst_f8;
This should scan the remainder fragment and move the rows that now fit
it's expression this new fragment
step 4: alter fragment on table stsubhst
add ((stprofil_token >47000000) AND (stprofil_token <= 54000000 )) in
sthst_f9;
step 5: alter fragment on table stsubhst
add ((stprofil_token >54000000) AND (stprofil_token <= 61000000 )) in
sthst_f10;
step 6: alter fragment on table stsubhst
add ((stprofil_token >61000000) AND (stprofil_token <= 68000000 )) in
sthst_f11;
Steps 4,5, and 6 again should only scan the remainder fragments and
move rows from it to the newly added fragment.
then finally you can remove your remainder fragment with
step 7: alter fragment on table stsubhst drop <name of dbspace you
used to hold remainder>
There should be no rows in the remainder unless you have
stprofil_token values > 68000000.
2) the answer to 2 depends on which of the 2 choices you use. Option
1 would create a duplicate table and move all the rows in the table
around even for the fragments that shoudn't be changing. Option 2
should only affect the rows in the fragment you are breaking up. But
because it requires multiple steps, you will need to scan that
remainder fragment multiple times to get the rows out to the new
fragments.
3) yes they should
I'm not sure if other people could come up with something else, but my
3rd idea was going to be to detach the fragment you wanted to split
into multiple pieces, but you are unable to detach fragments when you
have rowid defined on the table.
I'd probably recommend, if possible, playing around with a table with
a similiar schema and fragment expression list, but significantly less
data, to get an idea for sure w