Rebuilding Indexes

Gordon French of Fort Lauderdale recently asked, with reference to the INDEX.SUB program included on Utility Diskette Number 1, "How is it possible to force a rebuild of an index for a file?". The INDEX.SUB routine does not actually force a rebuild as it assumes that you will be rebuilding after adding a new index, in which case it will work perfectly. If however, it is suspected that the indexes have become corrupted it can be long-winded calling INDEX.SUB to remove, readd then rebuild the indexes.

It is possible to instruct the system to rebuild the index for a field by mimicking the action taken by the system when it adds an index to a field. The system simply writes a record to the !INDEXING file, containing information about the file to update and the keyword ALL.IDS. The listing below shows a generic routine designed to allow the rebuilding of indexes from within an RBASIC program. To use, call with the file name and the field name/index name (EG USER, COMPANY_NAME.XREF, CUST*INV*AR). Note, network users should ensure they incorporate locking logic into this subroutine.

     SUBROUTINE REBUILD_INDEX(FILE,FIELD)
     *
     *  Author   AMcA
     *  Date     Jan 91
     *  Purpose  To rebuild an index for a field
     *  COPYRIGHT Sprezzatura Ltd 1991. Permission given to
     *  REVMEDIA subscribers to use in any form.

      DECLARE SUBROUTINE FSMSG

      OPEN "!INDEXING" TO IFILE THEN
       GOSUB GET_VOL_INFO
       GOSUB UPDATE_INDEX_FILE
      END ELSE
       FSMSG()
      END
     RETURN

     GET_VOL_INFO:
      FILE_REC = XLATE("FILES",FILE,"","X")
      VOL_REC  = XLATE("VOLUMES",FILE_REC<1>,"","X")
      VOL_INFO = FILE_REC<2>:"*":FILE_REC<3>:"*":VOL_REC<1>
     RETURN

     UPDATE_INDEX_FILE:
      UPDATE_REC    = VOL_INFO      ; * Volume Information
      UPDATE_REC<2> = 1             ; * Number of transactions
      UPDATE_REC<3> = FIELD         ; * Index to update
      UPDATE_REC<4> = "%%ALL.IDS%%" ; * Rebuild Flag
      UPDATE_REC   := @FM : @FM : @FM
      READ ZERO_REC FROM IFILE, "0" ELSE ZERO_REC = @FM
      * Ensure there is a trailing field mark
      IF INDEX(ZERO_REC,@FM,1) ELSE ZERO_REC := @FM
      ZERO_REC := UPDATE_REC
      WRITE ZERO_REC TO IFILE, "0"
      CALL INDEX.FLUSH(VOL_INFO,'')
     RETURN

(Volume 2, Issue 8, Page 7)