Removing excessive extents

I have two tables that I want to reorganize to reduce the number of extents that they have. I am clear on how to do this by copying the data in the table, dropping the table and then recreating it with a larger extent size and then putting the data back. My question is what happens to tables that have a foreign key based on this table when I drop it? Robert Phillips (rphillips@ce-a.com) Systems Analyst Chamberlin Edmonds and Associates (www.ce-a.com) 404-634-5196 x1261

Could you give me an overview of how this works. The only documentation I have on fragments is regarding creating fragments in different dbspaces, and deleteing them from different dbspaces. Also I did a dbschema on one of the tables that have too many extents and it didn't give me the size of the extent... { TABLE "informix".hospitals row size = 854 number of columns = 34 index size = 12 } create table "informix".hospitals ( hospital_id serial not null , hospital_name char(20), hospital_full_name char(50), street char(62), street2 char(62), city char(30), state char(2), zip char(10), phone char(14), fax char(14), mstreet char(62), mstreet2 char(62), mcity char(30), mstate char(2), mzip char(10), use_mail_address char(1), region_id integer, district_id integer, provider_number char(50), contract_start date, contract_end date, ob char(1), comments char(255), in_percent decimal(7,4), out_percent decimal(7,4), profee_in_percent decimal(7,4), profee_out_percent decimal(7,4), last_modified date, iscurrent char(1), modified_by char(3), flat_rate money(16,2), ptreecode char(5), terms integer, email varchar(50), unique (hospital_id) ); revoke all on "informix".hospitals from "public"; -----Original Message----- From: rkusenet [mailto:rkusenet@sympatico.ca] Sent: Wednesday, June 04, 2003 12:25 PM To: ids@iiug.org; RPhillips@ce-a.com Subject: Re: Removing excessive extents [1273] ----- Original Message ----- From: <RPhillips@ce-a.com> To: <ids@iiug.org> Sent: Wednesday, June 04, 2003 11:48 Subject: Removing excessive extents [1273] > I have two tables that I want to reorganize to reduce the number of extents > that they have. I am clear on how to do this by copying the data in the > table, dropping the table and then recreating it with a larger extent size > and then putting the data back. > > My question is what happens to tables that have a foreign key based on this > table when I drop it? Informix will prevent you from dropping. If reducing the number of extent is your goal, then use alter fragment

Another easier way can be to create a cluster index on hospital table and then drop that cluster index. when u do that, it will relocate data from its present position, which would automatically free extents. ----- Original Message ----- From: <RPhillips@ce-a.com> To: <ids@iiug.org> Sent: Wednesday, June 04, 2003 12:41 Subject: RE: Removing excessive extents [1275] > Could you give me an overview of how this works. The only documentation I > have on fragments is regarding creating fragments in different dbspaces, and > deleteing them from different dbspaces. > > Also I did a dbschema on one of the tables that have too many extents and it > didn't give me the size of the extent... > > { TABLE "informix".hospitals row size = 854 number of columns = 34 index > size = 12 > } > create table "informix".hospitals > ( > hospital_id serial not null , > hospital_name char(20), > hospital_full_name char(50), > street char(62), > street2 char(62), > city char(30), > state char(2), > zip char(10), > phone char(14), > fax char(14), > mstreet char(62), > mstreet2 char(62), > mcity char(30), > mstate char(2), > mzip char(10), > use_mail_address char(1), > region_id integer, > district_id integer, > provider_number char(50), > contract_start date, > contract_end date, > ob char(1), > comments char(255), > in_percent decimal(7,4), > out_percent decimal(7,4), > profee_in_percent decimal(7,4), > profee_out_percent decimal(7,4), > last_modified date, > iscurrent char(1), > modified_by char(3), > flat_rate money(16,2), > ptreecode char(5), > terms integer, > email varchar(50), > unique (hospital_id) > ); > revoke all on "informix".hospitals from "public";> > -----Original Message----- > From: rkusenet [mailto:rkusenet@sympatico.ca] > Sent: Wednesday, June 04, 2003 12:25 PM > To: ids@iiug.org; RPhillips@ce-a.com > Subject: Re: Removing excessive extents [1273] > > > > ----- Original Message ----- > From: <RPhillips@ce-a.com> > To: <ids@iiug.org> > Sent: Wednesday, June 04, 2003 11:48 > Subject: Removing excessive extents [1273] > > > > I have two tables that I want to reorganize to reduce the number of > extents > > that they have. I am clear on how to do this by copying the data in the > > table, dropping the table and then recreating it with a larger extent size > > and then putting the data back. > > > > My question is what happens to tables that have a foreign key based on > this > > table when I drop it? > > Informix will prevent you from dropping. > > If reducing the number of extent is your goal, then use alter fragment > >

