Library Version 12.1.3.0

Reading Indexes
Prev | Chapter 7. Reading Table Rows | Next

## Reading Indexes

You use `TableIterator` to retrieve table rows using a table's indexes. Just as when you use `TableIterator` to read table rows using a table's primary key(s), when reading using indexes you can set options such as field ranges, traversal direction, and so forth. By default, index scans return entries in order (`Direction.FORWARD`).

In this case, rather than provide `TableIterator` with a `PrimaryKey` instance, you use an instance of `IndexKey`.

For example, suppose you defined a table like this:

    ## Enter into table creation mode
    table create -name myTable
    ## Now add the fields
    add-field -type STRING -name surname
    add-field -type STRING -name familiarName
    add-field -type STRING -name userID
    add-field -type STRING -name phonenumber
    add-field -type STRING -name address
    add-field -type STRING -name email
    add-field -type STRING -name dateOfBirth
    primary-key -field surname -field familiarName -field userID
    shard-key -field surname -field familiarName
    ## Exit table creation mode
    exit
    plan add-table -name myTable -wait
    plan add-index -name DoB -table myTable -field dateOfBirth -wait

This creates an index named `DoB` for table `myTable` based on the value of the `dateOfBirth` field. To read using that index, you use `Table.getIndex()` to retrieve the index named `Dob`. You then create an `IndexKey` from the `Index` object. For example:

    package kvstore.basicExample;

    ...

    import oracle.kv.KVStore;
    import oracle.kv.table.Index;
    import oracle.kv.table.IndexKey;
    import oracle.kv.table.Row;
    import oracle.kv.table.Table;
    import oracle.kv.table.TableAPI;
    import oracle.kv.table.TableIterator;

    ...

    // KVStore handle creation is omitted for brevity

    ...

    TableAPI tableH = kvstore.getTableAPI();

    Table myTable = tableH.getTable("myTable");

    // Construct the IndexKey. The name we gave our index when
    // we created it was 'DoB'.
    Index dobIdx = myTable.getIndex("DoB");
    IndexKey dobIdxKey = dobIdx.createIndexKey();

    // Exception handling is omitted, but in production code
    // ConsistencyException, RequestTimeException, and FaultException
    // would have to be handled.
    TableIterator<Row> iter = tableH.tableIterator(dobIdxKey, null, null);
    try {
        while (iter.hasNext()) {
            Row row = iter.next();
            // Examine your row's fields here
        }
    } finally {
        if (iter != null) {
            iter.close();
        }
    }

In the previous example, the code examines every row indexed by the `DoB` index. A more likely, and useful, example in this case would be to limit the rows returned through the use of a field range. You do that by using `Index.createFieldRange()` to create a `FieldRange` object. At that time, you must specify the field to base the range on. Recall that an index can be based on more than one table field, so the field name you give the method must be one of the indexed fields.

For example, if the rows hold dates in the form of `yyyy-mm-dd`, you could retrieve all the people born in the month of May, 1994 in the following way. This index only examines one field, `dateOfBirth`, so we give that field name to `Index.createFieldRange()`:

    package kvstore.basicExample;

    ...

    import oracle.kv.KVStore;
    import oracle.kv.table.FieldRange;
    import oracle.kv.table.Index;
    import oracle.kv.table.IndexKey;
    import oracle.kv.table.MultiRowOption;
    import oracle.kv.table.Row;
    import oracle.kv.table.Table;
    import oracle.kv.table.TableAPI;
    import oracle.kv.table.TableIterator;

    ...

    // KVStore handle creation is omitted for brevity

    ...

    TableAPI tableH = kvstore.getTableAPI();

    Table myTable = tableH.getTable("myTable");

    // Construct the IndexKey. The name we gave our index when
    // we created it was 'DoB'.
    Index dobIdx = myTable.getIndex("DoB");
    IndexKey dobIdxKey = dobIdx.createIndexKey();

    // Create the field range.
    FieldRange fh = dobIdx.createFieldRange("dateOfBirth");
    fh.setStart("1994-05-01", true);
    fh.setEnd("1994-05-30", true);
    MultiRowOptions mro = fh.createMultiRowOptions();

    // Exception handling is omitted, but in production code
    // ConsistencyException, RequestTimeException, and FaultException
    // would have to be handled.
    TableIterator<Row> iter = tableH.tableIterator(dobIdxKey, mro, null);
    try {
        while (iter.hasNext()) {
            Row row = iter.next();
            // Examine your row's fields here
        }
    } finally {
        if (iter != null) {
            iter.close();
        }
    }

Prev | Up | Next
Using MultiRowOptions to Retrieve Nested Tables | Home | Parallel Scans