Skip to Main Content
  • Questions

  • calcule space required for tables and indexes

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: June 13, 2000 - 6:56 am UTC

Last updated: September 16, 2012 - 4:25 am UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

how to calculate space required for tables and indexes ?

and Tom said...

The only accurate method of doing this in my experience is to load a representative percentage of the data into sample tables and analyze them to find their actual space usage.

I recommend loading representative records somewhere in the range of 0.1% (for really large tables -- 100,000,000 records or more), 1% for medium sized tables or 10% for small tables. Then, analyze the table and see how many blocks it took to contain that much data, see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:231414051079 
for a guide to doing that.  Then multiply by the appropriate factor.  This works for pretty much any object you would want to create.

There are formula, that may or may not be accurate depending on your circumstances, that can give you a "ball park" estimate that may be wildly inaccurate.  The reasons they may be inaccurate are

o different block sizes will give you different results as rows are chained on smaller blocks and so on.

o you size numbers correctly as numbers are stored in a packed format (not binary) in Oracle and hence the number 999 takes more room then the number 1,000,000,000 to store.

o block overhead varies according to your storage clause and other table level parameters.

o there are lots of different table types -- heap, index organized, hash, hash cluster, clustered and so on.  Each has their own nuances that the "formula" don't take into consideration.

They have stopped publishing these formula in the docs in the 8.x timeframe.  You can refer to the 7.3 docs
http://technet.oracle.com/doc/server73x/SAD73/apA.htm#toc201 <code>
but remember to adjust for differences...


Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

9i/10G?

Kashif, October 11, 2004 - 9:04 am UTC

Hi Tom,

Is there a different method to calculate space requirements for tables/indexes in 9i or 10G? TOAD and Designer 6i seem to provide a method for calculating size requirements, and I was curious to hear what you thought about those methods, if you are familiar with them. Thanks!

Kashif

Tom Kyte
October 11, 2004 - 9:39 am UTC

there is only one way I've seen work.

and i wrote about it in the original response above....

Estimate tables / index size

Alejandra Aruachan, March 09, 2005 - 9:18 am UTC

I read the previous and I'd like to know if Oracle have a tool like Toad to estimate tables size , specially if you are implementing a new system. I'd like to find a tool to estimate the table more easy specially if you have many tables



Thanks

A reader, September 10, 2012 - 4:19 pm UTC

I need to estimate the size of table, index growth based on the data loaded and calculate the amount to disk growth per day. Can you please let me there is any explanation on making this estimation on your website?
Tom Kyte
September 14, 2012 - 2:56 pm UTC

well, read the original answer above.

you'll have to build your table, load it up with some data, measure, load it up with more, measure, repeat. and then report.


Alexander, September 14, 2012 - 3:14 pm UTC

Doesn't the segment advisor (dbms_space.object_growth_trends) do this out of the box for us?
Tom Kyte
September 14, 2012 - 7:01 pm UTC

only if you have the data already - it didn't sound at all like they did.

if you start with "i need to estimate the size of a table", that indicates to you that you do not have the table yet.

Alexander, September 16, 2012 - 1:07 am UTC

dbms_space.create_table_cost can be used for that right?
Tom Kyte
September 16, 2012 - 4:25 am UTC

test it out, show us what you get versus what the table actually becomes.

the fact is - a varchar2(30) might

a) almost always have less than 5 characters in your particular database
b) almost always be null in your particular database and since it is the last column in the table, takes 0 bytes
c) almost always has more than 25 characters and is never null
d) think about all of the situations


a number might

a) be almost always 1 or 2 bytes in your database - you tend to store a single digit code.
b) almost always near 22 bytes - you store high precision numbers
c) somewhere in between
d) almost always null
e) etc etc etc


the only datatypes we can "count" on would be dates/timestamps and chars, although you have the nullality issue to still deal with.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.