As of jBASE 3.0 there exists a facility to create secondary indexes against a jBASE file. Secondary indexes are also known variously as B-TREE’s, balanced trees, binary trees et al.
The creation of one or more secondary index will have a performance impact when the file is being updated. To offset this, the time taken to select one or more records in a file can be dramatically reduced. For example, if you create a secondary index against all the surnames in a customer file, and then you take a phone call from somebody called "COOPER", you can find all the "COOPER"'s in you customer file with a statement like this :
SELECT CUSTOMERS WITH SURNAME EQ "COOPER"
The use of a secondary index can cut the search time dramatically. The exact improvement in performance depends upon many factors, such as original file size, type of query and so on. It is not uncommon for search time to be reduced from minutes down to sub-second times.
Therefore the decision to use indexes to improve performance during database searches must be considered against the degradation in performance that occurs when the database is updated.
If you have loaded jBASE indexes prior to August 1997 then you may want to look at the section entitled jBASE Secondary Indexes Change History. This provides a summary of changes you may need to be aware of.
Some general definitions about the jBASE Secondary Indexes can be located here.
The following list shows the components of jBASE secondary indexing .
| Command or jBC statement | Description |
|---|---|
| CREATE-INDEX | Used to create an index definition, or re-define an existing index definition. |
| DELETE-INDEX | This command will delete one or more currently existing index definitions and all the index data. |
| KEY-SELECT or QUERY-INDEX | Allows the application to specify a selection criteria against an existing index. |
| LIST-INDEX | Will display the currently defined index definitions for a given file. |
| REBUILD-INDEX | Rebuilds an index so long as the index definition has already been created with create-index. |
| VERIFY-INDEX | Once an index has been created, you can verify it is not corrupt and get some indexing statistics. |
| INDICES | A jBC program can use this function to determine all information about the indexes available for a given file, or extended information for a single index in a given file. |
| OPENINDEX | Used within a jBC program to open an index for later use with the extensions to the READNEXT , READPREV or SELECT statements. |
| READNEXT and READPREV | These jBC statements now have extensions to allow them to be better used with secondary indexes. |
| SELECT | This jBC statement has extensions to allow it to be better used with secondary indexes. |
| SELECTINDEX | Used within a jBC program to facilitate a fast and efficient selection of part of an index. |
The full description of an index definition is defined in this manual.
An explanation of calling jBC subroutines from an Index is given in this manual.
Warning: The jBASE index definition allows the programmer great freedom in defining the index key calculation. The index key calculation should always return the same index key at all times and must not be defined in a way that the index key generated could change over a period of time. An example would be using a T conversion that references a different file, the contents of which may change independently of the indexed file. Another example is using non-constants such as the time, date or port number in the index key calculation. All of these must be avoided otherwise your index will become invalid. Due to the flexibility given to the programmer by jBASE there is no way of detecting this, it is entirely the responsibility of the programmer.
Special considerations are required for your backup and restore strategies when using indexed files.
If you create a secondary index, then some jQL commands such as SELECT or COUNT may be able to automatically make use of them.
http://807199.827977/r5/knowledgebase/manuals/3.0/SecondaryIndexes/html/index.htm last modified on 06/19/09 05:29 AM