14.8 Configuring Key-Based Routing for Client-Server Connections

Learn how to establish client-server connections in a sharded database by using key-based (or direct) routing. In key-based routing to a shard, a connection is established to a single, relevant shard that contains the data pertinent to the required transaction using a sharding key.

  • About Key-Based Routing
    With key-based or direct routing, a database connection is established directly from the client to a shard according to the value of the sharding key specified in the connect string.
  • Specifying a Sharding Key in the Connect String
    You can directly connect to shards by specifying a sharding key in the CONNECT_DATA section of a connect string or tnsnames.ora file. You can also specify shard directors (as endpoints), global service name, and region name for your Oracle Globally Distributed Database deployment.

Parent topic: Optimizing Performance

14.8.1 About Key-Based Routing

With key-based or direct routing, a database connection is established directly from the client to a shard according to the value of the sharding key specified in the connect string.

Oracle Globally Distributed Database enables you to horizontally partition data across multiple, independent Oracle databases. Each physical database in such a configuration is called a shard. Sharded table partitions are distributed across shards at the tablespace level, based on a sharding key. Examples of keys include customer ID, account number, and country ID.

A shard director is a specific implementation of a Global Service Manager (GSM) that acts as a regional listener for clients that connect to a sharded database. Based on the sharding key passed during a connection request, the shard director establishes a connection to an appropriate shard as follows:
  • The shard director performs a lookup for the service and key value, and then redirects the client to a shard containing the data.
  • The client runs a SQL statement and receives results directly from the shard.

Direct routing assumes that all the database requests that are issued within a connection are related to the data associated with the specified key value. If a client needs to access data with a different key value, then it must establish a new database connection.

The connect string contains a single sharding key if an Oracle Database is sharded by consistent hash, list, or range. Oracle Database also supports composite sharding that enables two levels of sharding. First the data is sharded by list or range and then it is further sharded by consistent hash. In composite sharding method, the connect string contains both a sharding key (for sharding by consistent hash) and a super sharding key (for sharding by list or range).

Related Topics

Parent topic: Configuring Key-Based Routing for Client-Server Connections

14.8.2 Specifying a Sharding Key in the Connect String

You can directly connect to shards by specifying a sharding key in the CONNECT_DATA section of a connect string or tnsnames.ora file. You can also specify shard directors (as endpoints), global service name, and region name for your Oracle Globally Distributed Database deployment.

Specify a shard (partition) key value using one of the following parameters:

  • SHARDING_KEY: To specify a sharding key for a particular shard in simplified text format.

  • SHARDING_KEY_ID: To specify the unique SHA-256 ID of a sharding key in simplified text format. This allows clients that have already calculated the SHA-256 hash value of a sharding key to pass it directly in the connect string. You use this parameter for directory-based sharding.

  • SUPER_SHARDING_KEY: To specify a shardspace key for a collection of shards in simplified text format.

  • SHARDING_KEY_B64: To specify a sharding key for a particular shard in base64-encoded binary format.

  • SUPER_SHARDING_KEY_B64: To specify a shardspace key for a collection of shards in base64-encoded binary format.

The connect string must be in the format required to connect to a global service.

Here is an example of the connect string for a shard, where the SHARDING_KEY and SUPER_SHARDING_KEY parameter values are specified in simplified text format: 
(DESCRIPTION=
  (FAILOVER=on)
  (ADDRESS_LIST=
    (LOAD_BALANCE=ON)
    (ADDRESS=(host=sales-east1)(port=1522))
    (ADDRESS=(host=sales-east2)(port=1522))
    (ADDRESS=(host=sales-east3)(port=1522)))
  (ADDRESS_LIST=
    (LOAD_BALANCE=ON)
    (ADDRESS=(host=sales-west1)(port=1522))
    (ADDRESS=(host=sales-west2)(port=1522))
    (ADDRESS=(host=sales-west3)(port=1522)))
  (CONNECT_DATA=
   (SERVICE_NAME=sales)
   (SHARDING_KEY=40598230)(SUPER_SHARDING_KEY=gold)
   (REGION=east))
)
Here is an example of the connect string for a shard, where the SHARDING_KEY_ID parameter value is specified in simplified text format: 
(DESCRIPTION=
  (FAILOVER=on)
  (ADDRESS_LIST=
    (LOAD_BALANCE=ON)
    (ADDRESS=(host=sales-east1)(port=1522))
    (ADDRESS=(host=sales-east2)(port=1522))
    (ADDRESS=(host=sales-east3)(port=1522)))
  (ADDRESS_LIST=
    (LOAD_BALANCE=ON)
    (ADDRESS=(host=sales-west1)(port=1522))
    (ADDRESS=(host=sales-west2)(port=1522))
    (ADDRESS=(host=sales-west3)(port=1522)))
  (CONNECT_DATA=
   (SERVICE_NAME=sales)
   (SHARDING_KEY_ID='7E01C6D3F5AF3116668AFB6B2376DAA457165A34020617884C216F1ADAA25C7B')
   (REGION=east))
)

Related Topics

Parent topic: Configuring Key-Based Routing for Client-Server Connections