table size with compress and with no compress
J1604Jul 12 2010 — edited Jul 13 2010Hi,
I have a table occupies 35.5 GB space with 120 Mill records in it.
I thought compress would save me a lot of space so I did.
Here is how I compressed:
I have created a seperate tablespace and within the new tablespace I have created multiple database files each file is having 2 GB.
then Insert all the records off the original table to the new one then,
I ran query below so see size
SQL> select ((blocks*8192)-(blocks*avg_space))/1024/1024 "MB size", empty_blocks,
2 avg_space, num_freelist_blocks
3 from user_tables
4 where table_name = 'CLMs';
MB size EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
2059.88281 0 0 0
----------------------------------------
select ((blocks*8192)-(blocks*avg_space))/1024/1024 "MB size", empty_blocks,
2 avg_space, num_freelist_blocks
3 from user_tables
4 where table_name = 'CLMS';
MB size EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
35504.2422 0 0 0
in above queries first one results 2 gb over all space after compress and second one is the original with NO compress size.
I have a question Is these size computed are reliable .?
because with compress oracle inserts data into blocks within database files.
If these blocks are half way filled and left empty and then go to the next block and so on.
Is above sizes take in account for the empty blocks ..?
I am not sure how to explain this
Please help,
JP