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 and Scrollable lists), but data ordering is achieved mainly by the use of indexes.
The most important index is index no.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 sub-directory and all have a large I on their file 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. 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 creation 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!
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. Here is the way the window looks when the selected field is the Symbol field (SYM) of Elements. 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. This can be clearly seen using Music which contains four subsidiary indexes. 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. Null primary keys aren't allowed but null subsidiary keys are. If the field being indexed will be null in most records you may want to index only the records in which it isn't null. If so select the button before clicking Create.
Choosing from this submenu has the following effects when the parent menu choices are as follows:
The final entry on the Indexes menu is Convert and may be used to convert indexes created with older versions of Powerbase into the new index structure first introduced in v.9.70. A description of this index structure and how it differs from the older type will be found in Appendix C.
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 can make Powerbase save the indexes at regular intervals or simply warn you to do so.
This feature was described briefly in 3.13 but a more detailed explanation is now necessary. The Query panel has a 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 the search formula 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 includes 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 the Indexes button deselected. The group 1 elements are found first and duly listed in alphabetical order (caesium to sodium). Group 2 elements (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.
None of the sample databases is big enough to show the effect of using subsidiary indexes really well but you could try loading Music, selecting the Composer and Work fields and then reporting on COMP=Mozart, with and without the Indexes button selected. Don't forget to use ADJUST, not SELECT when launching the query, so that the Match window stays open to let you see the time taken. With or without Indexes selected the report appears very quickly, but the time will be seen to be shorter with the button selected. When reporting from a database with several thousand records the speed-up can be very marked.
Indexes can not be used in the way described when:
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: