Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Stanley.

Asked: February 09, 2001 - 9:32 pm UTC

Last updated: June 20, 2007 - 11:16 am UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

hi tom,
can u give me a link where i can find info abt sizing tables and indexes?
I am confused abt sizing numbers, one book says to allocate 21 bytes for NUMBER.
I can see that TOAD uses the same logic.
What will be the sizing for cols having datatype like
NUMBER(5, 2) or NUMBER(3) etc rather than plain NUMBER?

thanks in advance.
Stanley

and Tom said...

I guess this is in followup to the question:

hi tom,
i have a table with the following structure.
pct free is 10
Name Null? Type

EMPNO NOT NULL NUMBER(3)
ENAME VARCHAR2(10)
MGRNO NUMBER(2)

i calculated it manually using the method shown in
oracle dba certification program guide,
the average row length was 58.
.....


Numbers are always stored in varying length strings. See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:147812348066 
for an example.  The formula is in the server concepts manual.  It is a function of the number of significant digits in the number itself.

Note: the ONLY way to really accurately size a table is to load it with a small percentage of the real data, analyze it, and multiply.  All formulas are widely inaccurate.  If I have a number(5) field but only store the numbers:

1
10
100
1000
10000
2
20
200
2000
20000
(and so on)

in it - that'll take lots less space then if I store

1
11
111
1111
11111
111111

and so on.  Block overhead, Number of columns (things get interesting over 255 columns), pctfree, how many rows we decide to put on a block and so on impact the formula in such a way as to render then unrealistic.  They are typically good for finding an absolute UPPER bound on the size of the object.  None of them are good for sizing IOTs, HASH clusters, compressed indexes, bitmapped indexes, etc etc etc.

see
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:302216525000 <code>for more details.




Rating

  (3 ratings)

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

Comments

How to calculate index size

Sujit, February 15, 2005 - 10:21 pm UTC

Hi tom,

I have been through </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1295520444744 <code>I understand that the size cannot be correctlt predicted. But we can't say the same to our clients. So i wanted to know if at all we can have a idea on what space index can take.


I have a non-partitioned table with a non-paertitioned (b-tree) index on a varchar2 column (column size is 20).

I have some 50/100 rows inserted in the table (in my test database). I want to calculate avg. index size (per row). I have tried,

select sum(bytes) size_bytes from user_extents where
segment_type = 'INDEX'
and segment_name = <INDX_NAME>;

also tried,

analyze index A_NUM_IDX validate structure;

analysed.

select lf_rows, del_lf_rows from index_stats;

Row# LF_ROWS DEL_LF_ROWS

1 51 0


How can i calculate the index size (in bytes) from the above information? Is there any other way?

I just need an estimation.


Tom Kyte
February 16, 2005 - 7:48 am UTC

<quote>
But we can't say the same to our clients.
</quote>

what you have to lie to them? I say it all of the time. I can give you the gross overestimation (probable worst case), I can give you the probable best case. Somewhere in between will be reality.

There are two cases here:

If you wrote the application (eg: you have the schema already) you should be able to tell them PRECISELY based on your own empirical knowledge.

If you are getting ready to write the application -- dbms_random.random() would help -- for I don't think you can even say at this time "we will have 50 indexes on these columns".


You need to load somewhere between 0.1 and 10% of the data. Unless your table has 1,000 rows in it in real life, 50/100 "is not enough", not nearly. And if it does have 1,000 rows in it, the index will be "tiny" and that is more than enough to know unless I'm missing something.

If you want to size based on their data, use create table as select with the sample clause and get a healthy sample of their data, index it and then you can measure.


The index estimation tools in enterprise manager/grid control apply a formula and give you a number in between "worst" and "best" cases.


Table size calculation

Dheeraj, June 15, 2007 - 6:28 am UTC

Tom,

Can you provide how to calculate the row length?
How to decide the value of the intial storage parameters?

Thanks,
Dheeraj
Tom Kyte
June 15, 2007 - 7:52 am UTC

http://asktom.oracle.com/pls/ask/search?p_string=sizing+tables


use locally managed tablespaces with auto-allocated extents and do not use STORAGE anymore.

A reader, June 20, 2007 - 9:50 am UTC

That's helpful Tom,

Though I am looing at a small document which talks about the Table Sizing...

Could you provide me with the same...that should be great.

Thanks a ton,
Dheeraj
Tom Kyte
June 20, 2007 - 11:16 am UTC

I don't need to, apparently you already have one.
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.