Creating Indexes

Indexable Field Types
Index Manipulation Commands

Indexes represent an alternative way of retrieving table rows. Normally you retrieve table rows using the row's primary key. By creating an index, you can retrieve rows with dissimilar primary key values, but which share some other characteristic.

Indexes can be created on any field which is an indexable datatype, including primary key fields. See Indexable Field Types for information on the types of fields that can be indexed.

For example, if you had a table representing types of automobiles, the primary keys for each row might be the automobile's manufacturer and model type. However, if you wanted to be able to query for all automobiles that are painted red, regardless of the manufacturer or model type, you could create an index on the table's field that contains color information.

As with tables, the best way to create indexes is to use a script. In this way, you can ensure consistency in the index(es) created across all phases of your product development cycle — from engineering, to test, to production.

Indexes can take a long time to create because Oracle NoSQL Database must examine all of the data contained in the relevant table in your store. The smaller the data contained in the table, the faster your index creation will complete. Conversely, if a table contains a lot of data, then it can take a long time to create indexes for it.

To create an index, use the plan add-index command. Similarly, to remove an index use the plan remove-index command.

To add an index, specify the table you want to index, and one or more fields to index. For example, in Defining Tables we created a table named myTable. We could add a command to the end of that script that creates an index for the percentage field, like this:

## Enter into table creation mode
table create -name myTable
## Now add the fields
add-field -type STRING -name item
add-field -type STRING -name description
add-field -type INTEGER -name count
add-field -type DOUBLE -name percentage
## A primary key must be defined for every table
## Here, we will define field 'item' as the primary key.
primary-key -field item
## Exit table creation mode
exit
## Add the table to the store. Use the -wait flag to
## force the script to wait for the plan to complete
## before doing anything else.
plan add-table -name myTable -wait

plan add-index -name percent_idx -table myTable -field percentage -wait

Indexable Field Types

Fields can be indexed only if they are declared to be one of the following types:

  • Integer

  • Long

  • Float

  • Double

  • String

  • Enum

  • Array

    In the case of arrays, the field can be indexed only if the array contains values that are of one of the other indexable types. For example, you can create an index on an array of Integers. You can also create an index on a specific record in an array of records. Only one array can participate in an index, otherwise the size of the index can grow exponentially because there is an index entry for each array entry.

Index Manipulation Commands

There are two CLI commands that you use to manipulate indexes. They are:

  • plan add-index

    Adds an index to the store. Requires a table name and one or more field names to index. This plan can take a long time to complete if there is a large amount of existing data to be indexed.

  • plan remove-index

    Removes an existing index from the store.

An exhaustive list of all CLI commands, and their syntax, can be found in KVStore Command Reference. You can also see the command syntax for all CLI commands using the CLI help command.