728x90
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 type
2. Transfer data by "insert into ... select * from ..." clause
3. Change the table to standard type, rename or drop the original table, then rename the object table to original table's name
4. Recreate any necessary index and constraint on the new table
Benefit:
1. Process is very efficient
2. Can avoid long transaction
3. Minimize the impact to the original table, can stop the process at any time
4. Don't lose any data
Limitation:
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 space
1. Before action
oncheck -pt stores:customer_new
TBLspace Report for stores:informix.customer_new
Physical Address 2:536
Creation date 11/11/2009 12:11:21
TBLspace Flags 801 Page Locking
TBLspace use 4 bit bit-maps
Maximum 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 Pages
0 2:1438 8 8
8 2:1454 152 152
160 2:1610 40 40
200 2:1654 48 48
248 2:1706 40 40
288 2:1750 48 48
336 2:1802 40 40
376 2:1846 48 48
424 2:1898 40 40
464 2:1942 48 48
512 2:1994 40 40
552 2:2038 48 48
600 2:2090 40 40
640 2:2134 48 48
688 2:2190 88 88
776 2:2286 88 88
864 2:2382 96 96
960 2:2486 80 80
1040 2:2574 96 96
1136 2:2678 80 80
1216 2:2766 96 96
1312 2:2870 80 80
1392 2:2958 96 96
1488 2:3062 80 80
1568 2:3150 96 96
1664 2:3254 80 80
1744 2:3342 64 64
1808 2:3414 96 96
1904 2:3518 80 80
1984 2:3606 96 96
2080 2:3718 176 176
2256 2:3910 176 176
2432 2:4102 192 192
2624 2:4310 160 160
2784 2:4486 160 160
2944 2:4662 192 192
3136 2:4870 160 160
3296 2:5046 192 192
3488 2:5254 160 160
3648 2:5430 128 128
Allocated 3776 pages, there are 52632 rows in this table.
2. Delete some data
dbaccess stores <<!
delete from customer_new where customer_num > 1000;
!
Database selected.
52590 row(s) deleted.
Database closed.
oncheck -pt stores:customer_new
TBLspace Report for stores:informix.customer_new
Physical Address 2:536
Creation date 11/11/2009 12:11:21
TBLspace Flags 801 Page Locking
TBLspace use 4 bit bit-maps
Maximum 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 Pages
0 2:1438 8 8
8 2:1454 152 152
160 2:1610 40 40
200 2:1654 48 48
248 2:1706 40 40
288 2:1750 48 48
336 2:1802 40 40
376 2:1846 48 48
424 2:1898 40 40
464 2:1942 48 48
512 2:1994 40 40
552 2:2038 48 48
600 2:2090 40 40
640 2:2134 48 48
688 2:2190 88 88
776 2:2286 88 88
864 2:2382 96 96
960 2:2486 80 80
1040 2:2574 96 96
1136 2:2678 80 80
1216 2:2766 96 96
1312 2:2870 80 80
1392 2:2958 96 96
1488 2:3062 80 80
1568 2:3150 96 96
1664 2:3254 80 80
1744 2:3342 64 64
1808 2:3414 96 96
1904 2:3518 80 80
1984 2:3606 96 96
2080 2:3718 176 176
2256 2:3910 176 176
2432 2:4102 192 192
2624 2:4310 160 160
2784 2:4486 160 160
2944 2:4662 192 192
3136 2:4870 160 160
3296 2:5046 192 192
3488 2:5254 160 160
3648 2:5430 128 128
Although the data was deleted, but the space is not released.
3. Then run Repack and Shrink
dbaccess sysadmin <<!
> execute function task("table repack shrink", "customer_new", "stores");
> !
Database selected.
(ｅxpression) Succeeded: table repack shrink stores:informix.customer_new
1 row(s) retrieved.
Database closed.
4. After action
oncheck -pt stores:customer_new
TBLspace Report for stores:informix.customer_new
Physical Address 2:536
Creation date 11/11/2009 12:11:21
TBLspace Flags 801 Page Locking
TBLspace use 4 bit bit-maps
Maximum 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 Pages
0 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
728x90
'Informix > informix reference' 카테고리의 다른 글
| informix link (0) | 2011.03.29 |
|---|---|
| Chunk path rename - is it possible? (0) | 2011.03.28 |
| informix 트랜잭션 관리 (0) | 2011.03.12 |
| Informix JDBC 연결 테스트 (0) | 2011.03.09 |
| dbexport/dbimport 수행시 extent size 변경 (0) | 2011.03.08 |