Results 1 to 9 of 9
Thread: Estimate index size in db2

082903, 17:22 #1Registered User
 Join Date
 Aug 2003
 Location
 hyderabad
 Posts
 6
Unanswered: Estimate index size in db2
dear guru
how to calculate index size in db2
Is ther any seperate calculation for clustered index in db2
i used the following procedure to calculate index & temp tablepspace size
For estimating index and tablespace size in db2 i used the formula

1) Average Row Size: The average row size is the sum of the average column sizes
2) For each user table in the database, the number of 4 KB pages can be estimated by calculating:
ROUND DOWN(4020/(average row size + 10)) = records_per_page
3) Then use records_per_page with:
(number_of_records/records_per_page) * 1.1 = number_of_pages
The factor of "1.1" is for overhead
4)For each index, the space needed can be estimated as:
(average index key size + 9) * number of rows * 2
5)(.9 * (U  (M*2))) * (D + 1)

K + 7 + (5 * D)
(L + 2L/(average number of keys on leaf page)) * pagesize
For DMS table spaces, add together the sizes of all indexes on a table, and round up to a multiple of the extent size for the table space on which the index resides.
i ran load for this table with 5,00,000 records with an tablespace of 30mb the results were
list tablespaces show detail
used space free space No of recordsloaded
  
13 MB 17 MB 5,00,000
it was able to insert 5,00,00 records in 13MB but index estimate calculation shows 170MB .
pl advice me on this . I had also attached an txt file with the detailed output
Thanks
thiru

082903, 17:47 #2Registered User
 Join Date
 May 2003
 Location
 USA
 Posts
 5,737
Your post is a bit confusing to me. I have no idea what the calculations in 5) represent. You also list some strange numbers like 5,00,000 and 5,00,00 which I have idea what you really meant by these.
In looking at your txt file, the following makes no sense since the numbers 32.42486191 bytes and 77.81966858 MB are not even close for index IDX_SCLM9973_CID.
There is no separate calculation for a clustering index, although the amount of freespace defined for a clustering index may be different (either more or less freespace) than the amount of freespace defined for the other indexes (assuming that the freespace was defined in a rational manner by someone who knew what they were doing).

083003, 01:19 #3Registered User
 Join Date
 Aug 2003
 Location
 hyderabad
 Posts
 6
Dear Marcus_A
Thanks for the reply . I accept that i had confused you.
i am new to db2 dba . I will be gratefull if you could advice me on how to proceed with index calculations.
Main issue is i need to insert 5 lakh records in to a table by load utility
My index calculation was done with guidence from the below reference
Ref:[http://www3.ibm.com/cgibin/db2www/...n=c0004929.htm]
the index tablespace size estimate goes to 170MB with this calculation for inserting 5 lakh records.
[ Space requirements for indexes ]
For each index, the space needed can be estimated as:
(average index key size + 9) * number of rows * 2
but practically i ran a load with 5 lakh records with index tablespace size of 30mb. it loaded succesfully. it was used only 13mb to insert all
5 lakh record.
what formula should i apply to calculate an estimated index tablespace size.
regards
thiru

083003, 03:42 #4Registered User
 Join Date
 May 2003
 Location
 USA
 Posts
 5,737
I looked up lakh in the dictionary (never heard of it before) and it appears that it represents 100,000 so I assume you have 500,000 rows in the table (and 500,000 entries in each index).
As I tried to point out, the calculation on the third index on the attached txt file is in error. Here are your calculations:
INDEX NAME ________ KEY SIZE _INDEX SIZE __ (Size In MB)
PK_SCLM9973_CTRID ___18 ____ 26999946 ____ 25.74915504
IDX_SCLM9973_CED _____4 ____ 12999974 ____ 12.39774132
IDX_SCLM9973_CID ____ 25 ___ 32.42486191 __ 77.81966858
__________________________________ TOTAL: 170 MB
I don't know where you got these numbers, but the calculations for the last index are all wrong. This is obvious if you compare them to the calculations of the first two indexes. Even if the calculation of 77.81 MB was correct (its not), the total of 170MB is also calculated incorrectly.
If you specify a separate space for indexes during the create table, then the indexes (all of them) will occupy one tablespace and the table will be in a different tablespace. Otherwise, the table and all in the indexes will occupy the same tablespace.
Using the IBM supplied algorithm for estimating index space size, the amount of space estimated may be about 1.9 the actual space used right after a load (or a reorg). IBM suggests using a factor of 2 to account for page overhead, for disorganized (fragmented) indexes, the amount of freespace defined for the index, and for growth. The actual overhead factor may be lower in many cases.
Nevertheless, your calculations appear to be way off even using the algorithms you say you are using.

083003, 07:03 #5Registered User
 Join Date
 Aug 2003
 Location
 hyderabad
 Posts
 6
Dear Marcus_A
i admit my mistakes done. i am very thankfull to you for advicing me and taking me to the best path .
correct one is
INDEX NAME ________ KEY SIZE _INDEX SIZE __ (Size In MB)
PK_SCLM9973_CTRID ___18 ____ 26999946 ____ 25.74915504
IDX_SCLM9973_CED _____4 ____ 12999974 ____ 12.39774132
IDX_SCLM9973_CID ____ 25 ___ 33999932______32.42486191
i assure there are no mistakes in the excel sheet
i had attached an excel file in "ZIP"format which containg the formula in the cells and the value got. pl go throug this and advice.
Today i was again able to load 5 lakh entries in each index using 13Mb of 30Mb tablespace size for the above columns.
used space free space
 
13 MB 17 MB (all rounded)
i found another index calculation method from this same forum
ref:http://dbforums.com/t844838.html
regards
thiru

083003, 10:25 #6Registered User
 Join Date
 Mar 2003
 Posts
 343
Also please keep in mind when you are calculating tablesize that regardless of how much space is left on a page, each page can only hold a max of 256 rows. So if a tablespace is defined with a pagesize of 16k and a fairly narrow table is placed in it, it is possible that a fair amount of space will remained unused.

021610, 09:42 #7Registered User
 Join Date
 Sep 2008
 Location
 Toronto,Canada
 Posts
 655
Your guess was right.
1 lakh = 100,000
Some people write it as lac
http://www.merriamwebster.com/dictionary/lakh
Regards

021610, 14:55 #8Super Moderator
 Join Date
 Aug 2001
 Location
 UK
 Posts
 4,650
Visit the newlook IDUG Website , register to gain access to the excellent content.

021610, 14:59 #9Registered User
 Join Date
 Sep 2008
 Location
 Toronto,Canada
 Posts
 655
Sorry,
I read the date on wrong order. I took it to be 30 march 2008