Re: Detached indices in 7.3

>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. > >> 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? > >> 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. Madison Pruet