Oracle 9.2, block size 8k
If I have a table which is never going to grow (eg it is legacy data) am I better to re-create it with a single, large extent or extents which are uniform to the tablespace it will sit in?
The tablespaces for data has EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8192K
Examples of the fixed tables are:
Size(blocks) 197632 (I guess this is 197,632 x 8k - 1,581,056k ?)
Extents: 193
and
Size(blocks) 75776 (I guess this is 75,776 x 8k - 606,208k ?)
Extents: 74
but we have a lot of
Size(blocks) 1024 (I guess this is 1,024 x 8k - 8,192k ? Cuased by the uniform extent size of 8192k)
Extents: 1
select owner, segment_name, segment_type, blocks, extents from dba_segments where tablespace_name='PRMS_DATA';
OWNER SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS
------------- ----------------------------------- ------------- ---------- ----------
IFSAPP PRMS_PRODUCT_KIT TABLE 1024 1
IFSAPP PRMS_CUSTOMER_INVOICE TABLE 1024 1
IFSAPP PRMS_INVENTORY_TRANSACTION_2 TABLE 48128 47
IFSAPP PRMS_AP_INVOICE_HEADER TABLE 1024 1
IFSAPP PRMS_PRODUCT_ROUTING TABLE 1024 1
IFSAPP PRMS_PRODUCT_COST TABLE 1024 1
IFSAPP PRMS_WAREHOUSE TABLE 1024 1
IFSAPP PRMS_CUSTOMER_CREDIT_HEADER TABLE 1024 1
IFSAPP PRMS_VENDOR TABLE 1024 1
IFSAPP PRMS_PRODUCT_VENDOR TABLE 1024 1
IFSAPP PRMS_CUSTOMER TABLE 1024 1
IFSAPP PRMS_PURCHASE_ORDER_DETAIL TABLE 36864 36
IFSAPP PRMS_PRODUCT_FORECAST TABLE 3072 3
IFSAPP PRMS_AP_INVOICE_DETAIL TABLE 9216 9
IFSAPP PRMS_PRODUCT_STRUCTURE TABLE 2048 2
IFSAPP PRMS_PURCHASE_ORDER_HEADER TABLE 10240 10
IFSAPP PRMS_CUSTOMER_ORDER_TEXT TABLE 25600 25
IFSAPP PRMS_CUSTOMER_ORDER_DETAIL TABLE 75776 74
IFSAPP PRMS_CUSTOMER_ORDER_HEADER TABLE 44032 43
IFSAPP PRMS_CUSTOMER_ORDER_SHIPMENT TABLE 43008 42
IFSAPP PRMS_CUSTOMER_QUOTATION_DETAIL TABLE 33792 33
IFSAPP PRMS_CUSTOMER_QUOTATION_HEADER TABLE 16384 16
IFSAPP PRMS_CUSTOMER_QUOTATION_NOTES TABLE 1024 1
IFSAPP PRMS_CUSTOMER_QUOTATION_PRICE TABLE 13312 13
IFSAPP PRMS_CUSTOMER_SHIP_TO TABLE 1024 1
IFSAPP PRMS_GL_ACCOUNT TABLE 1024 1
IFSAPP PRMS_GL_ACCOUNT_SUMMARY TABLE 3072 3
IFSAPP PRMS_GL_POSTING TABLE 34816 34
IFSAPP PRMS_INVENTORY_TRANSACTION TABLE 197632 193
IFSAPP PRMS_CUSTOMER_CREDIT_DETAIL TABLE 3072 3
IFSAPP PRMS_PRICING TABLE 13312 13
IFSAPP PRMS_PRODUCT TABLE 5120 5
IFSAPP PRMS_PRODUCT_LOT TABLE 9216 9
IFSAPP PRMS_CUSTOMER_SALES TABLE 1024 1