Previous Next JavaScript must be enabled to correctly display this content

## 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 forward order.

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:

    CREATE TABLE myTable (
        surname STRING,
        familiarName STRING,
        userID STRING,
        phonenumber STRING,
        address STRING,
        email STRING,
        dateOfBirth STRING,
        PRIMARY KEY (SHARD(surname, familiarName), userID)
    )

    CREATE INDEX DoB ON myTable (dateOfBirth)

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();
        }
    }

If you want to return entries that match a specific field name and field value, then use the `IndexKey.put()` method:

    // Construct the IndexKey. The name we gave our index when
    // we created it was 'DoB'.
    Index dobIdx = myTable.getIndex("DoB");
    IndexKey dobIdxKey = dobIdx.createIndexKey();
    // Return only those entries with a dateOfBirth equal to
    // "1991-08-23"
    dobIdxKey.put("dateOfBirth", "1991-08-23");

    // 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();
        }
    }

If you want to return all the entries with a null value for the field, use the `IndexKey.putNull()` method:

    // Construct the IndexKey. The name we gave our index when
    // we created it was 'DoB'.
    Index dobIdx = myTable.getIndex("DoB");
    IndexKey dobIdxKey = dobIdx.createIndexKey();
    // Return only those entries with a NULL dateOfBirth
    // value.
    dobIdxKey.putNull("dateOfBirth");

    // 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. When you do this, 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();
        }
    }