Creating Indexes

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.

You can create indexes on any field that has a data type capable of indexing, including primary key fields. You can index table IDENTITY fields. For information on the types of fields that can be indexed, see Indexable Field Types.

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 red automobiles, regardless of the manufacturer or model type, you could create an index on the field containing color information.

Note:

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 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.

CREATE TABLE myInventory.itemDetails (
    itemSKU STRING,
    itemDescription STRING,
    price FLOAT,
    inventoryCount INTEGER,
    PRIMARY KEY (itemSKU)
) 

To create an index, use the CREATE INDEX statement. See CREATE INDEX. For example:

CREATE INDEX inventoryIdx on myInventory.itemDetails(inventoryCount)

Similarly, to remove an index, use the DROP INDEX statement. See DROP INDEX. For example:

DROP INDEX inventoryIdx on myInventory.itemDetails

Be aware that adding and dropping indexes can be time consuming. You may want to run drop index operations asynchronously using the KVStore.execute() method.

package kvstore.basicExample;

import java.util.concurrent.ExecutionException;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.TimeoutException;

import oracle.kv.ExecutionFuture;
import oracle.kv.FaultException;
import oracle.kv.StatementResult;
import oracle.kv.KVStore;
import oracle.kv.KVStoreConfig;
import oracle.kv.KVStoreFactory;
import oracle.kv.table.TableAPI;

...
// Store open skipped
... 

public void createIndex() {
    TableAPI tableAPI = store.getTableAPI();
    ExecutionFuture future = null;
    StatementResult result = null;
    String statement = null;

    try {

        statement = "CREATE INDEX inventoryIdx on " +
                    "myInventory.itemDetails(inventoryCount)"
        future = store.execute(statement);
        displayResult(future.getLastStatus(), statement);

        /*
         * Limit the amount of time to wait for the
         * operation to finish.
         */
        result = future.get(3, TimeUnit.SECONDS);
        displayResult(result, statement);

    } catch (IllegalArgumentException e) {
        System.out.println("Invalid statement:\n" + e.getMessage());
    } catch (FaultException e) {
        System.out.println
            ("Statement couldn't be executed, please retry: " + e);
        cleanupOperation(future);
    } catch (ExecutionException e) {
        System.out.println
            ("Problem detected while waiting for a DDL statement: " +
             e.getCause());
        cleanupOperation(future);
    } catch (InterruptedException e) {
        System.out.println
            ("Interrupted while waiting for a DDL statement: " + e);
        cleanupOperation(future);
    } catch (TimeoutException e) {
        System.out.println("Statement execution took too long: " + e);
        cleanupOperation(future);
    }
}

private void cleanupOperation(ExecutionFuture future) {
    if (future == null) {
        /* nothing to do */
        return;
    }

    System.out.println("Statement:");
    System.out.println(future.getStatement());
    System.out.println("has status: ");
    System.out.println(future.getLastStatus());

    if (!future.isDone()) {
        future.cancel(true);
        System.out.println("Statement is cancelled");
    }
}

private void displayResult(StatementResult result, String statement) {
    System.out.println("===========================");
    if (result.isSuccessful()) {
        System.out.println("Statement was successful:\n\t" + 
                            statement);
        System.out.println("Results:\n\t" + result.getInfo());
    } else if (result.isCancelled()) {
        System.out.println("Statement was cancelled:\n\t" + 
                            statement);
    } else {
        /*
         * statement wasn't successful: may be in error, or may still be
         * in progress.
         */
        if (result.isDone()) {
            System.out.println("Statement failed:\n\t" + statement);
            System.out.println("Problem:\n\t" + result.getErrorMessage());
        } else {
            System.out.println("Statement in progress:\n\t" + statement);
            System.out.println("Status:\n\t" + result.getInfo());
        }
    }
} 

For examples of how to index supported non-scalar types, see Indexing Non-Scalar Data Types.