Monday, March 28, 2011

Estimating Database Table Size

Table->PRODUCT

Sql>analyze table PRODUCTcompute statistics;

Sql> select extent_id, bytes, blocks
from user_extents
where segment_name = 'PRODUCT'
and segment_type = 'TABLE'

"EXTENT_ID" "BYTES" "BLOCKS"
0 1048576 128
1 1048576 128
2 1048576 128


Sql> select blocks, empty_blocks,
avg_space, num_freelist_blocks
from user_tables
where table_name = 'PRODUCT'

"BLOCKS" "EMPTY_BLOCKS" "AVG_SPACE" "NUM_FREELIST_BLOCKS"
376 8 1021 0

Anaylsis
the above shows us:
• we have 128*3(384) blocks allocated to the table
• 8 blocks are totally empty
• 376 block contains data (the other block is used by the system)
• we have an average of about 1k(1021, AVG_SPACE) free on each block used.

Therefore, our table ('FMT027T')

• consumes 376 block
• of which 376block * 8k blocksize - 8 block * 1k free = 3000k is used for our data.


Another Simple SQL is

select sum(bytes) from user_segments where segment_name = 'PRODUCT';

1 comment:

Ecommerce developer said...

Your post is truly informative for me and i am so grateful to you for sharing this informative post here. really great job done by you.