Deleting Rows from the Store

You delete a single row from the store using the TableAPI.delete() method. Rows are deleted based on a PrimaryKey, which you obtain using the Table.createPrimaryKey() method. You can also require a row to match a specified version before it will be deleted. To do this, use the TableAPI.deleteIfVersion() method. Versions are described in Using Row Versions.

When you delete a row, you must handle the same exceptions as occur when you perform any write operation on the store. See Write Exceptions for a high-level description of these exceptions.

package kvstore.basicExample;

import oracle.kv.KVStore;
import oracle.kv.table.PrimaryKey;
import oracle.kv.table.Table;
import oracle.kv.table.TableAPI;

...

// 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");

// Get the primary key for the row that we want to delete
PrimaryKey primaryKey = myTable.createPrimaryKey();
primaryKey.put("item", "Bolts");

// Delete the row
// This performs a store write operation
tableH.delete(primaryKey, null, null); 

Using multiDelete()

You can delete multiple rows at once in a single atomic operation, so long as they all share the shard key values. Recall that shard keys are at least a subset of your primary keys. The result is that you use a partial primary key (which happens to be a shard key) to perform a multi-delete.

To delete multiple rows at once, use the TableAPI.multiDelete() method.

For example, suppose you created a table 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

Then in this case, you can delete all the rows sharing the partial primary key Hats, baseball, longbill as follows:

package kvstore.basicExample;

import oracle.kv.KVStore;
import oracle.kv.table.PrimaryKey;
import oracle.kv.table.Table;
import oracle.kv.table.TableAPI;

...

// 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");

// Get the primary key for the row that we want to delete
PrimaryKey primaryKey = myTable.createPrimaryKey();
primaryKey.put("itemType", "Hats");
primaryKey.put("itemCategory", "baseball");
primaryKey.put("itemClass", "longbill");

// Exception handling omitted
tableH.multiDelete(primaryKey, null, null);