Table extents differences on recreate
When I recreate a table using the 'create table....as select from...' syntax,
the new table gets created with the extent size/next size of 16/16. The
original table has 13 extents, however, the new table is in 2 extents. This
would not surprise me if the original table was small. In my case the original
table/new has 14 million records. Why is the new table in 2 extents and its
next size so large (see details below) despite the extent sizes being 16/16?
My colleague confirmed this with oncheck -pT
System Info: Informix 12.10.FC6, HP-UX 11.31.ia64
Here are the details (Sorry about the formatting mess here):
a. _fi_debt_price is fi_debt_price renamed.
b. fi_debt_price is created by:
create table dba.fi_debt_price_new as select * from dba._fi_debt_price;
rename table fi_debt_price_new to fi_debt_price;
_fi_debt_price (oncheck -pT)
TBLspace Report for evaluation:dba._fi_debt_price
Physical Address 25:3998735
Creation date 05/21/2011 12:35:11
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 95
Number of special columns 0
Number of keys 0
Number of extents 13
Current serial value 1
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 30336
Next extent size 96256
Number of pages allocated 728192
Number of pages used 704305
Number of data pages 704130
Number of rows 14612101
Partition partnum 11534727
Partition lockid 11534727
Extents
Logical Page Physical Page Size Physical Pages
0 26:311298 222848 222848
222848 26:1983008 12032 12032
234880 26:2601949 12032 12032
246912 26:3228816 12032 12032
258944 26:4054671 12032 12032
270976 26:4887549 24064 24064
295040 32:1516317 48128 48128
343168 32:4448664 48128 48128
391296 25:946816 48128 48128
439424 39:461787 48128 48128
487552 39:3819044 48128 48128
535680 46:262467 96256 96256
631936 49:3008520 96256 96256
fi_debt_price (oncheck -pT)
TBLspace Report for evaluation:dba.fi_debt_price
Physical Address 49:4024583
Creation date 10/02/2018 13:15:18
TBLspace Flags 801 Page Locking
TBLspace use 4 bit bit-maps
Maximum row size 95
Number of special columns 0
Number of keys 0
Number of extents 2
Current serial value 1
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 8
Next extent size 131072
Number of pages allocated 786049
Number of pages used 730788
Number of data pages 730606
Number of rows 14612101
Partition partnum 11535665
Partition lockid 11535665
Extents
Logical Page Physical Page Size Physical Pages
0 54:1433687 4737 4737
4737 54:1454432 781312 781312
Original post:
When I recreate a table using the 'create table....as select from...' syntax,
the new table gets created with the extent size/next size of 16/16. The
original table has 13 extents, however, the new table is in 2 extents. This
would not surprise me if the original table was small. In my case the original
table/new has 14 million records. Why is the new table in 2 extents and its
next size so large (see details below) despite the extent sizes being 16/16?
...<oncheck -pt output cut>...
Response:
Not sure when it was added, but at some time, if an extent gets added to a
table, and this new extent is contiguous with the current last extent of the
table, the newly added extent is merged with the previous last extent (so all
that needs to be done is increasing the size of the previous last extent to
include the newly added pages). This change was made to try and reduce the
number of extents on a table. So it seems like when this new table was built,
it went into a chunk without any other activity (or very little activity) so
after it got it's 2nd extent, all the new extent requests after that were
contiguous with the 2nd extent, so they were able to be merged. However,
because the initial extent size was default, I believe we are able to keep
track of new extent requests, so that extent size doubling was able to happen.
So that extent size doubling likely explains the resulting next extent size
from the oncheck -pT output. I believe there is an issue where partition page
next extent size being out of sync with system catalog next extent size is a
known issue when extent size doubling happens. Or at least I'm sure I've seen
that before.
Jacques Renaut
HCL Informix Advanced Support