1. Deploy a Directory-Based Sharded Database

A directory-based configuration follows the same steps as you would for a user-defined sharded database, with a few differences.

Most of the information you need is found in Oracle Globally Distributed Database Deployment for planning, installing and creating the databases for the sharded database topology.

To configure the topology for Directory-based sharding, do the following tasks:

1. Create a shard catalog for user-defined sharding. See Creating a Shard Catalog for Directory-Based Sharding.

2. Add and start shard directors. See Add and Start Shard Directors.

3. Create shardspaces, and shards in those shardspaces. See Add Shardspaces If Needed, Add the Shard CDBs, and Add the Shard PDBs.

4. Create tablespaces in the shardspaces. See User-Defined Sharding for examples.

   Note that each tablespace has to be created individually, and explicitly associated with a shardspace.

5. Verify the topology, add shards and host metadata, deploy the configuration, and start global database services. See Oracle Globally Distributed Database Deployment.

2. Create Schema Objects

• To create tables sharded by directory, see Creating Tables Sharded by Directory.

• To add (and remove) keys to the directory, see Managing Keys in Directory-Based Sharding.

3. Run DML and Queries

See DML Support on Tables Sharded by Directory.

4. Perform Lifecycle Operations

Over the lifetime of your directory-based sharded database, you'll need to do tasks such as:

• Add and remove keys. See Managing Keys in Directory-Based Sharding

• Add partitions. See Adding a New Tablespace and Chunks (Partition) in a Shardspace

• Move chunks. See Chunk Management in Directory Based Sharding.

• Split chunks. See Splitting Partitions (Chunks).

• Query the directory view for metadata. See Sharding Key Directory Public View.

GDSCTL> create shardcatalog -database catalog_connect_string
 -user mysdbadmin/mysdbadmin_password -sharding user
 -protectmode maxperformance 

More details about creating a shard catalog, including specifying the shard catalog Connect String and connecting to the shard catalog can be found in Create the Shard Catalog.

For example:

CREATE SHARDED TABLE customers
( id             NUMBER NOT NULL
, name           VARCHAR2(30)
, address        VARCHAR2(30)
, status         VARCHAR2(1)
,
CONSTRAINT cust_pk PRIMARY KEY(id)
)
PARTITION BY DIRECTORY (id)
( PARTITION p1 TABLESPACE tbs1,
  PARTITION p2 TABLESPACE tbs2,
  PARTITION p3 TABLESPACE tbs3…);

Granting References

This case is illustrated in this example:

• Root table dealerships is under schema user1, and has account_id as the sharding key.

• Child table salespeople is under schema user2, and is defined via "parent user1.dealerships".

Before this salespeople child table can be created, you need:

• grant all privileges on user1.dealerships to user2;

  This is the same as needed for user-defined sharding.

• grant references (account_id) on user1.dealerships$sdir to user2;

  This is new for directory-based sharding.

Note that dealerships$sdir is the internally generated directory table name; it has the format of root_table_name$sdir.

Without the 2nd grant, the child table creation DDL will succeed on the shard catalog but will fail on the shards (as the foreign key is only added on the shards).

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

The steps involved are:

1. Create new tablespaces in the desired shardspace.

2. Run ALTER TABLE ADD PARTITION partition_name TABLESPACE tablespace_name on the sharded table, for example:

   ALTER TABLE customers ADD PARTITION p4 TABLESPACE tb4;

This results in the creation of an empty partition and chunk in the specified shardspace. Subsequent inserts of new key values can then specify this new partition as the target.

If you specify the assignment rule to be last partition, all new key inserts will be automatically assigned to the new partition.

1. Invoke the DBMS_SHARDING_DIRECTORY PL/SQL API flagKeyForSplit to mark keys for splitting.

   PROCEDURE flagKeyForSplit
        (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.

2. Issue the partition split DDL.

   ALTER TABLE customers SPLIT PARTITION p1 INTO 
   ( PARTITION p1 TABLESPACE tb1,
     PARTITION p3 TABLESPACE tb3 ) 
     UPDATE INDEXES;
   

   Note that, in directory-based sharding, a partition can be split into only two partitions at a time.

   This operation will go through all of the keys that have been marked for split in the directory and split the corresponding data out into the new partition.