DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Index size calculation

  1. #1
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    Is there a formula to calculate the approximate size of the index for a table like --

    (sum of storage bytes of the columns forming index) * number_of_rows

    I need to create an index on a large existing table and am trying to figure out the storage parameters.

    Thanks,
    - Rajeev

  2. #2
    Join Date
    Sep 2000
    Posts
    384
    select 'ALTER INDEX ' || rtrim(substr(segment_name,1,length(segment_name))) ||
    ' REBUILD TABLESPACE tablespace_name STORAGE (INITIAL ', trunc(bytes / 1024 / 1024) + 1 || 'M NEXT 1M PCTINCREASE 0) ONLINE ;'
    from dba_segments
    where extents > 5
    and segment_type = 'INDEX'
    and owner = user
    /

    the above sql will work for in 8i.For all other versions pls remove the last word online.

    after rebuliding the index do an analyze of the index.

    hope this helps.
    Radhakrishnan.M

  3. #3
    Join Date
    Jul 2000
    Location
    Oxford, OH
    Posts
    117
    There is an excellent section in Appendix A of the Oracle8 Administrator's Guide that discusses appropriately estimating space requirements for tables, indexes and clusters.

    Joe Ramsey
    _________________________
    Joe Ramsey
    Senior Database Administrator
    dbaDirect, Inc.
    (877)687-3227

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