# JQL Usage of Secondary Indexes

Updated: 10/19/2021, 9:29:48 PM
Created: 10/19/2021, 9:29:48 PM
Last Updated By: Kevin Munoz
Read Time: 2 minute(s)

Tags: jql select file indexing

Once an index has been created with create-index , and assuming the index data is in sync, then you can start to use the index data with special commands such as key-select or with jBC statements such as SELECTINDEX.

Another way the index data can be used is from jQL commands such as COUNT or SELECT. This occurs automatically and will dramatically speed up the operation of the jQL statements.

For example, assuming an index called DESCR is created on a file called PRODUCTS with:

jsh --> create-index PRODUCTS DESCR
1

This usage of create-index uses an existing DICT definition called DESCR and create and index name of DESCR. If now a SELECT against the file is issued as:

jsh --> SELECT PRODUCTS IF DESCR EQ "PIPE]"
1

Then in the above example the SELECT command will automatically use the index instead of the usual mechanism of searching through the entire file. Similar results may be obtained by using:

jsh --> key-select PRODUCTS DESCR PIPE]
1

Note the index could equally have been created with a command as:

jsh --> create-index PRODUCTS DESCR BY 55
1

it is just the alternative syntax for create-index was shown as this used an existing DICT definition to create the index (the command syntax is easier but less powerful)

# Note

There are some limitations to this usage of the index. If the jQL statement is too complex or the index data is not in sync, the jQL command will still work but will use the normal mechanism of searching the file rather than using any index data for improved performance.

Limited compound selection criteria is supported . For example the following first example will still use the index data whereas the second example will not, even if an index exists for both BIRTHDAY and SURNAME.

jsh --> SELECT PRODUCTS IF BIRTHDAY GE "23-JUL-56" AND BIRTHDAY LE "28-JUL-56"
1
jsh --> SELECT PRODUCTS IF BIRTHDAY GE "23-JUL-56" AND SURNAME EQ "COOPER]"
1

To be absolutely sure the index data is used to speed up a query, use the key-select command.

Back to Indexes