Re: a couple of quick fragmentation questions
Just more tips,
1) use export PSORT_NPROCS=.. and export PSORT_DBTEMP=.. to speed up,
big difference some times.
2) Alter fragment is very very expensive... I had a impression : unload
to and load from could behave better performance some times.
Frank
On 4/4/07, Floyd Wellershaus <fwellers@yahoo.com> wrote:
>
> 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 tab