HI

What should be Ideal size for Extents in LMT?
For example I have Inventory_Data, Inventory_IDX tablespace where i am storing data , indexes created for Inventory Tables. both tablespaces are LMT
All the tables in the Invetory_Data are IOT and their secondary indexes are stored in the Inventory_IDX. The avg Row length for each table in the Indentory_Data is 400 bytes and around 12-50 bytes for tables in the Inventory_IDX.
All the tables in the Inventory tablespace will roughly contain 10 lack+ records and will have heavy updates and inserts on these tables and around 50-100 concurrent users will be playing with the system
Now, how should i set BLOCK_SIZE and EXTENT_SIZE for both.
IF BLOCK_SIZE = 8K and EXTENT_SIZE = 64 K
Avg Row Length : 400 bytes
No of Rows in the Block: 20
IF PCTINCREASE = 20 No of Free Rows per Block : 4
No of Rows per Block = 20-4 =16
No of Rows per Extent = 16 x 8 = 128
with same calculation i could have 162 rows per block for Index
tablespace tables


IF BLOCK_SIZE = 8K and EXTENT_SIZE = 1M
Avg Row Length : 400 bytes
No of Rows in the Block: 20
IF PCTINCREASE = 20 No of Free Rows per Block : 4
No of Rows per Block = 20-4 =16
No of Rows per Extent = 16 x 128 = 2048
with same calculation i could have 162 rows per block and 20864
rows per extent for Index tablespace tables

With this setting Can i able to avoid Internal fragmentation ?
How should i maintain INITTRANS , MAXTRANS, PCTFREE, PCTUSED
and other Segment space parameters ?
Will larger or reduced block size will help in this situation ?


Regards
Shrikant Kulkarni
Pune, India