calculating size of a table
Does this forumla still work good in Oracle 10g for calculating the size of a table?
Or is there anyother better way to calculate? Pls advice.
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?
Just encountered this post and thought some of the people encountering it as well and that would like to calculate existing row and table sizes in order to calculate the entire sizing impact of new rows in the database can use a script I wrote. The script predicts the extra size needed for new rows inserted into a table with the table indexes and all refereing tables (with foreign keys) as well. Might be useful as it was to me...
You can find it here.
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)
Last edited by Vipassana; 07-07-2005 at 05:15 AM.
of course you need to analyze the table for the CBO to work properly! How much of a difference?
select avg_row_len * num_rows * (1 + PCT_FREE/100) * 1.15
from dba_tables where table_name = 'PRODUCT'
SQL> select sum(bytes) from user_segments where segment_name = 'PRODUCT';
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.
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
I think you have added more confusion
Originally Posted by jmodic
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
Sorry for coming late to the party, but if you're using 10g you might want to take look at this:
It takes some of the guesswork out of sizing estimations and growth trends.
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.
Click Here to Expand Forum to Full Width