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:
-
Create a shard catalog for user-defined sharding. See Creating a Shard Catalog for Directory-Based Sharding.
-
Add and start shard directors. See Add and Start Shard Directors.
-
Create shardspaces, and shards in those shardspaces. See Add Shardspaces If Needed, Add the Shard CDBs, and Add the Shard PDBs.
-
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.
-
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 schemauser1
, and hasaccount_id
as the sharding key. -
Child table
salespeople
is under schemauser2
, 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:
-
Create new tablespaces in the desired shardspace.
-
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)
-
Invoke the
DBMS_SHARDING_DIRECTORY
PL/SQL APIflagKeyForSplit
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. -
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 |