-
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
Klaus Huewes
-
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?
-
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".
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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...
Klaus Huewes
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|