output of oncheck -PT, about table size
Folks,
IDS11.FC8 , Redhat Linux.
I attached the table definition and the output of oncheck -PT. The table
has about 170,000 rows.
You can see,
BLspace Usage Report for noaa:fqu.my_gran_details_np
Type Pages Empty Semi-Full Full Very-Full
---------------- ---------- ---------- ---------- ---------- ----------
Free 8809
Bit-Map 179
Index 0
Data (Home) 77255
Data (Remainder) 0 0 0 0 0
TBLspace BLOBs 634653 0 0 264309 370344
----------
Total Pages 720896
The question is, how many data pages the table really used?
"Data (Home)" line should be the reasonable size, 77255 pages , but
"TBLspace BLOBs" line, 634653 pages , is what? used or just assigned
and not really used?
Thanks,
Frank
create table "fqu".my_gran_details_np
(
sub_inventory_id integer not null ,
inventory_id integer not null ,
npoess_doc_ref LIST(varchar(255) not null),
quality_sum_name LIST(varchar(255) not null),
quality_sum_value LIST(varchar(255) not null),
geo_lat LIST(decimal(10,5) not null),
geo_lon LIST(decimal(10,5) not null),
anc_filename LIST(varchar(255) not null),
aux_filename LIST(varchar(255) not null),
primary key (sub_inventory_id) constraint "fqu".my_gran_details_np_pk
) extent size 4096 next size 4096 lock mode row;
revoke all on "fqu".my_gran_details_np from "public" as "fqu";
alter table "fqu".my_gran_details_np add constraint (foreign
key (inventory_id) references "informix".ds_np_agg constraint
"informix".my_gran_details_np_fk);
alter table "fqu".my_gran_details_np add constraint (foreign
key (sub_inventory_id) references "informix".ds_np_gran constraint
"informix".my_gran_details_np_fk1);
[fqu@franklin npp_revamp]$ oncheck -pT noaa:my_gran_details_np
TBLspace Report for noaa:fqu.my_gran_details_np
Physical Address 19:2217503
Creation date 01/17/2012 23:32:57
TBLspace Flags d02 Row Locking
TBLspace contains VARCHARS
TBLspace contains TBLspace
BLOBs
TBLspace use 4 bit bit-maps
Maximum row size 2030
Number of special columns 14
Number of keys 0
Number of extents 6
Current serial value 1
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 2048
Next extent size 65536
Number of pages allocated 720896
Number of pages used 719333
Number of data pages 77255
Number of rows 172277
Partition partnum 12583879
Partition lockid 12583879
Extents
Logical Page Physical Page Size Physical Pages
0 19:4499406 2048 2048
2048 19:4496462 2048 2048
4096 19:4501454 290816 290816
294912 19:4792478 98304 98304
393216 19:4890990 65536 65536
458752 20:3 262144 262144
TBLspace Usage Report for noaa:fqu.my_gran_details_np
Type Pages Empty Semi-Full Full Very-Full
---------------- ---------- ---------- ---------- ---------- ----------
Free 8809
Bit-Map 179
Index 0
Data (Home) 77255
Data (Remainder) 0 0 0 0 0
TBLspace BLOBs 634653 0 0 264309 370344
----------
Total Pages 720896
Unused Space Summary
Unused data bytes in Home pages 57865032
Unused data bytes in Remainder pages 0
Unused bytes in TBLspace Blob pages 195999620
Home Data Page Version Summary
Version Count
0 (current) 77255
Index 807_2338 fragment partition dbdata00 in DBspace
dbdata00
Physical Address 19:2217504
Creation date 01/17/2012 23:32:57
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 2030
Number of special columns 0
Number of keys 1
Number of extents 10
Current serial value 1
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 13
Next extent size 208
Number of pages allocated 1443
Number of pages used 1277
Number of data pages 0
Number of rows 0
Partition partnum 12583880
Partition lockid 12583879
Extents
Logical Page Physical Page Size Physical Pages
0 19:1417168 13 13
13 19:1417072 26 26
39 19:1430209 52 52
91 19:1439075 104 104
195 19:4498510 208 208
403 19:4498878 208 208
611 19:4792270 208 208
819 19:4890782 208 208
1027 19:4956686 208 208
1235 19:4957054 208 208
TBLspace Usage Report for noaa:fqu.my_gran_details_np
Type Pages Empty Semi-Full Full Very-Full
---------------- ---------- ---------- ---------- ---------- ----------
Free 306
Bit-Map 1
Index 1136
Data (Home) 0
Data (Remainder) 0 0 0 0 0
----------
Total Pages 1443
Unused Space Summary
Unused data slots 0
Unused data bytes in Remainder pages 0
Home Data Page Version Summary
Version Count
0 (current) 0
Index Usage Report for index 807_2338 on noaa:fqu.my_gran_details_np
Average Average
Level Total No. Keys Free Bytes
----- -------- -------- ----------
1 1 15 1844
2 15 74 1124
3 1120 153 20
----- -------- -------- ----------
Total 1136 152 36
Index 807_2341 fragment partition dbdata00 in DBspace
dbdata00
Physical Address 19:2217505
Creation date 01/17/2012 23:33:46
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 2030
Number of special columns 0
Number of keys 1
Number of extents 9
Current serial value 1
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 10
Next extent size 160
Number of pages allocated 1120
Number of pages used 977
Number of data pages 0
Number of rows 0
Partition partnum 12583881
Partition lockid 12583879
Extents
Logical Page Physical Page Size Physical Pages
0 19:1184674 10 10
10 19:1417616 30 30
40 19:1425023 40 40
80 19:1430695 80 80
160 19:4498718 160 160
320 19:4499086 320 320
640 19:4956526 160 160
800 19:4956894 160 160
960 19:4957262 160 160
TBLspace Usage Report for noaa:fqu.my_gran_details_np
Type Pages Empty Semi-Full Full Very-Full
---------------- ---------- ---------- ---------- ---------- ----------
Free 257
Bit-Map 1
Index 862
Data (Home) 0
Data (Remainder) 0 0 0 0 0
----------
Total Pages 1120
Unused Space Summary@