RE: 9.2 indexes bigger

So that means that all of us Lawson customers ('index-heavy') will need to reorg after upgrading due to the large 'first extent' figure that is now not needed? And I thought that this would be relatively painless . . . . . ouch!! John Carlson Informix DBA EDS - WHSmith USA 3200 Windy Hill Road, Suite 1500 West Atlanta, GA 30330 > -----Original Message----- > From: Art S. Kagel [mailto:kagel@bloomberg.net] > Sent: Monday, September 11, 2000 12:22 PM > To: informix-list@iiug.org > Subject: Re: 9.2 indexes bigger > > > Doug McAllister wrote: > > > > Hi all... > > > > I just received a message from one of my third party > application vendors > > telling me that his 9.2 databases were much bigger than his 7.23 db > > because: > > A: The indexes are now detached and in their own tblspace - and > > B: Each index entry needs an additional 8 bytes over the > old indexes. > > and: > > C: The extent sizing from the original database included > space for the > indexes which need not be allowed for in 9.2. > > Yes. > > Art S. Kagel > > > Can anyone confirm or deny this? > > > > tnx.... >

John Carlson wrote: > So that means that all of us Lawson customers ('index-heavy') will need to > reorg after upgrading due to the large 'first extent' figure that is now not > needed? Hi, you don't have to reorg. The 9.2 engine still supports attached indexes, so the layout of existing tables remains unchanged. Only for new tables indexes will be created detached by default. Also, there is an environment variable to switch the default behavior from detached to attached indexes if really needed. Hope this helps, Heiko > > > And I thought that this would be relatively painless . . . . . ouch!! > > John Carlson > Informix DBA > EDS - WHSmith USA > 3200 Windy Hill Road, Suite 1500 West > Atlanta, GA 30330 > > > -----Original Message----- > > From: Art S. Kagel [mailto:kagel@bloomberg.net] > > Sent: Monday, September 11, 2000 12:22 PM > > To: informix-list@iiug.org > > Subject: Re: 9.2 indexes bigger > > > > > > Doug McAllister wrote: > > > > > > Hi all... > > > > > > I just received a message from one of my third party > > application vendors > > > telling me that his 9.2 databases were much bigger than his 7.23 db > > > because: > > > A: The indexes are now detached and in their own tblspace - and > > > B: Each index entry needs an additional 8 bytes over the > > old indexes. > > > > and: > > > > C: The extent sizing from the original database included > > space for the > > indexes which need not be allowed for in 9.2. > > > > Yes. > > > > Art S. Kagel > > > > > Can anyone confirm or deny this? > > > > > > tnx.... > >

Could you tell us what that environment variable is please? Heiko Giesselmann wrote: > John Carlson wrote: > > > So that means that all of us Lawson customers ('index-heavy') will need to > > reorg after upgrading due to the large 'first extent' figure that is now not > > needed? > > Hi, > > you don't have to reorg. The 9.2 engine still supports attached indexes, so the > layout of existing tables remains unchanged. Only for new tables indexes will be > created detached by default. Also, there is an environment variable to switch > the default behavior from detached to attached indexes if really needed. > > Hope this helps, Heiko > > > > > > > And I thought that this would be relatively painless . . . . . ouch!! > > > > John Carlson > > Informix DBA > > EDS - WHSmith USA > > 3200 Windy Hill Road, Suite 1500 West > > Atlanta, GA 30330 > > > > > -----Original Message----- > > > From: Art S. Kagel [mailto:kagel@bloomberg.net] > > > Sent: Monday, September 11, 2000 12:22 PM > > > To: informix-list@iiug.org > > > Subject: Re: 9.2 indexes bigger > > > > > > > > > Doug McAllister wrote: > > > > > > > > Hi all... > > > > > > > > I just received a message from one of my third party > > > application vendors > > > > telling me that his 9.2 databases were much bigger than his 7.23 db > > > > because: > > > > A: The indexes are now detached and in their own tblspace - and > > > > B: Each index entry needs an additional 8 bytes over the > > > old indexes. > > > > > > and: > > > > > > C: The extent sizing from the original database included > > > space for the > > > indexes which need not be allowed for in 9.2. > > > > > > Yes. > > > > > > Art S. Kagel > > > > > > > Can anyone confirm or deny this? > > > > > > > > tnx.... > > >