You mean like this (http://www.iiug.org/forums/ids/index.cgi?read=377) Which I'm not sure why it is supposed to work. "I have used the following technique on some of my tables when I could not get enough down time to do a "proper" resizing (unload, change schema, drop table, rebuild, load). alter the tables next extent size so that it is large enough to hold the whole table. Then alter one of the indexes to cluster and then drop the cluster. it would look like this alter table table_x modify next size 123456; alter index table_x_index to cluster; alter index table_x_index to not cluster;" -----Original Message----- From: rkusenet [mailto:rkusenet@sympatico.ca] Sent: Wednesday, June 04, 2003 1:12 PM To: RPhillips@ce-a.com Cc: ids@iiug.org Subject: Re: Removing excessive extents [1275] Another easier way can be to create a cluster index on hospital table and then drop that cluster index. when u do that, it will relocate data from its present position, which would automatically free extents. ----- Original Message ----- From: <RPhillips@ce-a.com> To: <ids@iiug.org> Sent: Wednesday, June 04, 2003 12:41 Subject: RE: Removing excessive extents [1275] > Could you give me an overview of how this works. The only documentation I > have on fragments is regarding creating fragments in different dbspaces, and > deleteing them from different dbspaces. > > Also I did a dbschema on one of the tables that have too many extents and it > didn't give me the size of the extent... > > { TABLE "informix".hospitals row size = 854 number of columns = 34 index > size = 12 > } > create table "informix".hospitals > ( > hospital_id serial not null , > hospital_name char(20), > hospital_full_name char(50), > street char(62), > street2 char(62), > city char(30), > state char(2), > zip char(10), > phone char(14), > fax char(14), > mstreet char(62), > mstreet2 char(62), > mcity char(30), > mstate char(2), > mzip char(10), > use_mail_address char(1), > region_id integer, > district_id integer, > provider_number char(50), > contract_start date, > contract_end date, > ob char(1), > comments char(255), > in_percent decimal(7,4), > out_percent decimal(7,4), > profee_in_percent decimal(7,4), > profee_out_percent decimal(7,4), > last_modified date, > iscurrent char(1), > modified_by char(3), > flat_rate money(16,2), > ptreecode char(5), > terms integer, > email varchar(50), > unique (hospital_id) > ); > revoke all on "informix".hospitals from "public";> > -----Original Message----- > From: rkusenet [mailto:rkusenet@sympatico.ca] > Sent: Wednesday, June 04, 2003 12:25 PM > To: ids@iiug.org; RPhillips@ce-a.com > Subject: Re: Removing excessive extents [1273] > > > > ----- Original Message ----- > From: <RPhillips@ce-a.com> > To: <ids@iiug.org> > Sent: Wednesday, June 04, 2003 11:48 > Subject: Removing excessive extents [1273] > > > > I have two tables that I want to reorganize to reduce the number of > extents > > that they have. I am clear on how to do this by copying the data in the > > table, dropping the table and then recreating it with a larger extent size > > and then putting the data back. > > > > My question is what happens to tables that have a foreign key based on > this > > table when I drop it? > > Informix will prevent you from dropping. > > If reducing the number of extent is your goal, then use alter fragment > >

----- Original Message ----- From: RPhillips@ce-a.com At: 6/ 4 13:50 > Could you give me an overview of how this works. The only documentation I > have on fragments is regarding creating fragments in different dbspaces, and > deleteing them from different dbspaces. Check the doc for ALTER FRAGMENT ON TABLE <tablename> INIT IN <dbspacename>; According to the Guide to SQL Syntax following the INIT IN clause one can include a fragmentation expression OR a single dbspace. It turns out this can be the same dbspace in which the table already resides and the engine will simply reorg the table into a new extent(s) in the named dbspace. I do it all the time and it is the fastest way to reorg if you have enough logical log space to record all of the page mods. > Also I did a dbschema on one of the tables that have too many extents and it > didn't give me the size of the extent... Dbschema only outputs the extent options if you include '-ss' option at the end of the commandline. BTW, my dbschema replacement utility, myschema, can optionally (-a -r) output for you the required ALTER TABLE and ALTER FRAGMENT commands to reorg a table (or tables) automatically. Myschema will optionally (-n <adj%>) taking growth criteria into consideration. Myschema is included in the package utils2_ak available for download from the IIUG Software Repository and implements all of dbschema's functionality (except -hd) and much much more. Art S. Kagel > > { TABLE "informix".hospitals row size = 854 number of columns = 34 index > size = 12 > } > create table "informix".hospitals > ( > hospital_id serial not null , > hospital_name char(20), > hospital_full_name char(50), > street char(62), > street2 char(62), > city char(30), > state char(2), > zip char(10), > phone char(14), > fax char(14), > mstreet char(62), > mstreet2 char(62), > mcity char(30), > mstate char(2), > mzip char(10), > use_mail_address char(1), > region_id integer, > district_id integer, > provider_number char(50), > contract_start date, > contract_end date, > ob char(1), > comments char(255), > in_percent decimal(7,4), > out_percent decimal(7,4), > profee_in_percent decimal(7,4), > profee_out_percent decimal(7,4), > last_modified date, > iscurrent char(1), > modified_by char(3), > flat_rate money(16,2), > ptreecode char(5), > terms integer, > email varchar(50), > unique (hospital_id) > ); > revoke all on "informix".hospitals from "public";> > -----Original Message----- > From: rkusenet [mailto:rkusenet@sympatico.ca] > Sent: Wednesday, June 04, 2003 12:25 PM > To: ids@iiug.org; RPhillips@ce-a.com > Subject: Re: Removing excessive extents [1273] > > > > ----- Original Message ----- > From: <RPhillips@ce-a.com> > To: <ids@iiug.org> > Sent: Wednesday, June 04, 2003 11:48 > Subject: Removing excessive extents [1273] > > > > I have two tables that I want to reorganize to reduce the number of > extents > > that they have. I am clear on how to do this by copying the data in the > > table, dropping the table and then recreating it with a larger extent size > > and then putting the data back. > > > > My question is what happens to tables that have a foreign key based on > this > > table when I drop it? > > Informix will prevent you from dropping. > > If reducing the number of extent is your goal, then use alter fragment

Yes, but doesn't the alter index...to cluster statement put an exclusive lock on the table? Seems that you still need downtime to do this (unless you can operate with this particular table unavailable for a while which doesn't apply at my company.) --John Bejarano Shutterfly. --- RPhillips@ce-a.com wrote: > Date: Wed, 4 Jun 2003 13:31:19 -0400 (EDT) > To: ids@iiug.org > From: RPhillips@ce-a.com > Subject: RE: Removing excessive extents [1277] > > You mean like this > (http://www.iiug.org/forums/ids/index.cgi?read=377) > Which > I'm not sure why it is supposed to work. > > "I have used the following technique on some of my > tables when I could not > get enough down time to do a "proper" resizing > (unload, change schema, drop > table, rebuild, load). > > > alter the tables next extent size so that it is > large enough to hold the > whole table. Then alter one of the indexes to > cluster and then drop the > cluster. > > > it would look like this > > alter table table_x modify next size 123456; > alter index table_x_index to cluster; > alter index table_x_index to not cluster;"> > -----Original Message----- > From: rkusenet [mailto:rkusenet@sympatico.ca] > Sent: Wednesday, June 04, 2003 1:12 PM > To: RPhillips@ce-a.com > Cc: ids@iiug.org > Subject: Re: Removing excessive extents [1275] > > > Another easier way can be to create a cluster index > on hospital table > and then drop that cluster index. > when u do that, it will relocate data from its > present position, > which would automatically free extents. > > ----- Original Message ----- > From: <RPhillips@ce-a.com> > To: <ids@iiug.org> > Sent: Wednesday, June 04, 2003 12:41 > Subject: RE: Removing excessive extents [1275] > > > > Could you give me an overview of how this works. > The only documentation I > > have on fragments is regarding creating fragments > in different dbspaces, > and > > deleteing them from different dbspaces. > > > > Also I did a dbschema on one of the tables that > have too many extents and > it > > didn't give me the size of the extent... > > > > { TABLE "informix".hospitals row size = 854 number > of columns = 34 index > > size = 12 > > } > > create table "informix".hospitals > > ( > > hospital_id serial not null , > > hospital_name char(20), > > hospital_full_name char(50), > > street char(62), > > street2 char(62), > > city char(30), > > state char(2), > > zip char(10), > > phone char(14), > > fax char(14), > > mstreet char(62), > > mstreet2 char(62), > > mcity char(30), > > mstate char(2), > > mzip char(10), > > use_mail_address char(1), > > region_id integer, > > district_id integer, > > provider_number char(50), > > contract_start date, > > contract_end date, > > ob char(1), > > comments char(255), > > in_percent decimal(7,4), > > out_percent decimal(7,4), > > profee_in_percent decimal(7,4), > > profee_out_percent decimal(7,4), > > last_modified date, > > iscurrent char(1), > > modified_by char(3), > > flat_rate money(16,2), > > ptreecode char(5), > > terms integer, > > email varchar(50), > > unique (hospital_id) > > ); > > revoke all on "informix".hospitals from "public";> > > > -----Original Message----- > > From: rkusenet [mailto:rkusenet@sympatico.ca] > > Sent: Wednesday, June 04, 2003 12:25 PM > > To: ids@iiug.org; RPhillips@ce-a.com > > Subject: Re: Removing excessive extents [1273] > > > > > > > > ----- Original Message ----- > > From: <RPhillips@ce-a.com> > > To: <ids@iiug.org> > > Sent: Wednesday, June 04, 2003 11:48 > > Subject: Removing excessive extents [1273] > > > > > > > I have two tables that I want to reorganize to > reduce the number of > > extents > > > that they have. I am clear on how to do this by > copying the data in the > > > table, dropping the table and then recreating it > with a larger extent > size > > > and then putting the data back. > > > > > > My question is what happens to tables that have > a foreign key based on > > this > > > table when I drop it? > > > > Informix will prevent you from dropping. > > > > If reducing the number of extent is your goal, > then use alter fragment > > > > >

You will need to add the -ss option for dbschema to see FIRST EXTENT and NEXT EXTENT. -----Original Message----- From: RPhillips@ce-a.com [mailto:RPhillips@ce-a.com] Sent: Wednesday, June 04, 2003 12:42 PM To: ids@iiug.org Subject: RE: Removing excessive extents [1275] Could you give me an overview of how this works. The only documentation I have on fragments is regarding creating fragments in different dbspaces, and deleteing them from different dbspaces. Also I did a dbschema on one of the tables that have too many extents and it didn't give me the size of the extent... { TABLE "informix".hospitals row size = 854 number of columns = 34 index size = 12 } create table "informix".hospitals ( hospital_id serial not null , hospital_name char(20), hospital_full_name char(50), street char(62), street2 char(62), city char(30), state char(2), zip char(10), phone char(14), fax char(14), mstreet char(62), mstreet2 char(62), mcity char(30), mstate char(2), mzip char(10), use_mail_address char(1), region_id integer, district_id integer, provider_number char(50), contract_start date, contract_end date, ob char(1), comments char(255), in_percent decimal(7,4), out_percent decimal(7,4), profee_in_percent decimal(7,4), profee_out_percent decimal(7,4), last_modified date, iscurrent char(1), modified_by char(3), flat_rate money(16,2), ptreecode char(5), terms integer, email varchar(50), unique (hospital_id) ); revoke all on "informix".hospitals from "public"; -----Original Message----- From: rkusenet [mailto:rkusenet@sympatico.ca] Sent: Wednesday, June 04, 2003 12:25 PM To: ids@iiug.org; RPhillips@ce-a.com Subject: Re: Removing excessive extents [1273] ----- Original Message ----- From: <RPhillips@ce-a.com> To: <ids@iiug.org> Sent: Wednesday, June 04, 2003 11:48 Subject: Removing excessive extents [1273] > I have two tables that I want to reorganize to reduce the number of extents > that they have. I am clear on how to do this by copying the data in > the table, dropping the table and then recreating it with a larger > extent size and then putting the data back. > > My question is what happens to tables that have a foreign key based on this > table when I drop it? Informix will prevent you from dropping. If reducing the number of extent is your goal, then use alter fragment "CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel Retail. This email message and all attachments may contain legally privileged and confidential information intended solely for the use of the addressee. If you are not the intended recipient, you should immediately stop reading this message and delete it from the system. Any unauthorized reading, distribution, copying, or other use of this message or its attachments is strictly prohibited. All personal messages express solely the sender's views and not those of WHSmith USA Travel Retail. This message may not be copied or distributed without this disclaimer."

There is no way to reorg a table with zero downtime. If you need to reorg a table that is needed live or with minimal downtime the only option is: 1. Create a new table with the same structure and desired extent sizing & location 2. Copy all rows from the original table to the new one, this is the time consumer and the last thing you can do without locking out users 3. Lock the original table in exclusive mode 4. Drop all constraints and indexes on the original table (will also destroy any referencing foreign keys on other tables) 5. Rename the original table to a new name 6. Unlock the original table, the lock's not needed anymore 7. Rename the new table to the original name 8. Lock the table 9. Recreate the indexes on the table 10. Recreate constraints on the table 11. Recreate referencing foreign keys pointing to the table 12. If the original table was active during the copy you may have to recover missing or updated data at this point. (If this step is difficult given your table structure and/or the nature of the transactions you may need to add audit triggers to the table before starting so you can track which rows to delete, copy, and/or update afterward.) Art S. Kagel ----- Original Message ----- From: John Bejarano <jbejaran@yahoo.com> At: 6/ 4 15:35 Yes, but doesn't the alter index...to cluster statement put an exclusive lock on the table? Seems that you still need downtime to do this (unless you can operate with this particular table unavailable for a while which doesn't apply at my company.) --John Bejarano Shutterfly. --- RPhillips@ce-a.com wrote: > Date: Wed, 4 Jun 2003 13:31:19 -0400 (EDT) > To: ids@iiug.org > From: RPhillips@ce-a.com > Subject: RE: Removing excessive extents [1277] > > You mean like this > (http://www.iiug.org/forums/ids/index.cgi?read=377) > Which > I'm not sure why it is supposed to work. > > "I have used the following technique on some of my > tables when I could not > get enough down time to do a "proper" resizing > (unload, change schema, drop > table, rebuild, load). > > > alter the tables next extent size so that it is > large enough to hold the > whole table. Then alter one of the indexes to > cluster and then drop the > cluster. > > > it would look like this > > alter table table_x modify next size 123456; > alter index table_x_index to cluster; > alter index table_x_index to not cluster;"> > -----Original Message----- > From: rkusenet [mailto:rkusenet@sympatico.ca] > Sent: Wednesday, June 04, 2003 1:12 PM > To: RPhillips@ce-a.com > Cc: ids@iiug.org > Subject: Re: Removing excessive extents [1275] > > > Another easier way can be to create a cluster index > on hospital table > and then drop that cluster index. > when u do that, it will relocate data from its > present position, > which would automatically free extents. > > ----- Original Message ----- > From: <RPhillips@ce-a.com> > To: <ids@iiug.org> > Sent: Wednesday, June 04, 2003 12:41 > Subject: RE: Removing excessive extents [1275] > > > > Could you give me an overview of how this works. > The only documentation I > > have on fragments is regarding creating fragments > in different dbspaces, > and > > deleteing them from different dbspaces. > > > > Also I did a dbschema on one of the tables that > have too many extents and > it > > didn't give me the size of the extent... > > > > { TABLE "informix".hospitals row size = 854 number > of columns = 34 index > > size = 12 > > } > > create table "informix".hospitals > > ( > > hospital_id serial not null , > > hospital_name char(20), > > hospital_full_name char(50), > > street char(62), > > street2 char(62), > > city char(30), > > state char(2), > > zip char(10), > > phone char(14), > > fax char(14), > > mstreet char(62), > > mstreet2 char(62), > > mcity char(30), > > mstate char(2), > > mzip char(10), > > use_mail_address char(1), > > region_id integer, > > district_id integer, > > provider_number char(50), > > contract_start date, > > contract_end date, > > ob char(1), > > comments char(255), > > in_percent decimal(7,4), > > out_percent decimal(7,4), > > profee_in_percent decimal(7,4), > > profee_out_percent decimal(7,4), > > last_modified date, > > iscurrent char(1), > > modified_by char(3), > > flat_rate money(16,2), > > ptreecode char(5), > > terms integer, > > email varchar(50), > > unique (hospital_id) > > ); > > revoke all on "informix".hospitals from "public";> > > > -----Original Message----- > > From: rkusenet [mailto:rkusenet@sympatico.ca] > > Sent: Wednesday, June 04, 2003 12:25 PM > > To: ids@iiug.org; RPhillips@ce-a.com > > Subject: Re: Removing excessive extents [1273] > > > > > > > > ----- Original Message ----- > > From: <RPhillips@ce-a.com> > > To: <ids@iiug.org> > > Sent: Wednesday, June 04, 2003 11:48 > > Subject: Removing excessive extents [1273] > > > > > > > I have two tables that I want to reorganize to > reduce the number of > > extents > > > that they have. I am clear on how to do this by > copying the data in the > > > table, dropping the table and then recreating it > with a larger extent > size > > > and then putting the data back. > > > > > > My question is what happens to tables that have > a foreign key based on > > this > > > table when I drop it? > > > > Informix will prevent you from dropping. > > > > If reducing the number of extent is your goal, > then use alter fragment > > > > >

The foreign keys will be dropped and you will have to reinstitute them. Alternatively, if you have the logical log space and disk space, you can do the reorg in place using ALTER FRAGMENT ... INIT IN ...; into the same dbspace or fragmentation expression the table currently lives in (or another) after altering the NEXT SIZE for the table. This will not change the initial extent size as recorded in the table's partition page but often the next few extents will be concatenated to that initial extent anyway. I rarely end up with more than 2 or 3 extents after such a reorg. Also lock the table in exclusive mode before the ALTER FRAGMENT and you will not use bjillions of locks during the reorg. This tends to be considerably faster then unloading, dropping, creating, reloading. Art S. Kagel ----- Original Message ----- From: RPhillips@ce-a.com At: 6/ 4 12:57 > I have two tables that I want to reorganize to reduce the number of extents > that they have. I am clear on how to do this by copying the data in the > table, dropping the table and then recreating it with a larger extent size > and then putting the data back. > > My question is what happens to tables that have a foreign key based on this > table when I drop it? > > Robert Phillips (rphillips@ce-a.com) > Systems Analyst > Chamberlin Edmonds and Associates (www.ce-a.com) > 404-634-5196 x1261

At 4.6.2003 20:19, ART KAGEL, .... wrote: >----- Original Message ----- >From: RPhillips@ce-a.com >At: 6/ 4 13:50 > >> Could you give me an overview of how this works. The only documentation I >> have on fragments is regarding creating fragments in different dbspaces, and >> deleteing them from different dbspaces. > >Check the doc for ALTER FRAGMENT ON TABLE <tablename> INIT IN <dbspacename>; > >According to the Guide to SQL Syntax following the INIT IN clause one can >include a fragmentation expression OR a single dbspace. It turns out this can >be the same dbspace in which the table already resides and the engine will >simply reorg the table into a new extent(s) in the named dbspace. I do it all >the time and it is the fastest way to reorg if you have enough logical log space > to record all of the page mods. I use this procedure to reorganize the tables on a regular basis and its working like a champ :-) Johann ****************************************************** Tele Atlas Deutschland GmbH Dept: TA/FIT-DEU Am Neuen Horizont 1 D-31177 Harsum Tel: +49 5127 408-465 Fax: +49 5127 408-559 E-mail: Johann.Eggers@teleatlas.com ******************************************************

A couple of comments around this discussion... With respect to "setting the index to non-cluster"...a clustered index physically doesn't look any different than a non-clustered index. And since we don't maintain the clustered/physical order of the data rows with a cluster index (something many find surprising...Sybase does this btw), it doesn't matter if you leave the index clustered or not. No overhead. It's simply a definition in sysindexes via a couple of columns. If you decide to use a clustered index, and set the NEXT SIZE to be "large enough for the whole table" (as mentioned below), keep in mind that doesn't guarantee at all that the whole table will fall in a single extent of the NEXT SIZE. When you alter the index to cluster (or initially create the clustered index), we do a copy of the data rows into a new partition or table (partnums will change, btw....if you have any scripts looking for a hard-coded partnum, beware), and we go through the same algorithm for finding extent space for the new table (of the same name after it's done though). We interrogate the chunk free list of each chunk in the dbspace, and grab the NEXT SIZE if we can. Simply put, we grab the largest contiguous set of pages available within a given chunk, but never less than 4 pages. (Not to be confused with the default of 8 pages for most ports). IF that extent is in fact the NEXT SIZE requested, and the table will fit there, you'll have 1 extent. If not, the process repeats, and you could easily end up with many extents. If you're relocating this partition/table to it's own dbspace with sufficient chunk space, then you'll most like avoid multiple extents. OR - if you've added another chunk to the existing dbspace that is large enough to house the table, we will utilize that large extent space available from that chunk, versus fighting for room in a more heavily used chunk. Many clients now use the ALTER FRAGMENT ... INIT to reorg tables. I haven't played with it at all, so no comments about it other than it's "popular". HTH - Mark Mark Scranton Principal Consultant/Teacher IBM Denver IBM Software Group - Data Management Office: 303-773-5067 Cell: 303-929-0914 email: mscranto@us.ibm.com RPhillips@ce-a.co m To: ids@iiug.org Sent by: cc: forum.subscriber@ Subject: RE: Removing excessive extents [1277] iiug.org 06/04/2003 11:31 AM You mean like this (http://www.iiug.org/forums/ids/index.cgi?read=377) Which I'm not sure why it is supposed to work. "I have used the following technique on some of my tables when I could not get enough down time to do a "proper" resizing (unload, change schema, drop table, rebuild, load). alter the tables next extent size so that it is large enough to hold the whole table. Then alter one of the indexes to cluster and then drop the cluster. it would look like this alter table table_x modify next size 123456; alter index table_x_index to cluster; alter index table_x_index to not cluster;" -----Original Message----- From: rkusenet [mailto:rkusenet@sympatico.ca] Sent: Wednesday, June 04, 2003 1:12 PM To: RPhillips@ce-a.com Cc: ids@iiug.org Subject: Re: Removing excessive extents [1275] Another easier way can be to create a cluster index on hospital table and then drop that cluster index. when u do that, it will relocate data from its present position, which would automatically free extents. ----- Original Message ----- From: <RPhillips@ce-a.com> To: <ids@iiug.org> Sent: Wednesday, June 04, 2003 12:41 Subject: RE: Removing excessive extents [1275] > Could you give me an overview of how this works. The only documentation I > have on fragments is regarding creating fragments in different dbspaces, and > deleteing them from different dbspaces. > > Also I did a dbschema on one of the tables that have too many extents and it > didn't give me the size of the extent... > > { TABLE "informix".hospitals row size = 854 number of columns = 34 index > size = 12 > } > create table "informix".hospitals > ( > hospital_id serial not null , > hospital_name char(20), > hospital_full_name char(50), > street char(62), > street2 char(62), > city char(30), > state char(2), > zip char(10), > phone char(14), > fax char(14), > mstreet char(62), > mstreet2 char(62), > mcity char(30), > mstate char(2), > mzip char(10), > use_mail_address char(1), > region_id integer, > district_id integer, > provider_number char(50), > contract_start date, > contract_end date, > ob char(1), > comments char(255), > in_percent decimal(7,4), > out_percent decimal(7,4), > profee_in_percent decimal(7,4), > profee_out_percent decimal(7,4), > last_modified date, > iscurrent char(1), > modified_by char(3), > flat_rate money(16,2), > ptreecode char(5), > terms integer, > email varchar(50), > unique (hospital_id) > ); > revoke all on "informix".hospitals from "public";> > -----Original Message----- > From: rkusenet [mailto:rkusenet@sympatico.ca] > Sent: Wednesday, June 04, 2003 12:25 PM > To: ids@iiug.org; RPhillips@ce-a.com > Subject: Re: Removing excessive extents [1273] > > > > ----- Original Message ----- > From: <RPhillips@ce-a.com> > To: <ids@iiug.org> > Sent: Wednesday, June 04, 2003 11:48 > Subject: Removing excessive extents [1273] > > > > I have two tables that I want to reorganize to reduce the number of > extents > > that they have. I am clear on how to do this by copying the data in the > > table, dropping the table and then recreating it with a larger extent size > > and then putting the data back. > > > > My question is what happens to tables that have a foreign key based on > this > > table when I drop it? > > Informix will prevent you from dropping. > > If reducing the number of extent is your goal, then use alter fragment > >

----- Original Message ----- From: Mark Scranton <mscranto@us.ibm.com> At: 6/ 5 12:29 > A couple of comments around this discussion... > > With respect to "setting the index to non-cluster"...a clustered index > physically doesn't look any different than a non-clustered index. And since > we don't maintain the clustered/physical order of the data rows with a > cluster index (something many find surprising...Sybase does this btw), it Just to clarify, Sybase does not exactly maintain the clustered order of the table since it really does not sort the table to cluster it in the same way that IDS (well all Informix versions) does. Informix simply sorts the table so that rows adjacent in the index by the cluster key are physically located together on disk in the table structure. This means that when the engine recognises that it will be accessing a series of sequential rows by that cluster key it needs to only access the index once then just begin a table scan starting with that row. Obviously with the real possibility that the table has not remained clustered it's not that simple but the efficiency is the point. In Sybase if a table has a 'CLUSTERED' index it means that there is no physical table anymore, each row is attached to its index node in the clustered index. This makes it VERY quick to access any particular row using that index but slower for large sequential scans using that key. Also sequential scans become inefficient even when not using a key because the index structure has to be followed to read each row. Art S. Kagel