# Thread: How to calculate the index average row size?

1. Junior Member
Join Date
Aug 2001
Location
Brazil
Posts
22
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

2. Junior Member
Join Date
Jun 2001
Posts
15
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?

3. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439
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".

4. Junior Member
Join Date
Aug 2001
Location
Brazil
Posts
22
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...

Join Date
Sep 2001
Location
NJ, USA
Posts
1,287
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...

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•