Re: Space for constraints.

Slight correction to the earlier answer - the formula listed is for attached indexes. If you put the indexes in their own dbspace, they are detached, and the formula changes to (sum of column sizes + 13) * rows * 1.25. The extra four bytes store the partition number of the table (or fragment) in which the indexed row is found. I'm assuming that the "1.25" is a general rule-of-thumb for estimating the overhead of non-leaf pages, but that method is only a rough approximation at best. Another thing that needs to be addressed when calculating index space is FILLFACTOR. The best place to look for detailed information is in the Performance Guide for Informix Dynamic Server on your documentation CD or Answers Online at the Informix web site. Of course, detached indexes assumes a ODS engine, which was not stated in the original question. If you create the table with no constraints, then create the indexes necessary to support the constraints, then alter the table to add the constraints, your indexes will be used rather than building indexes with a blank in the first character of the name. > Formula to calculate ~ bytes that an index will use: > > (sum of column sizes + 9) * rows * 1.25 > > For example, if there is a table: > > create table person > ( > person_id serial, > fname char(25), > lname char(25), > ssn char(char9) > ); > create index ix1 on person(person_id); > create index ix2 on person(ssn); > create index ix3 on (fname, lname);> > then the index space needed for 1,000,000 rows would be approximately: > > ix1: (4 +9) * 1000000 * 1.25 ----> 16,250,000 bytes or 16,250 Kb > ix2: (9+9) * 1000000 * 1.25 -----> 22,500,000 bytes or 22,500 Kb > ix3: (25+25+9) * 1000000 * 1.25 ---> 73,750,000 bytes or 73,750 Kb > > If you have put your indexes in their own dbspace, you can see how much space > they have consume (sort of) using oncheck -pe. > > John wrote: > > > Hi, > > I've two questions . > > First is : > > - how can I check the amount of disk space used by one index or constraint ? > > Second one is involved with the first: > > - when I create constraint there is created index for the constraint ; > > the first character of the name of the index is space " " ; there is no > > way for me to calculate > > disk amount needed for the index because there is no information about > > the index in sysextents table. > > The question is how to calculate the necessary disk space ? > > > > Regards JG Mark Collins mcollins@us.dhl.com Words that come to mean everything may finally mean nothing; yet their very emptiness may allow them to be filled with a mesmerizing glamour.