# calculating size of a table

Show 40 post(s) from this thread on one page
Page 1 of 2 12 Last
• 07-07-2005, 02:02 AM
Vipassana
calculating size of a table
Does this forumla still work good in Oracle 10g for calculating the size of a table?

AVG_ROW_LEN*NUM_ROWS*(1+PCT_FREE/100)*1.15

Or is there anyother better way to calculate? Pls advice.
• 07-07-2005, 04:00 AM
davey23uk
select sum(bytes) from user_segments where segment_name = 'TABLE_NAME' will give you the real table size

the above query never was accurate and was dependant on the table being analyzed right up to date

surely you have this information since you work for oracle?
• 07-07-2005, 04:13 AM
Vipassana
hey thx dave. used to work for oracle, not anymore.

and so, u r saying we need not analyze the table if i am using

select sum(bytes) from user_segments where segment_name = 'TABLE_NAME' ??

hmm.. big diff between this and the query i mentioned (though i was analyzed up to date)
• 07-07-2005, 04:26 AM
davey23uk
of course you need to analyze the table for the CBO to work properly! How much of a difference?
• 07-07-2005, 05:01 AM
Vipassana
select avg_row_len * num_rows * (1 + PCT_FREE/100) * 1.15
from dba_tables where table_name = 'PRODUCT'
/

AVG_ROW_LEN*NUM_ROWS*(1+PCT_FREE/100)*1.15
------------------------------------------
13925.12

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

SUM(BYTES)
----------
65536
• 07-07-2005, 07:05 AM
jmodic
So? One is (aproximately) how much space is needed to store all those rows, the other is how much space is actually allocated by that table.
• 07-07-2005, 10:53 AM
mike9
Quote:

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

SUM(BYTES)
----------
65536
I guess, I don't know why, that your tables uses one extent and this extent is probably something like 64k large... :p
• 07-07-2005, 09:57 PM
Vipassana
Quote:

Originally Posted by jmodic
So? One is (aproximately) how much space is needed to store all those rows, the other is how much space is actually allocated by that table.

I think you have added more confusion :confused:

First of all which is which? I presume the first statement means "how much space is needed to store all those rows" and the second means "the other is how much space is actually allocated by that table".

Secondly I didnt understand what you meant by "how much space is needed to store all those rows". What does "all the rows" mean here? You mean the rows that are existing? And also what does "how much space is needed..." mean? You mean what is the maximum space it can occupy?

Thridly I didn't understand what you meant by "the other is how much space is actually allocated by that table". You mean how is actually used? If so "used out of what (how much)?"

Two statements you made and they are so cryptic :)
• 07-08-2005, 01:34 AM
TimHall
Hi.

Sorry for coming late to the party, but if you're using 10g you might want to take look at this:

http://www.oracle-base.com/articles/...rce_estimation

It takes some of the guesswork out of sizing estimations and growth trends.

Cheers

Tim...
• 07-08-2005, 06:44 AM
bazza
Vipassana, what do you want to know?

How big your table is now? - use dba_segments

How big your table could be? - use your formula and consider how many rows will be inserted and the rate they are inserted.

Perhaps a refresh of the concepts manual is in order.

Bazza
Show 40 post(s) from this thread on one page
Page 1 of 2 12 Last