list of chapters    previous chapter   next chapter

Ch 7 - Using extra indexes


Ch 7 - Using extra indexes

The record data is stored in a file called Database inside the database's application directory. The order of records within the Database file is determined by the order in which the records are entered. To access the records in a logical order we need one or more indexes. Powerbase does have sorting facilities for sorting reports and validation tables, but data ordering is achieved mainly by the use of indexes.

The most important index is index 0. This is always called "PrimaryKey" and is created as part of the process of setting up a database (see 4.5). You will find the file PrimaryKey inside the database directory. Other, subsidiary indexes are stored in the database's Indexes directory and all have a large I on their icon. When a database is opened any indexes in this directory will be loaded automatically. (Indexes operate in memory - to achieve maximum speed - not from disc.) Changes to the database update the indexes and closing the database (or quitting Powerbase itself) causes the updated indexes to be written back to the disc.

Note that the more indexes you use the longer it takes to add and delete records or to make alterations to records which involve changing indexes. The ideal situation might seem to be to have all fields indexed and, given a fast enough machine and a record structure with not very many fields, this might be feasible. Remember that fields which are repetitive (those which have only a few distinct values repeated throughout many records) do not index efficiently but might still be worth indexing to speed up reporting (see 7.3).

Subsidiary indexes can speed up report printing in some cases (see 3.13). When you enter a query Powerbase will see if a subsidiary index can be used with advantage and, if so, will go ahead and use it. To indicate that this is happening the number of the subsidiary index will appear in the icon at the top right of the query panel (also used to indicate when records are marked). For the speed-up to work the case button on the query panel must match the case-dependence of the index. This is a point easily overlooked!

7.1 Indexing a field

Click MENU over the required field and choose Create Index ( Ctrl J) from the Field submenu. The Key structure window will appear with the tag of the field you clicked on in the first of the four Field icons. You may use the bump icons or pop-up menus to bring the tags of the other fields into view if you wish to base the index on more than one field. Enter the data in exactly the same way as when creating the primary key index (see 4.5) and click on Create. If the field is already indexed you will be warned of this and asked if you wish to overwrite the existing index. When indexing is complete you will see that the descriptor of the indexed field has changed from black to dark blue.

If you click the "key" button on the tool-pane (Shift F4/F5) the red highlighting of the descriptor which indicates the current key moves from one indexed field to another. When you have a subsidiary key selected as the current key it works just like the primary key as regards browsing, searching and printing.

There is nothing to stop you indexing one field whilst a subsidiary index based on another field is the currently-active one, thus producing what amounts to a sort within a sort. 

One button which was shaded when creating the primary key is the option button Omit null keys. Although null primary keys aren't allowed null subsidiary keys are and the default setting for this button is therefore OFF. The field being indexed might in some cases be null most of the time and you want to index only the records in which it isn't null. If so, select the button before clicking Create.

 The Index entry on the main menu leads to a submenu offering four choices. Show details (Ctrl K) displays the structure of the currently active key. Show files (Ctrl I) opens the Indexes directory and, unless the directory is empty, has its own submenu listing the names of the files. Choosing from this menu makes the chosen index the currently-active one. Delete allows you to remove an index (after confirmation). The index file is moved from Indexes into a subdirectory called Deleted. Such an index is no longer active and will not be loaded when the database is opened. You can, however, restore it by moving it back into Indexes but it is only useful to do so if no records have been added to or removed from the database in the meantime. Rebuild displays the structure of the current index and allows you to rebuild it, with or without modification.

7.2 Automatic saving of indexes

Indexes are written to disc when you close a database or quit Powerbase but, as long as the database remains open, they are in memory and therefore could be lost if the power fails or the computer is switched off. Occasional use of the tool-pane's Update button ensures that the disc copy of all indexes is more or less up-to-date but you can automate the process if you wish. Choose Preferences from the iconbar menu, make the appropriate selection from the Save indexes section of the Preferences window and click on Accept. You may make Powerbase save the indexes at regular intervals or simply warn you to do so.

7.3 Indexes and speed of reporting

This feature was described briefly in 3.13 but the expanded application now possible in v.9.20 requires a detailed explanation. The Query panel has a new button labelled Indexes. When this button is selected Powerbase will attempt to speed up report generation by making efficient use of indexes (including the primary key index) wherever possible.

It is suggested that, if you haven‘t already done so, you perform the test with GP=T on the Elements database as described in 3.13. Here‘s what happens in that example. Powerbase recognizes that GP is indexed so, instead of going through all the records, it jumps straight to the first occurrence of "T" in the subsidiary index and prints records sequentially until the key is no longer "T". There is one small disadvantage which becomes obvious if you try something like GP=1,2,3 which is equivalent to GP=1 OR GP=2 OR GP=3. The names aren‘t all in alphabetical order, which they will be if you repeat the exercise with Indexes deselected. The group 1 elements are found first and duly listed in alphabetical order (caesium to sodium). Group 2 element (barium to strontium) then follow and, finally, group 3 elements (aluminium to thallium). You can, of course, click MENU over the list and sort the names so it‘s not much of a disadvantage, but attention is drawn to this point because there may be situations where you want to avoid it by deselecting Indexes.

Indexes can not be used in the way described when:

If any of the above situations arises Powerbase will refuse to use the speed-up methods even though Indexes remains selected. You can always tell when the feature is active because the numbers of the indexes used appear briefly in the small square to the right of the writable icon in the Query panel. If no number appears then the entire database is being scanned.

To clarify the last case (above), consider two fields, F1 and F2, both of which are indexed, the indexes being numbered 1 and 2 respectively. The search formula:

F1=wibble OR F2=wibble

will first search index 1 for all occurrences of "wibble" in F1, then search index 2 for all occurrences in F2. If F2 is not indexed Powerbase has to search the whole database for occurrences of "wibble" in that field, so the use of an index for the other field is of no help at all. No such problem would occur if the search formula were:

F1=wibble AND F2=wibble

because all records in which F2 is matched must also be ones in which F1 is matched, so index 1 would be used.

For indexes to be used as described they must meet the following criteria:

You might sometimes see 0 appear in the index-number icon, indicating that the primary key index itself is being used in this special way. This will happen if the field being targeted is a primary key field which meets the above criteria.



top of page   list of chapters    previous chapter   next chapter