Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    10

    Unanswered: Index extent sizing on IDS 9.40

    Hello,

    In the process moving from IDS 7.31 to 9.40 and have now reached the point where I need to look at my extent sizing. I have always done this using the following query.

    select substr(tabname,1,12) tab,
    count(*) num_of_exts,
    round(sum(pe_size)
    * 2 {Systems page size in KB}
    * 1.2 {Add 20% growth factor})
    proposed_ext_size, {First extent size in kb}
    round(sum(pe_size)
    * 2 {Systems page size in KB}
    * 0.2 {Estimated 20% yearly growth})
    next_ext_size {Next extent size in kb}
    from systabnames stn,
    sysptnext sti
    where
    stn.partnum = sti.pe_partnum and
    stn.tabname >= 'tcpaaa000000' and stn.tabname <= 'ttuzzz999999'
    group by 1
    having count(*) > 8
    order by 2 desc, 3 desc;

    However now when running this query I have noticed that indexs are also listed whereas under 7.31 only tables were listed.

    The query still seems to work for sizing my table extents however it doesn't now seem to be appropriate for index extent sizing.

    I found on a previous post that somebody was recommending the following formula.

    (keysize+9/rowsize) * table_ext_size

    When trying to write a query for this I have ran into the problem that I cannot find keysize in the sysmaster database.

    Can anybody help me with a query for this or advise me on any other ways of sizing index extents.

    Thanks
    Neal Matthews
    IT Support Analyst
    ATY Automotive & Industrial Components (UK) Ltd.

  2. #2
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    I'm pretty sure that you cannot directly manipulate index extent sizing for indexes. Found this to be true with IDS 7.3, 9.2, and 9.3 so this probably hasn't changed with 9.4 IMHO.

    I've asked this question at another BB & I was told that the IDS engine somehow derives the initial/next extent size numbers for indexes from the initial/next extent sizes of the table.

    After defragging a DB I have found many large tables with indexes with relatively small initial/next extent sizes for the indexes. Though they are contiguous right now & treated as one extent, they (the indexes) will obviously become fragmented quickly as soon as the DB becomes active.

    Make the initial/next extent sizes for the table(s) optimistically large (as you have allowing for 25% growth) and monitor growth.

    If there is a way of directly setting index extent sizes, someone please post a solution.

  3. #3
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    The reference to "keysize" is the space occupied by the column(s) that make up the key. For example, a composit key of EmployeeID (Integer) and CompanyCd (Char 4) would yeild a key size of 8 bytes.

    But, you don't apply an extent size to Indexes when you create them, only the table. When a CREATE INDEX is issued, the initial size request is based upon the table extent size.

    If you add records such that the key(s) are random, tweek the % fill when you create the indexes so you have less fragmentation later.
    Fred Prose

  4. #4
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    Any suggestions on the onconfig FILLFACTOR setting beyond the standard 90%?
    Last edited by mjldba; 10-22-04 at 15:05.

  5. #5
    Join Date
    Jun 2004
    Posts
    10
    Thanks for the feedback guys.

    Cheers
    Neal

Posting Permissions

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