Any idea why there is an extra 8 byte overhead for each index entry? Heiko Giesselmann wrote: > John Carlson wrote: > > > So that means that all of us Lawson customers ('index-heavy') will need to > > reorg after upgrading due to the large 'first extent' figure that is now not > > needed? > > Hi, > > you don't have to reorg. The 9.2 engine still supports attached indexes, so the > layout of existing tables remains unchanged. Only for new tables indexes will be > created detached by default. Also, there is an environment variable to switch > the default behavior from detached to attached indexes if really needed. > > Hope this helps, Heiko > > > > > > > And I thought that this would be relatively painless . . . . . ouch!! > > > > John Carlson > > Informix DBA > > EDS - WHSmith USA > > 3200 Windy Hill Road, Suite 1500 West > > Atlanta, GA 30330 > > > > > -----Original Message----- > > > From: Art S. Kagel [mailto:kagel@bloomberg.net] > > > Sent: Monday, September 11, 2000 12:22 PM > > > To: informix-list@iiug.org > > > Subject: Re: 9.2 indexes bigger > > > > > > > > > Doug McAllister wrote: > > > > > > > > Hi all... > > > > > > > > I just received a message from one of my third party > > > application vendors > > > > telling me that his 9.2 databases were much bigger than his 7.23 db > > > > because: > > > > A: The indexes are now detached and in their own tblspace - and > > > > B: Each index entry needs an additional 8 bytes over the > > > old indexes. > > > > > > and: > > > > > > C: The extent sizing from the original database included > > > space for the > > > indexes which need not be allowed for in 9.2. > > > > > > Yes. > > > > > > Art S. Kagel > > > > > > > Can anyone confirm or deny this? > > > > > > > > tnx.... > > >

Well, if they are detached indexes, then the index record for the leaf has to point to the tblspace page for the data as well as the logical row number. That accounts for 4 more bytes. The other four elude me. HTH, Doug "Doug McAllister" <doug.mcallister@nospam.fmr.com> wrote in message news:39BE6CF5.81C7268@nospam.fmr.com... > Any idea why there is an extra 8 byte overhead for each index entry? > > Heiko Giesselmann wrote: > > > John Carlson wrote: > > > > > So that means that all of us Lawson customers ('index-heavy') will need to > > > reorg after upgrading due to the large 'first extent' figure that is now not > > > needed? > > > > Hi, > > > > you don't have to reorg. The 9.2 engine still supports attached indexes, so the > > layout of existing tables remains unchanged. Only for new tables indexes will be > > created detached by default. Also, there is an environment variable to switch > > the default behavior from detached to attached indexes if really needed. > > > > Hope this helps, Heiko > > > > > > > > > > > And I thought that this would be relatively painless . . . . . ouch!! > > > > > > John Carlson > > > Informix DBA > > > EDS - WHSmith USA > > > 3200 Windy Hill Road, Suite 1500 West > > > Atlanta, GA 30330 > > > > > > > -----Original Message----- > > > > From: Art S. Kagel [mailto:kagel@bloomberg.net] > > > > Sent: Monday, September 11, 2000 12:22 PM > > > > To: informix-list@iiug.org > > > > Subject: Re: 9.2 indexes bigger > > > > > > > > > > > > Doug McAllister wrote: > > > > > > > > > > Hi all... > > > > > > > > > > I just received a message from one of my third party > > > > application vendors > > > > > telling me that his 9.2 databases were much bigger than his 7.23 db > > > > > because: > > > > > A: The indexes are now detached and in their own tblspace - and > > > > > B: Each index entry needs an additional 8 bytes over the > > > > old indexes. > > > > > > > > and: > > > > > > > > C: The extent sizing from the original database included > > > > space for the > > > > indexes which need not be allowed for in 9.2. > > > > > > > > Yes. > > > > > > > > Art S. Kagel > > > > > > > > > Can anyone confirm or deny this? > > > > > > > > > > tnx.... > > > > >