The "traditional" way to reorganise the table was to create a cluster
index

Any method of reducing the number of extents does requrie you have
adequate contiguous disk space

So unloading a table, dropping it and then not doing anything with the
other tables that are in the dbsapce will mean you do not have
sufficient contiguous space to reload the data

Hi.

  Firstly, thanks for this comprehensive list of reorganization methods, you really included every option.

  I had just reorganized a lot of very fragmented tables (from 80 extents to a hundred and something), and I used either unload-drop-create-load or alter fragment paths. Both worked for me, except that methods which don't recreate original table can't increase first extent size (major suggestion for coming version, in my opinion) and they used to create at least two extents - a tiny one and the important-big one.

  I got same results than you - that is to say, more extents than I expected - when I failed calculating next extent size or when I didn't have enough contiguous space on disk. My favourite method to don't fail with calculation is using oncheck -pt to see actual size of every fragment, convert it to kilobytes, increasing 10% (to support growing, percentage may vary according with grow ratio on your database or table) applying it, make all the stuff and finally set next size to a reasonable value (I mean, if oncheck says my table used 100K, I set next size to 100K just while I was defragmenting, and then restore former value or another acconding with new size and growing expectations)  When I have enough free space (you can verify it with oncheck -pt on the dbspace of choice) It never fail.

  I hope you find this useful. Later

                 Omar
 

Yes, alter fragement init into a new dbspace (then move the other
tables into that dbspace as well).
That way you have contiguous free space to move the table into.

When you use any of the techniques for increasing the next extent size you
need to be sure that you currently have enough contiguous free space to
build the table into.  If you haven't then the table will still be
fragmented.  There are a number of ways to check for contiguous free space.
One is to use oncheck for the dbsapce.  Another is to use sysmaster and to
find the free extents from that.

Regards

Malcolm

-----Original Message-----
From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]
On Behalf Of natebsi@gmail.com
Sent: 20 June 2008 01:07
To: informix-list@iiug.org
Subject: Methods for Reducing Extents

Hi all,

I'm aware this subject has been discussed at nauseum for many years,
but I just want to make sure the info I have is still up to date.

The methods I know about are:

alter table <tablename> modify next 100000; (where 100000 = a number
roughly the size of the table)
alter fragment on table <tablename> init in <same dbspace>;

or:

alter table <tablename> modify next 100000; (where 100000 = a number
roughly the size of the table)
alter index <an index for the table in question> to cluster;

or:

unload to filename
select * from <tablename>;
dbschema -t tablename -d databasename >> tablename.sql (alter .sql
file to increase initial/next extents)
drop table tablename;
recreate table with tablename.sql
load from filename
insert into <tablename>;

or:

dbexport the entire database (if wishing to do many tables)
drop database;
modify the dbexport.sql file to change the initial/next extents for
large tables
dbimport the database

or:

onunload database (or just a table)
drop/recreate the table or entire database
onload the database or table

Does that sound right?

Questions:

1. The onunload method seems to work. That is, I onunload from prod
and insert into the test instance, and almost all tables in test are
in single extents (whereas prod has many extents for the large
tables). This also seems to be the fastest method. However, I read a
post from '98 that said that onunload/method (at least for the entire
database) is not a good idea because:

"Since onunload is taking a binary dump of the table
(i.e. page images), you are really not "defragmenting" it when you
onload it
back in.  That is, if all the pages were not full when you onunloaded,
they will
remain not full when you onload (it will, as mentioned above, probably
result in
a single extent, though). "

Is that still true? Will there still be wasted space in each page,
even the extents are merged into one? And if so, will it be
detrimental to performance?

2. I've tried the other methods with varying degrees of success. After
I modify the extent, and run, say, an alter fragment on it, often
times the number of extents don't change at all or by very little
(like 204 to 171, in one case). I added a 10GB chunk to the dbspace,
just to make sure I had plenty of contiguous space, and still had very
inconsistent results. And thats with initial/next extents about as
large as the whole table. (The table size for these tables were less
then about 3.5GB's each, and I'm using 10.00.FC6 and FC7).
Does anyone know what could be wrong? Am I missing a step or two?

Thanks for ANY advice regarding this, and feel free to correct any
mistakes I've made. I/We have many, many instances to do, and I'd like
to get the methodology down pat!

Nate
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list