Re: OnLine 7.12: Fragment Elimination Problem

In article <00001a16+0000295b@msn.com>, Terry Gauchat <tgauch@msn.com> writes >Any ideas, folks, on the following problem? > >... > >We created a fragmented version of the t_acct_stnd table in the monthend >database, for the purpose of facilitating the updating of the t_acct_sum >table. The intent was to allow us to run 6 update processes >concurrently, and >to avoid contention for disk access among these six processes. To >that end, I >wrote the 4gl program that does the updates so that each one takes an >integer >[arg] between 1 and 5 as an argument, and processes those records where > mod(round(acct_id/10),6) = [ arg]. > >The fragmentation of the t_acct_stnd follows the same formula, i.e., > mod(round(acct_id/10),6) = 0 in dbspace1 > mod(round(acct_id/10),6) = 1 in dbspace2 > mod(round(acct_id/10),6) = 2 in dbspace3 > mod(round(acct_id/10),6) = 3 in dbspace4 > mod(round(acct_id/10),6) = 4 in dbspace5 > mod(round(acct_id/10),6) = 5 in dbspace6 >so that when any of the 4gl programs doing the updates to t_acct_sum searches >for the data pertaining to a particular account, it can eliminate 5 of the >fragments from its search, and so that each of the programs should always be >searching a different disk for its data. > >What we found in practise was that the optimizer would not eliminate >fragments >for any query for acct_id under any circumstances. Specifically, we used set >explain onfor various permutations of the basic query run by the 4gl >program, (select collection _status from t_acct_stnd where >acct_id = ? >and credit_month = ?) > >We ran this query with and without the credit_month criterion. We >ran it with >and without selecting the collection status. We ran it with a fragmented >composite index on acct_id and credit_month, and we ran it after >having dropped >that index. We ran it with an index on the acct_id, and without the index on >acct_id. > >In each of these instances we used the output of set explain on and >the output >from a query of the sysmaster database (returning the number of >datapages read >from and written to for each chunk), to verify that the optimizer was not >eliminating any fragments from the search. Paradoxically, after we had >eliminated all the indices, we found that the query appeared to eliminate the >fragment that the row was actually in from its search, even though it >returned >the appropriate record. > > >Thanks. > >...Terry. >tgauch@aol.com I believe that Informix does not do fragment elimination when it uses an index - all it has to do is use the index to get to the data - fragmnent elimination only occurs when indicies are not used. The idea is that if you have to scan the table for data you can scan only one fragment if you know the data can only exist in one fragment. Aso when data is inserted you can force the insertion to occur into one fragment so if the data row ( or index for fragmented indicies) has to be read from the disk you can force the disk I/O to occur from a particular disk i.e. balance disk I/O across disks by better controlling the layout of data/indicies. Can anyone confirm this? -- David Williams