Re: Corrupt Indexes

In article <3lcb6n$lla@ixnews3.ix.netcom.com> Gulch@ix.netcom.com "mal" writes: > We have been having a major problem with our production system > at several of our field sites. We will intermittently experience > corrupted indexes. We will typically get an error like, > "Could not update a row in the table" (346) or > "Duplicate value for a record with unique key" (100). > > Sometimes we do a repair table and the problem is fixed. <snip> I have seen problems similar to this where the data in the columns indexes has highly skewed distributions. For example (taken from a 3rd party product we use), take a Nominal Ledger Transactions table. This is in essence a complete summary of all transactions for all companies withing the database. The crucial columns are: tran_no serial Nominal Ledger Transaction ledger char(1) 'N', 'S' or 'P' source_trans integer Source transaction if not 'N' otherwise 0 In large tables, an index on nomtrans( ledger, source_trans) will be cr*p as there will be too many entries for 'N', 0 and the index will never be built correctly *whatever* you do. Solution when it's not your product: add 'tran_no' to the end of the index with the problem e.g. nomtrans( ledger, source_trans, tran_no). Solution if it's your product (hope they read this bit! :-)): set source_tran to tran_no if ledger = 'N'. Hope this helps. diagnostic of the problem is that 'REPAIR' can't actually repair the broken index how ever many times you run it! ============================================================ Sally Woolrich | This mail contains my personal sally@excelsis.demon.co.uk | views not those of my employer! ============================================================