Library Version 12.1.3.0

Iterating over Table Rows
Prev | Chapter 7. Reading Table Rows | Next

## 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` and `UNORDERED` are the only options).

  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:

    ## Enter into table creation mode
    table create -name myTable
    ## Now add the fields
    add-field -type STRING -name itemType
    add-field -type STRING -name itemCategory
    add-field -type STRING -name itemClass
    add-field -type STRING -name itemColor
    add-field -type STRING -name itemSize
    add-field -type FLOAT -name price
    add-field -type INTEGER -name inventoryCount
    primary-key -field itemType -field itemCategory -field itemClass
    -field itemColor -field itemSize
    shard-key -field itemType -field itemCategory -field itemClass
    ## Exit table creation mode
    exit

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 with `TableAPI.multiGet()`. If you have a complete shard key, and if the entire results set will fit in memory, then `TableAPI.multiGet()` will perform much better than `TableAPI.TableIterator`. However, if the results set cannot fit entirely in memory, or if you do not have a complete shard key, then `TableAPI.TableIterator` is the better choice. Note that reads performed using `TableAPI.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 CLI's 'table create' command.
    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();
        }
    }

Prev | Up | Next
Using multiGet() | Home | Specifying Field Ranges