How to calculate the index average row size?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How to calculate the index average row size?

  1. #1
    Join Date
    Aug 2001
    Location
    Brazil
    Posts
    22

    Question

    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

  2. #2
    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. #3
    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".
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    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...
    Klaus Huewes

  5. #5
    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
  •  


Click Here to Expand Forum to Full Width