RE: a couple of quick fragmentation questions
I have to agree here. The cost of altering the fragmentation strategy will
undoubtedly be much higher than unloading/dropping/recreating/reloading with
HPL.
When you alter a fragment and it requires data movement, that movement goes
through the logs and typically uses 3-6x the amount of space that your
fragment has. So if this is a large fragment (10GB) - count on using 60GB
of logical log space.
No I do not recall if my experience included a table which was unlogged, my
impression is that it did not. This is certainly something I would test.
j.
-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org]On Behalf Of FRANK
Sent: Wednesday, April 04, 2007 4:17 PM
To: Floyd Wellershaus
Cc: informix-list@iiug.org; jprenaut@yahoo.com
Subject: 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;@@N