Re: Detached indices in 7.3

> > Even if the changes occur in the 7.3 product line, it will make no difference > > to any user because 1) the existing attached indexes will still be functional, > > 2) the total amount of space that is required for attached and detached indexes > > is identical, 3) there will be a performance benifit by using detached indexes > > because of read-ahead. > > > > Agreed, although I'll need to check 3) out just a bit. Thanks for the > info. Check item 2) also. Detached indexes actually take more space. In an attached index, each key includes a one-byte delete flag and a four-byte row location. With a detached index, you also have a four-byte tablespace identifier that points to the base table's tablespace. This can be verified by running oncheck against an attached index vs. a detached index. Samples of such onchecks are included here: Attached index Level 2 Node 28 Prev 0 Next 27 Key: 4900001: Rowids: 201 Key: 4900002: Rowids: 202 Detached index Level 2 Node 3 Prev 0 Next 2 Key: 4900001: Fragids/Rowids: 30001f/ 101 Key: 4900002: Fragids/Rowids: 30001f/ 102 Note that instead of just "Rowids", we now have "Fragids/Rowids." The fragid value points to the tablespace number of the base table. Regardless of the name "fragids", let me assure you that the table used in this case was not fragmented. This was a simple table defined in a specific dbspace, with the index defined with an "in dbspace" clause as well. For experimental purposes, I tried the "create index" directing the index into the same dbspace as the base table, and also into a separate dbspace. The oncheck results were the same in both cases. Thus, anytime you specify "in dbspace" when creating an index, even if you specify the same dbspace as for the table, the index is considered detached. As a result, you may want to modify the space calculations for estimating index pages. I know I wasn't able to calculate index space correctly until I discovered the extra four bytes. Mark Collins mcollins@us.dhl.com Everybody at some level realizes that the calendar is a fairly arbitrary thing, invented by humans, for reasons that have more to do with how committees are structured than with anything that's really happening in the heavens. And yet people look at the fact that the calendar is about to turn 2000, and assume there's some deity who thinks that the base 10 counting system is pretty darn important, and make all sorts of predictions of doom and gloom as a result. To me, that tells you everything you need to know about human beings -- and a whole lot about the market for the NC. Scott Adams, creator of _Dilbert_