Re: Detached indices in 7.3
>> How that ? The detached index would automatically land in the >> same dbspace (fragments) as the table, same as fragmented tables in >> >=7.0 already behave. >> > >This is true; there would be no difference between current 7.2x and >7.3. But when your database has 400+ tables with associated indices, >then administration could be fun. Data and index extents for a table >would get to play leapfrog with other data and index extents for other >tables within the same dbspace. > >I'm just wondering if they (detached indices) are required under 7.3x. > > >> > Informix 7.23.UC4 >> > HPUX 10.01 >> > >> > Just got off the phone with my third-party (financial) software vendor. >> > The script that creates their table structure automatically detached all >> > indices created (within the same dbspace as the data). After >> > researching the "why's" with Informix (<?>), they came to the conclusion >> > that this would not be a maintenance problem or a performance issue. >> > Besides, they said, Informix 7.3 will not support attached indices. >> > >> > While I like the flexibility of detaching indices and fragmenting data, >> > forcing all indices to be detached could create an administrative >> > nightmare. Is this particular rumor true? >> > >> > John Carlson >> > Informix DBA >> > WH Smith, Inc. > > >John Carlson In the 4.x and 5.x days, the concept of including the index pages within the same tablespace as the datapages was a performance argument. The theory was that if the disk head was close to the data then there would be little head motion between the reading of the index pages and the actual data page. Nice theory but not factual in multi-use environments because by the time the index had been read, some other user would have already moved the head. By making the indexes detached we are able to maximize read-aheads. This means that when we are reading datapages, we don't have to skip over index pages, simply read. This minimizes disk head motion during scan phases and maximizes the useful data read by sequential reads. As far as this approach taking more space, - nope right the opposite. The data extent should contain the same number of data pages as before and the index extent should contain the index pages. There should be no difference in the total number of pages used. From a maintenance standpoint, attached indexes are still functional. I just ran a quick test on 7.3 and my index was still attached. If this behaves like 9.x, then attached indexes can still be used, so the user does not have to drop and recreate their indexes. However with 9.x, any new indexes will be detached, even though they are in the same dbspace.>> How that ? The detached index would automatically land in the >> same dbspace (fragments) as the table, same as fragmented tables in >> >=7.0 already behave. >> > >This is true; there would be no difference between current 7.2x and >7.3. But when your database has 400+ tables with associated indices, >then administration could be fun. Data and index extents for a table >would get to play leapfrog with other data and index extents for other >tables within the same dbspace. > >I'm just wondering if they (detached indices) are required under 7.3x. > > >> > Informix 7.23.UC4 >> > HPUX 10.01 >> > >> > Just got off the phone with my third-party (financial) software vendor. >> > The script that creates their table structure automatically detached all >> > indices created (within the same dbspace as the data). After >> > researching the "why's" with Informix (<?>), they came to the conclusion >> > that this would not be a maintenance problem or a performance issue. >> > Besides, they said, Informix 7.3 will not support attached indices. >> > >> > While I like the flexibility of detaching indices and fragmenting data, >> > forcing all indices to be detached could create an administrative >> > nightmare. Is this particular rumor true? >> > >> > John Carlson >> > Informix DBA >> > WH Smith, Inc. > > >John Carlson Madison Pruet