Iterating over Table Rows

TableAPI.tableIterator() provides non-atomic table iteration. Use this method to iterate over indexes. This method performs a parallel scan of your tables if you set a concurrent request size other than 1.

TableAPI.tableIterator() does not return the entire set of rows all at once. Instead, it batches the fetching of rows in the iterator, to minimize the number of network round trips, while not monopolizing the available bandwidth. Also, the rows returned by this method are in unsorted order.

Note that this method does not result in a single atomic operation. Because the retrieval is batched, the return set can change over the course of the entire retrieval operation. As a result, you lose the atomicity of the operation when you use this method.

This method provides for an unsorted traversal of rows in your table. If you do not provide a key, then this method will iterate over all of the table's rows.

When using this method, you can optionally specify:

• A MultiRowOptions class instance. This class allows you to specify a field range, and the ancestor and parent tables you want to include in this iteration.

• A TableIteratorOptions class instance. This class allows you to identify the suggested number of keys to fetch during each network round trip. If you provide a value of 0, an internally determined default is used. You can also use this class to specify the traversal order (FORWARD, REVERSE, and UNORDERED are supported).

  This class also allows you to control how many threads are used to perform the store read. By default this method determines the degree of concurrency based on the number of available processors. You can tune this concurrency by explicitly stating how many threads to use for table retrieval. See Parallel Scans for more information.

  Finally, you use this class to specify a consistency policy. See Consistency Guarantees for more information.

Note:

When using TableAPI.tableIterator(), it is important to call TableIterator.close() when you are done with the iterator to avoid resource leaks. This is especially true for long-running applications, especially if you do not iterate over the entire result set.

For example, suppose you have a table that stores information about products, which is designed like this:

CREATE TABLE myTable (
    itemType STRING,
    itemCategory STRING,
    itemClass STRING,
    itemColor STRING,
    itemSize STRING,
    price FLOAT,
    inventoryCount INTEGER,
    PRIMARY KEY (SHARD(itemType, itemCategory, itemClass), itemColor,
    itemSize)
) 

With tables containing data like this:

• Row 1:

  • itemType: Hats

  • itemCategory: baseball

  • itemClass: longbill

  • itemColor: red

  • itemSize: small

  • price: 12.07

  • inventoryCount: 127

• Row 2:

  • itemType: Hats

  • itemCategory: baseball

  • itemClass: longbill

  • itemColor: red

  • itemSize: medium

  • price: 13.07

  • inventoryCount: 201

• Row 3:

  • itemType: Hats

  • itemCategory: baseball

  • itemClass: longbill

  • itemColor: red

  • itemSize: large

  • price: 14.07

  • inventoryCount: 39

• Row n:

  • itemType: Coats

  • itemCategory: Casual

  • itemClass: Winter

  • itemColor: red

  • itemSize: large

  • price: 247.99

  • inventoryCount: 9

Then in the simplest case, you can retrieve all of the rows related to 'Hats' using TableAPI.tableIterator() as follows. Note that this simple example can also be accomplished using the TableAPI.multiGet() method. If you have a complete shard key, and if the entire results set will fit in memory, then multiGet() will perform much better than tableIterator(). However, if the results set cannot fit entirely in memory, or if you do not have a complete shard key, then tableIterator() is the better choice. Note that reads performed using tableIterator() are non-atomic, which may have ramifications if you are performing a long-running iteration over records that are being updated.

package kvstore.basicExample;

...

import oracle.kv.KVStore;
import oracle.kv.table.PrimaryKey;
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();

// The name you give to getTable() must be identical
// to the name that you gave the table when you created
// the table using the CREATE TABLE DDL statement.
Table myTable = tableH.getTable("myTable");

// Construct the PrimaryKey. In this case, we are
// using a partial primary key.
PrimaryKey key = myTable.createPrimaryKey();
key.put("itemType", "Hats");

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