Re: Detached indices in 7.3

SaTriGuy wrote: > > >SaTriGuy wrote: > > . . . snip . . . > > > >> 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. > >> > > > >Makes sense. > > > >> 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. > >> > > > >I thought that Informix didn't skip pages; hence, the ixda-RA portion of > >onstat -p.> > Not quite... The main benifit of read-aheads are for data pages, not index > pages. The RA scan has to read (and ignore) the index pages. Index > read-aheads are used primarily when index scans are being used. Since the > logical address of index pages will rarely match the physical address, the > advantage of RA on index pages really does not provide that much benifit. They > must be broken into several "scatter/gather" io's. RA for data pages do not > have to be broken into multiple IO's. > That makes more sense; I thought that Informix RA also included index pages that happened to be mixed in with data pages. What was I thinking? If OnLine is doing an indexed read, why would it need the 'extra' index pages from a read-ahead. > > > >> 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. > >> > > > >Other than extents, yes. > > ??? I don't understand. An extent is simply a collection of pages. Since the > total number of pages should not increase what do you mean by extents > increasing the total amount of space used? > I'm sorry; it has nothing to do with the total amount of pages used. My concern is the possible fragmentation of data and index extents within a dbspace populated by other data / index extents. There could be a lot of interleaving <sp?> within a dbspace depending on the growth of the tables. When all pages are in the same tablespace, it seems easier to allocate space for growth. > > > >> 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. > > > >So then any changes would be in a much-later release? That makes more > >sense. > > 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. John Carlson Informix DBA WH Smith, Inc.