How to calculate the index average row size?

[khuewes]

For example, if I have the emp table:

- oid number (PK)
- name varchar2(50)
- dept_id number (FK)
- salary number
- obs varchar2(1000)

total rows: 1000
average row length: 50 bytes

The total size of the table will be:

size = 1000 * 50 = 50000 bytes

This it's true? Thinking that the emp table have 3 indexes:

- idx_pk on the column oid
- idx_fk on the column dept_id
- idx_name on the cloumns oid and name

How can I calculate the size of each index? It's like the table size calculation?

index size = 1000 * index_average_size

I searched in the Oracle documentation of how to calculate the index_average_size, but I could not found.

Any Idea?

Thanks...

Klaus

[APylypenko]

this is how I calculate the table and the index size:

table size:

7292/avg_row_size = x
num_rows/x = y (blocks needed)
y*8192/1024/1024 = MB needed for the table

index calculation uses the same algorithm but instead of 7292 use 7870

8192 is my db_block_size
for this block size space available in the block for data will be 8102.
and 7292 is 8102 minus free space (10% in my case).


an important thing: avg_row_size for index is the summary of DECLARED columns lengths,
so your indexes avg_row_len will be

- idx_pk on the column oid ----- 8 bytes
- idx_fk on the column dept_id ---- 8bytes
- idx_name on the cloumns oid and name -----50+8=58 bytes

Actually I have a question for others:
is it correct that a number of less then 15 would need 4 bytes of memory and a number more than 15 would need 8 bytes?

[jmodic]

Originally posted by APylypenko
an important thing: avg_row_size for index is the summary of DECLARED columns lengths,

Where have you heard/read this? It certainly isn't true. AFAIK the values of variable-length type are stored in an index just the same way as they are in a table. So I would also say that your question about 4/8 bytes for numbers is irrelevant.

Besides, there is one important thing you've forgot to mention: each index entry also have coresponding ROWID stored with it in a leaf blocks. This means additional 10 bytes for each index "row".

[khuewes]

Where do you find the values 8102 for the table and the 7870 for the index?
The value 7870 is using the pct_free = 0 or 10?

In the case of the indexes, how Oracle storage the index values?

For example, the idx_pk index have the rowid + oid value, that's right?
If you use the vsize procedure, you can calculate the size for the rowid like the following command:

select round(avg(nvl(vsize(oid),0))+avg(nvl(vsize(rowid),0))) from emp;

if the result is 13 bytes, the total size of the index maybe is:

index size = 8192*(#_of_rows/(7870/13))

That's right?

Thanks...

[Shestakov]

ONLY for B+ indexes (probably for Bitmap indexes, in most cases)
and
ONLY for nocompressed indexes (in any way)

Each index key, that store in db_block may has:
1) index_key_value, ROWID or
2) index_key_value, ROWID_begin:ROWID_end

So, We can get only aproximate estimation of index size, because it deperd from:
-- # of distinct index keys,
-- distribution table rows that have some index key,
-- avg length of index key value
don't forget about index fragmentation (on another term) leaf block density.

This is reason, why Oracle don't publish method of index size calculation.
--------------------------------------------------
Common method should consist from:
1) analyze index IIIIII validate structure;
2) get detailed information from view INDEX_STAT
3) calculate number of rows per leaf block
4) calculate # of (all types) index blocks (depend from HWM)
and so on...