7 Deploying and Managing a Directory-Based Oracle Globally Distributed Database

Directory-based sharding allows you to explicitly associate key value with shards dynamically at run time, which gives you fine-grained control over mapping of key values to shards

Directory-Based Sharding Roadmap

Set up a directory-based sharded database, including configuring the sharded database, creating schema objects, and doing lifecycle management operations.

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.

Creating a Shard Catalog for Directory-Based Sharding

Directory-based sharding is an enhancement of the User-defined sharding method, so the shard catalog is configured with the user-defined sharding option.

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.

Creating Tables Sharded by Directory

Create directory-based sharded tables using PARTITION BY DIRECTORY in the CREATE SHARDED TABLE statement.

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…);

Note:

• Unlike in user-defined sharding, key values are not specified for the partitions in the CREATE TABLE statement.

• The directory table is automatically created during root table creation. The definition of the directory table is:

  <shard user schema>.<root_table>$SDIR

• Maximum length for the root table name (identifier length limit) is 113, to account for the additional characters added to the view name created on the root table (as in root_table_name_$SHARD_DIR_VIEW).

• If a child table is created with parent clause in a different schema from the root table, an additional privilege is required for the child table's schema owner. (This is only for directory-based sharding and is not required for regular user-defined sharding.)

  This is because there is a foreign key constraint on the child table to the directory table's sharding key columns, to ensure that no rows can be inserted into the child table without the sharding key value being present in the directory mapping. As a consequence, the child table's schema needs a reference privilege on the directory table's sharding key columns.

  See "Granting References" below.

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).

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

DML Support on Tables Sharded by Directory

Directory-based sharding offers the same support as other sharding methods for regular DMLs and queries run on the shard with partition pruning support.

Adding a New Tablespace and Chunks (Partition) in a Shardspace

You may need to add a new tablespace and partition to a table sharded by directory when you want to add new groupings of keys on an existing shardspace or a newly added shardspace.

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.

Chunk Management in Directory Based Sharding

As with user-defined sharding, tablespaces created for directory-based sharding are assigned to chunks.

The total number of chunks is defined by the number of partitions specified in the sharded table. The number of chunks for a given shardspace is the number of partitions assigned to it. The ALTER TABLE ADD, DROP, and SPLIT PARTITION commands on the sharded table increases or decrease the number of chunks.

The GDSCTL SPLIT CHUNK command, which is used to split a chunk in the middle of the hash range for system-managed sharding, is not supported for directory-based sharding. You must use the ALTER TABLE SPLIT PARTITION statement to split a chunk.

Also, just like user-defined sharding, no chunk migration is automatically started when a shard is added to the sharded database. You must run the GDSCTL MOVE CHUNK command for each chunk that needs to be moved to another shard.

Splitting Partitions (Chunks)

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.

Sharding Key Directory Public View

The view root_table_name$shard_dir_view provides you with the key to partition/chunk/shard mappings for the specified root table.

Table 7-1 root_table_name$SHARD_DIR_VIEW

Name	Type	NULL	Description
KEY columns…	varies	No	Unique sharding key column values
KEY_ID$	RAW(32)	No	Unique SHA-256 ID assigned to the key
CHUNK_ID$	NUMBER	No	The chunk ID to which the key is assigned
PARTITION_NAME	VARCHAR2(128)	No	Name of the root table partition the key is assigned to
SHARDSPACE_NAME	VARCHAR2(128)	No	The shardspace name where the chunk belongs to
SPLIT_FLAG$	NUMBER	Yes	0: not flagged (default) 1: flagged