Indexes / Repairs

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.

Re-indexing & Repairing EXACT Data

Before you start

1. Print this page, using the 'Print' button in the header of the Help.

2. Make a note of the system numbers for later checking, by looking in the Configure menu under practice Settings. Click the

   Open

   

    

   button, then write the existing numbers in the boxes below.

1. Make sure that all computers, including the one you are on, are logged out of EXACT.

2. Perform a network backup.

3. While you can sometimes rebuild just a single index to save time, often it is easier to just rebuild all indexes for a file. Depending on what index or indices your are rebuilding, the reindex process can be quite slow, and in most cases it would be best left to run overnight. If you are going to do this, make sure you disable any backups that may interfere; this is usually as simple as not putting a tape in.

To perform a Re-index

1. Start the EXACT re-indexing tool SoeAdmin. You may have an icon

   in the system tray at the bottom of the screen marked 'Server Administrator'; right-click on this, select "Administer Databases" from the menu, then follow the instructions from 2. below (all tables) or from 3. below (a single table index).
   Otherwise, from the Windows ‘Start’ menu click on ‘Run’, then 'Browse'. In the browser window, find the following directory/folder on the PC, where <drive> is the appropriate drive letter (usually C:)

   <drive>\Program Files\Software of Excellence\EXACT Server\
   
   Find and then double-click SoeAdmin.exe

   This will add the path and program name to the 'Run' dialog:

    

   Click the button.

2. A window similar to the following example:

    

3. If you are going to rebuild all indexes in a database, right-click the database, and select 'Repair Tables'.
   When the list of tables is displayed, click the 'ALL' button.
   Click the

    

   button; the selected table will be rebuilt and re-indexed.
   
   OR:

4. If you are instructed by SOE Support to reindex only a single table, click on the database name in the left pane, to expand the view, then click again on 'Tables' to show the tables, and once again on the table you need to rebuild the index for:

    

5. Right-click on the appropriate dataset and choose ‘Repair Table’ - this will fix any problems in the table as well as rebuild the indexes.

The re-index or repair should now start, and display a progress bar to indicate how far through the process it is. If you are leaving it overnight make sure that either YOU are the first one in or you leave a note so that no one uses the computers until you arrive.

After the Re-index

When the re-index has completed the dialogue box will disappear leaving only the SOE Administrator program running. Click on the X to close this program. Next, to make sure that it has completed properly, log in to EXACT and display the system numbers as described in the "Before you start" section above. Check the numbers against the ones you wrote down earlier. If they are the same then you can continue using EXACT and allow any other terminals to log in as well.

If the numbers are different then you MUST click on the Reset System Numbers button, which is immediately below the system numbers. This will correct most of the numbers although you may still have to manually correct a couple. You can correct these by simply typing the numbers you wrote down into the appropriate boxes. When you have done this you can continue using EXACT and allow others to log in if they want.

About Repairing Tables

What is repairing?

Choosing the Repair Tables option does one additional step before going on to do a rebuild indexes. It trawls through the tables and validates all the records as much as possible. If it finds errors where possible the record is repaired and if not the record is deleted. Any records that have previously been marked for deletion are also deleted at this stage. This results in a smaller data file than before.

The resulting table is written to a new file whilst it is being compiled. When it is complete the names are swapped so that the new table has the original file name and the replaced table is given a .sav file extension. There are now effectively two copies of the table. Once you have tested the repaired database and are happy, you can delete all the .sav files.

After the new table is in place the system goes on to perform a normal rebuild of the indexes.

When should you repair a table?

There are three reasons why you might want to repair a table.

• You are about to perform a version update.
  Whilst this is not normally necessary, it may become so if the update has previously been attempted and failed for whatever reason. As the version update may have cause to change the database structure it may fail if it encounters corrupt data. The repair process can eliminate this data. If this were the reason you could only rebuild the table that caused the failure, in most cases though you would repair all tables.

• You have deleted a lot of records and wish to compress the database.
  This is not really necessary. However should you need to permanently delete a record so that it cannot be recovered, or you are genuinely very low on disk space then you may wish to do this.

• You still have a problem that the rebuild indexes process has not fixed.
  On some occasions the rebuild index process may not be able to cope with a corruption of the data file and may keep producing the same wrong result. In this case a repair may be the answer to delete or correct the corruption so the rebuild process works.

Why should you repair?

You should repair a table because:

• Otherwise updates may not work correctly

• Data tables may be larger than necessary or deleted records may be recoverable.

• The rebuild index process may not work correctly.

How do you repair?

The steps are exactly as for doing a ‘Rebuild Index’ but selecting the 'Repair Table' option instead.

Logging the Rebuild and Repairs

Both the rebuild and the repair processes are logged automatically in the filesys.log file. This file is stored in the Windows directory of the workstation PC or on the server if you are running in client server mode. It is appended with all file systems warnings errors and events. When ever you perform a rebuild or a repair EXACT will log it in this file. For a single file rebuild the entry would look similar to the following,

Rebuild Started

**4/7/2000 11:04:08 :

Rebuilding SFEE

Record count before rebuild: 523

Record count after rebuild: 523

**4/7/2000 11:04:08 :

Rebuild Finished

If you think there have been any problems with the rebuild or repair then you should check here first as any abnormalities will have been noted in addition.

Where should I run Rebuilds and Repairs?

Rebuild and repairs should be run on the server. This is mainly because the process will run a lot faster with the data being accessed locally than if it has to travel across the network. It also cuts out any possibility of network errors affecting the process.

If you are running in client server mode, the rebuild and repair processes will automatically run on the server even if they are started on the workstation. You will notice that the bars that keep track of the process are a lot lumpier than if it is started directly on the server, as they are not updated so often.