Managing Keys in Directory-Based Sharding

The directory table contains the metadata for mapping keys to partitions. You can use the DBMS_SHARDING_DIRECTORY PL/SQL API to add and remove keys.

Note:

When adding and removing keys there are APIs that include commit and those that do not. Unless the commit versions of the APIs are used, the directory content is not propagated to the shards until commit is issued explicitly.

Adding Keys

You can add a key to the directory with the specified partition name using addKeyToPartition or addKeyToPartitionCommit.

The addKeyToPartitionCommit procedure is exactly the same as the addKeyToPartition procedure with the same parameters, except that it performs a commit automatically at the end. 

PROCEDURE addKeyToPartition[Commit]
     (schema_name    IN varchar2,    -- root table schema name
      root_table     IN varchar2,    -- root table name
      partition_name IN  varchar2,   -- name of the partition
      key …)                        -- shard key column value

Note that the key column value needs to be in the same order as specified in the CREATE TABLE statement with the correct types. The procedure can only succeed if the provided key does not yet exist in the directory.

Removing Keys

You can remove a key from the directory using removeKey or removeKeyCommit.

The removeKeyCommit procedure is exactly the same as the removeKey procedure with the same parameters, except that it performs a commit automatically at the end. 

PROCEDURE removeKey
     (schema_name    IN varchar2,    -- root table schema name
      root_table     IN varchar2,    -- root table name
      key … )                       -- shard key column values

Note that the key column values need to be in the same order as specified in the CREATE TABLE statement with the correct types. The procedure can only succeed if the provided key exists in the directory, and there are no tables (either root table or child tables) with rows still referencing the key.

Enable Automatic Key-to-Partition Assignment

You can indicate an automatic key-to-partition assignment rule for subsequent new key inserts into the root table. 

PROCEDURE setAssignmentRule
     (schema_name    IN varchar2,    -- root table schema name
      root_table     IN varchar2,    -- root table name
      rule_id        IN number);   -- rule ID as defined in public constants

Once set, the key-to-partition assignment rule stays in effect across different sessions, regardless of system restart, until another call to the procedure is made with a different rule value, or with NONE meaning automatic assignment should be turned off.

The following constants are defined for key-to-partition assignment rules.

  • NONE constant number :=0; -- turn off rule-based assignment

  • LAST_PARTITION constant number := 1; -- rule for assigning key only to the last added partition

  • ROUNT_ROBIN constant number :=2; -- rule for assigning key to partition by round robin

  • RANDOM constant number :=3; -- rule for assigning key to partition randomly

  • CUSTOM constant number :=4; -- TBD