Results 1 to 12 of 12

Thread: find Index size

  1. #1
    Join Date
    Aug 2008
    Posts
    10

    Question Unanswered: find Index size

    how would you find size of indexes. Is there any formula for that. Highly appreciated for any hint. I am new to DB2
    Last edited by yash2400; 09-04-08 at 20:46.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I believe there are some extensive formulas you can find via searching and googlin online. I don't know what they are offhand and haven't really seen any posts on this site for them (but I may be wrong.) To be honest, I never really went through the formulas on calculating them and never found a need to even after indexing many fields in a table. I use SQL Server extensively and found it does a pretty good job of managing them.

    Is there a specific reason you want to calculate them? My guess is that if you're using MSAccess tables and need to index a lot of fields in a table, you may find the need to do this. Otherwise, again, I never really need to calculate their size even utilizing MSAccess tables with many fields indexed. I did notice that over-indexing can have a negative impact sometimes but you'll get better advice in a post by someone who has actually done the calculations.

    I always found that configuring the relational tables and "main" tables correctly (along with indexing "key" fields) and utilization of unbound forms were the biggest benefits in speed on returns if that is your concern. If speed of query returns is an issue, also keep in mind that having many relational tables in a query will give you slower returns than if you simply added the text field of certain "grouping" type fields to the main table (as well as having links to the relational tables.) This way I would not need to always link the relational tables in certain queries and indexing the "grouping" fields in the "flatter" type main table was a great speed enhancer with little impact on coding or storage. For example, although I may have a relational table linking ID's to a relational table for "XXXGrouping", adding in the actual text field for "XXXGrouping" to the main table so I wouldn't have to link the relational table in any queries, produced very fast query returns (Note: I will often just link the 2 tables together on the text field versus even having the ID linking field in the main table - I found this to be a better method versus having to utilize an ID field to link).

    I may be offbase on answering your question and the reason here but just thought I'd throw this out there if this was the concern regarding your question.
    Last edited by pkstormy; 09-04-08 at 20:57.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Aug 2008
    Posts
    10

    Size of Index

    How would you find out the size of index. Is there any formula for that ?

    what is package cache hit ratio? what does it determines?

  4. #4
    Join Date
    Aug 2008
    Posts
    10

    Re:Index size

    I was asked this question from someone. This is in DB2. The question is "How would you find index size?"

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Ok. I might come back with a question to them on "Why do you want to know?" (but that's just me.) Again though, I did once google on this topic and found some formulas online (but I was searching for it on SQL Server indexing sizes.) I never really took the time to do the actual calculations though but I believe it involved dividing something with something else and then this with that and then that with something else...and so on (it was a tad more in-depth than I wanted to actually get and someone more calculas minded might understand better.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'm sure there's probably an easy formula though and my brain made it more complicated than it actually needed to be.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    What is a "manual"? Is there any point in reading them?
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    DB2?

    This is an Access forum!

    What is DB2 anyway ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    irrespective of stroage engine the index size will the the length of the number of rows in the db/index tree * (key + length of the position/slot id in the db)

    The "slot id in the db" (my choice of language), and that will almost certainly be db dependant.. (for Access Im guessing its around 32 bits). effectivley I see this being a virtual pointer to the row in the table.

    but I've got to agree with earlier comments why would anyone NEED to know the index file size.. its totally meaningless unless you are short of space..

    If y'need the index y'need the index, unles the DBA can suggest a better mechanism for the index eg use a numeric ID as opposed to a character based name then you may need toknow the index size.. ie a 3 character string / text ID may be samller than a long integer ID..
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    DB2 is an IBM RDBMS (in case you were seriously asking ST).

    Most enterprise RDBMSs will allow you to get the size statistics for indexes. All major players create indexes as B-Tree structures - as such the size of the index is logarythmically related to the size of the underelying table. There are factors other than the data (nature, amount) that will affect the size (e.g. Fill Factor and Pad Index Options in SQL Server).

    Anwyay, it's all moot because you put a DB2 question in the Access forum and I've referred to SQL Server as my point of reference. Moving to the proper forum.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - I've also now merged threads too.

    A Moderationals work is never done
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Nov 2007
    Posts
    72
    select rtrim(substr(i.tabschema,1,8))||'.'||rtrim(substr( i.tabname,1,24)) as tabname,decimal(sum(i.nleaf)/(1024 / (b.pagesize/1024)),12,2) as indx_used_pertable from syscat.indexes i,syscat.tables t where i.tabschema is not null and i.tabname=t.tabname and i.tabschema=t.tabschema and t.tbspace=b.tbspace group by i.tabname,i.tabschema b.pagesize with ur ;

    this will tell you the index size also you can verify it against the estimate size tool availabe in db2

Posting Permissions

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