how would you find size of indexes. Is there any formula for that. Highly appreciated for any hint. I am new to DB2

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.

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?

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

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.)

I'm sure there's probably an easy formula though and my brain made it more complicated than it actually needed to be.

What is a "manual"? Is there any point in reading them?

DB2?

This is an Access forum!

What is DB2 anyway ^^

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..

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.

pootle flump ur codings are working excelent.

Ok - I've also now merged threads too.

A Moderationals work is never done

pootle flump ur codings are working excelent.

select rtrim(substr(i.tabschema,1,8))||'.'||rtrim(substr( i.tabname,1,24)) as tabname,decimal(sum(i****eaf)/(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