Question
Too many extents in a table can cause problems:
1. Can impact the performance of accessing the table, engine can not access the data effectively as they are stored discontinuously.
2. Can reach the storage limitation of the table, more data can not be inserted into the table again.
So
1. Need to reorganize a table with many extents and reclaim the space.
2. Decide to delete most rows from a huge table
3. Decide to change the structure of a huge table
Answer
- You can reorganize a table using oncheck -me in IDS 7.31.
- Running IDS 9.x or IDS 10.x, follow these steps:
1. Create an object raw table with the same structure as the original table, the differences are table name and table type2. Transfer data by "insert into ... select * from ..." clause3. Change the table to standard type, rename or drop the original table, then rename the object table to original table's name4. Recreate any necessary index and constraint on the new tableBenefit:1. Process is very efficient2. Can avoid long transaction3. Minimize the impact to the original table, can stop the process at any time4. Don't lose any dataLimitation:During the transfer of data from original table to object table, the data in original table can be changed unless you lock it or complete this task during a maintenance window.Example:We will reorganize a table customer, with one index and one primary key;create raw table customer_new(customer_num serial not null ,fname char(15),lname char(15),company char(20),address1 char(20),address2 char(20),city char(15),state char(2),zipcode char(5),phone char(18)) extent size 16 next size 16 lock mode page;insert into customer_new select * from customer;alter table customer_new type (standard);drop table customer;rename table customer_new to customer;set pdqpriority high;alter table customer add constraint primary key (customer_num) constraint pk_customer;create index zip_ix on customer (zipcode) ;- From IDS 11.5, the table can be reorganized by another way using repack and shrink:
Example:Need to repack & shrink the table customer_new and release the free space1. Before actiononcheck -pt stores:customer_newTBLspace Report for stores:informix.customer_newPhysical Address 2:536Creation date 11/11/2009 12:11:21TBLspace Flags 801 Page LockingTBLspace use 4 bit bit-mapsMaximum row size 134 Number of special columns 0 Number of keys 0 Number of extents 40 Current serial value 962567 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 2 First extent size 8 Next extent size 32 Number of pages allocated 3776 Number of pages used 3761 Number of data pages 3760 Number of rows 52632 Partition partnum 2097237 Partition lockid 2097237 Extents Logical Page Physical Page Size Physical Pages0 2:1438 8 88 2:1454 152 152160 2:1610 40 40200 2:1654 48 48248 2:1706 40 40288 2:1750 48 48336 2:1802 40 40376 2:1846 48 48424 2:1898 40 40464 2:1942 48 48512 2:1994 40 40552 2:2038 48 48600 2:2090 40 40640 2:2134 48 48688 2:2190 88 88776 2:2286 88 88864 2:2382 96 96960 2:2486 80 801040 2:2574 96 961136 2:2678 80 801216 2:2766 96 961312 2:2870 80 801392 2:2958 96 961488 2:3062 80 801568 2:3150 96 961664 2:3254 80 801744 2:3342 64 641808 2:3414 96 961904 2:3518 80 801984 2:3606 96 962080 2:3718 176 1762256 2:3910 176 1762432 2:4102 192 1922624 2:4310 160 1602784 2:4486 160 1602944 2:4662 192 1923136 2:4870 160 1603296 2:5046 192 1923488 2:5254 160 1603648 2:5430 128 128Allocated 3776 pages, there are 52632 rows in this table.2. Delete some datadbaccess stores <<!delete from customer_new where customer_num > 1000;!Database selected.52590 row(s) deleted.Database closed.oncheck -pt stores:customer_newTBLspace Report for stores:informix.customer_newPhysical Address 2:536Creation date 11/11/2009 12:11:21TBLspace Flags 801 Page LockingTBLspace use 4 bit bit-mapsMaximum row size 134 Number of special columns 0 Number of keys 0 Number of extents 40 Current serial value 962567 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 2 First extent size 8 Next extent size 32 Number of pages allocated 3776 Number of pages used 3761 Number of data pages 3 Number of rows 42 Partition partnum 2097237 Partition lockid 2097237 Extents Logical Page Physical Page Size Physical Pages0 2:1438 8 88 2:1454 152 152160 2:1610 40 40200 2:1654 48 48248 2:1706 40 40288 2:1750 48 48336 2:1802 40 40376 2:1846 48 48424 2:1898 40 40464 2:1942 48 48512 2:1994 40 40552 2:2038 48 48600 2:2090 40 40640 2:2134 48 48688 2:2190 88 88776 2:2286 88 88864 2:2382 96 96960 2:2486 80 801040 2:2574 96 961136 2:2678 80 801216 2:2766 96 961312 2:2870 80 801392 2:2958 96 961488 2:3062 80 801568 2:3150 96 961664 2:3254 80 801744 2:3342 64 641808 2:3414 96 961904 2:3518 80 801984 2:3606 96 962080 2:3718 176 1762256 2:3910 176 1762432 2:4102 192 1922624 2:4310 160 1602784 2:4486 160 1602944 2:4662 192 1923136 2:4870 160 1603296 2:5046 192 1923488 2:5254 160 1603648 2:5430 128 128Although the data was deleted, but the space is not released.3. Then run Repack and Shrinkdbaccess sysadmin <<!> execute function task("table repack shrink", "customer_new", "stores");> !Database selected.(expression) Succeeded: table repack shrink stores:informix.customer_new 1 row(s) retrieved.Database closed.4. After actiononcheck -pt stores:customer_newTBLspace Report for stores:informix.customer_newPhysical Address 2:536Creation date 11/11/2009 12:11:21TBLspace Flags 801 Page LockingTBLspace use 4 bit bit-mapsMaximum row size 134 Number of special columns 0 Number of keys 0 Number of extents 1 Current serial value 962567 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 2 First extent size 8 Next extent size 32 Number of pages allocated 8 Number of pages used 8 Number of data pages 3 Number of rows 42 Partition partnum 2097237 Partition lockid 2097237 Extents Logical Page Physical Page Size Physical Pages0 2:1438 8 8
The pages didn't store data were released. Can reclaim lots of space and decrease the number of table's extents.
Related information
Reorganize table by oncheck in IDS 7.31.xDx
Reorganize table by repack and shrink in IDS 11.5
http://www-01.ibm.com/support/docview.wss?uid=swg21330735