jBASE Secondary Indexes

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 :


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.

How to Define an Index

The full description of an index definition is defined in this manual.

Calling jBC subroutines from an Index

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.

Other Considerations

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.

  Return to Contents Page   Return to Knowledge Base   Return to jBASE Home Page

http://807199.827977/r5/knowledgebase/manuals/3.0/SecondaryIndexes/html/index.htm last modified on 06/19/09 05:29 AM