About Rebuilding Indexes

What is an Index?

EXACT data is stored in tables. Tables are like long lists with different columns of information; at the top of each column is a heading to say what is stored there. Each line in the table is called a record; each entry on a line is called a field. When you add another record the table gets longer. As you can imagine some of the tables in EXACT get very long indeed.

When you ask the computer to show you a particular record it has to look down the table reading every entry to find the record that you asked for. The longer the table, the longer the record takes to find. Indexes get around this problem by telling the computer exactly where in the table to look.

For every table of information in EXACT there is a corresponding index file, which tells the computer where to find the records in that table. Sometimes an index file may contain more than one index, this lets you look up the record in different ways. The patient file for example, contains many indexes allowing you to look up patients by surname, date of birth and phone number amongst others.

What is rebuilding an Index?

Indexes are very important to a database. If the index becomes damaged and can no longer tell the computer where to find a record, then the computer may not be able to display that record, even though the record still exists in the main table. The rebuilding process goes through the table and recreates the index from scratch. If a table needs more than one index it will be traversed once for each index. Because the indexes are completely recreated by the rebuild process they are completely disposable.

Every time a new record is added or an existing record amended the corresponding indexes will need to be adjusted to reflect this. Thus you can see that one change to a data table can result in many writes to the index files. It is because of this that index files tend to corrupt more easily than table.

Using the client/server model of the database can significantly reduce all types of corruption to the files. The majority of corruption occurs due to network problems, either network cards sending faulty data or data being garbled by interference in the wiring. The client server mode eliminates this by having an active process on the server that ‘vets’ all incoming data and only writes to the table, data that it approves of.

When should you rebuild indexes?

There are three occasions when you should perform a rebuild.

• The software tells you to.
  Whilst carrying out its normal functions EXACT may notice that a branch of the index tree goes nowhere, or to an illegal place. It will report to the screen that it has found a broken index and give the name and number. In these cases you may only need to rebuild the one index file mentioned in the error.
• You notice that a report seems incomplete, values are to low or entries between certain dates are missing. You may also notice that records are unavailable or parts of a record are missing.
  In this case the software has not detected that the branch is broken. The corruption has left the index tree looking like it terminates normally. As there are no pointers as to precisely which index is broken, in this case you should rebuild all indexes.
• The software crashes, freezes or errors when performing a function that it would normally be able to do.
  In this case again, the software has not detected the corruption and the index tree appears normal but is in fact pointing to some data which the program is not designed to deal with at that point. As before, in this case you should rebuild all the index files.

Why should you rebuild indexes?

You should rebuild an index file because:

• Otherwise Reports may be inaccurate, or missing off sections of files because they are inaccessible due to a broken index.
• Not all records may be available.
• The system may behave erratically.
  

To rebuild indexes

1. Ensure that all practitioners exit from EXACT.
2. Backup what you have now. Even if it is not working completely at least it is working partially.

   Why is it necessary to do this?
   Once you start a rebuild you will not be able to use the system until it is complete. The only way around this is to restore a backup.

3. Carry out the rebuild using the SoeAdmin.exe program.
4. Log in and check the data set and the system numbers.
5. Retry the condition that made the rebuild necessary.