Giving UniVerse Secondary Indexes a Second Chance

Type 25 (B-tree) Files

Secondary Indexes are implemented using Type 25 (B-tree) Files. The records in a type 25 file are stored in sorted order, not hashed like Types 2-18 and 30. These files can be read or written just like others, or they can be browsed forwards and backwards, and they are extremely efficient.  The files are implemented using a structure called a B-tree.  There are a number of theories about what the B in B-tree stands for – I’ve heard Binary and Boeing, but the UniVerse documentation uses the term Balanced.  This balancing and managing concurrent updates and reads adds a great deal of complexity to the B-tree file structure.  We pay a penalty in terms of disk space – 300% overhead seems to be a minimum – but we get a reliable index that's built in to the database engine.  The indexes are always in synch with the data.   Note that there were issues with Type 25 files before release 9.6, but the product is considered very stable currently.

The CREATE.INDEX command

When we issue a create.index command, UniVerse creates an index directory (I_filename) containing an index map file and one file for each index.  It imbeds the path to this index directory in the header of the original data file; this ensures that F-pointers to this file will inherit all the indexes.   Let's look at a session:

Just issuing the create.index command doesn't make the index useable.  When we add a require.index clause to our select, it fails.

If we shell out to the operating system level and look at the newly created I_STUDENT directory we see two files: INDEX.000 – the type 25 file that will contain the index; and INDEX.MAP – a special file containing information on all the indexes associated with the file STUDENT.

This information is summarized in the list.index command output.  Note that the Index Updates status is "Enabled" – this is misleading, changes to the data file are not reflected  in the index until it is built.

The set.index command is used to display or update the index path in the file header.

The BUILD.INDEX command

The build.index command locks the data file, selects it, and processes the records one at a time building the index.  If you try to set an item lock on the data file while the build is underway, you'll get an error.  Let's look at a session:

TEACHER.ID       D    Not Reqd  Yes    Yes      S    L     N    1

The build.index command can take hours or days for large files with complex index keys.  During this time the data file has read-only access.  There may be a way around this using temporary files and the set.index command, discussed later.

SELECTing Indexes

The drawback to using an index instead of an explicit list of detail items in the header record is that the two traditional techniques used to get a list of detail keys for a given header item are not "header centric."  They involve a select against the detail file. In order to access the list of detail keys, there are two documented methods:

We could wrap either one of these selects in a subroutine called from an I-descriptor:

These are the traditional ways to deal with Secondary Files programmatically.  Apart from the complexity, there exists a danger that someone else will be using the select list you’ve chosen.  It is especially important when we’re using an I-descriptor; who knows what select lists will be active when this I-descriptor is eventually used.

Accessing Index Files Directly

We can create a file pointer to the actual type 25 file containing the index.  This file will be located in a directory I_filename (in this case I_STUDENT) in the UniVerse account where the index was created.  The easiest way to do this is to do a CREATE.FILE DICT STUDENT_TEACHER.ID and edit the new VOC item to replace attribute 2 with I_STUDENT/INDEX.000. This is documented in the UniVerse File Structures chapter of the UniVerse System Description manual.