1. Developer's Guide
  2. Features of Oracle Data Provider for .NET
  3. Oracle Globally Distributed Database

Oracle Globally Distributed Database

Oracle Globally Distributed Database is a data tier architecture, where data is horizontally partitioned across independent databases. Each database in such a configuration is called a shard. All shards together make up a single logical database, which is referred to as a sharded database. Oracle Globally Distributed Database is a shared-nothing database architecture. The independent physical databases do not share CPU, memory, or storage devices. However, from the perspective of an application, the collection of physical databases looks like a single logical database.

Oracle Globally Distributed Database uses Global Data Services (GDS), where GDS routes a client request to an appropriate database based on parameters such as availability, load, network latency, and replication lag. A GDS pool is a set of replicated databases that offers the same global service. The databases in a GDS pool can be located in multiple data centers across different regions. A sharded GDS pool contains all shards of a sharded database and their replicas, and appears as a single sharded database to database clients.

Applications can connect to multiple databases (shards) where data is partitioned based on one or more sharding strategies. The strategy can be hash based, range based, or list based. Each time a database operation is required, the application needs to determine which shard it must connect to.

A sharding key provides the partitioning key that determines in which shard a row of data is stored. A table can be partitioned using a sharding key.

A super sharding key is a collection of shard chunks, where only those chunks, which have a specific value of the super shard key identifier, are stored. A super sharding key is used for distributing data across database groups. Specifying super sharding keys are a way through which user-controlled data partitioning is possible.

ODP.NET Sharding

Starting from version 12.2, unmanaged ODP.NET and Oracle Database both support sharding. Managed ODP.NET and ODP.NET Core started supporting sharding with version 21. All three providers support the same sharding features with the exception that unmnanaged ODP.NET does not support the chunk migration connection timeout property.

ODP.NET applications must provide the sharding key and super sharding key information before opening the database connection for single shard queries. These sharding values cannot be set or changed after opening the connection. If any of the shard key values need to be modified, a new connection must be created with the new values and then opened.

If shard keys are set after the connection has been opened, the ODP.NET connection will not use these new shard key values until after the next OracleConnection.Open() call.

The OracleShardingKey object stores one or more key values. Multiple keys can be set to create a composite key. ODP.NET recognizes the sharding key(s) specified and connects to the correct shard and chunk.

Sharding is supported with or without connection pooling. The ODP.NET connection pool maintains connections to different shards and chunks of the sharded GDS database within the same shared pool.

The shard key (SHARD_KEY) and super sharding key (GROUP_KEY) can be specified in the TNS connect descriptor, rather than in the application code. The .NET developer then chooses the connect descriptor applicable to the shard that the application will use.

The data distribution across the shards and chunks in the database is transparent to the end user. ODP.NET minimizes the end user impact of chunk resharding within GDS.

To perform cross-shard queries, no ODP.NET shard APIs are used. Instead, applications connect to the GDS catalog service, allowing access to all the sharded databases. The SQL query is specifically constructed to iterate over all the necessary shards. For example, the non-shard database query select count(*) from employees is equivalent to the cross- shard query select sum(c) from (Iterator(select count(*) c from employees(i)).

ODP.NET Single Shard Query Example

using System;
using Oracle.DataAccess.Client;
 
class Sharding
{
  static void Main()
  {
    OracleConnection con = new OracleConnection("user id=hr;password=hr;Data Source=orcl;");
    //Setting a shard key
    OracleShardingKey shardingKey = new OracleShardingKey(OracleDbType.Int32, 123);
    //Setting a second shard key value for a composite key
    shardingKey.SetShardingKey(OracleDbType.Varchar2, "gold");
    //Creating and setting the super shard key
    OracleShardingKey superShardingKey = new OracleShardingKey();
    superShardingKey.SetShardingKey(OracleDbType.Int32, 1000);
    
    //Setting super sharding key and sharding key on the connection
    con.SetShardingKey(shardingKey, superShardingKey);
    con.Open();
 
    //perform SQL query
  }
}

By default, ODP.NET only dispenses connections to shards with chunks with read and write privileges available. If some chunks in a shard are read-only, ODP.NET will not dispense a connection to that shard unless READONLY_CHUNK_OK is set to TRUE in the CONNECT_DATA section of the connect descriptor. Connect descriptors are generally stored in tnsnames.ora files. Enabling READONLY_CHUNK_OK just means that both read/write and read-only connections can be dispensed. It does not specify only read-only connections are dispensed.

Shards are made read-only when chunks are migrating from one shard to another, usually to re-balance data or workload across shards.

Here's an example using READONLY_CHUNK_OK in a connect descriptor: 

SHARDDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = tcp)(HOST = myhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = myservicename)
     (READONLY_CHUNK_OK=true)
    )
  )

In ODP.NET, super shard keys can use range sharding or list sharding. With composite sharding, the shard keys must use hash sharding. The table below describes operations supported for different shard key types, not super shard keys.

Table 3-13 Supported Operations for Shard Key Types

Chunk Event Type Hash Range List

Chunk Read Only

Not supported in unmanaged ODP.NET

Yes

Yes

Yes

Chunk Up

Yes

Yes

Yes

Chunk Down

Yes

Yes

Yes

Chunk Split

Yes

Yes

Yes

Add Chunk

No

Yes

Yes

Add Chunk Values

No

No

Yes

Drop Chunk

No

Yes

Yes

Drop Chunk Values

No

No

Yes

Merge Chunk

No

Yes

Yes

Invalidate Chunk

No

Yes

Yes

Partitionset Split

Not supported in unmanaged ODP.NET

Yes

No

No

New Partitionset

Not supported in unmanaged ODP.NET

Yes

No

No

Sharding Split Partitionset

Over time, changing business needs require modifying data organization to optimize resource usage. Multiple super sharding keys grouped in the same shardspace may have been optimal under past conditions. If it becomes necessary to move one or more of the super sharding keys into their own shardspace, such as on a faster machine, then split partitionset operations can facilitate efficient data movement with little down time.

Because each chunk contains data for multiple super sharding keys, a split operation occurs first. Then, the data is moved. The operation is performed one chunk at a time. When the chunk needs to be split, its data will be read-only for a short time. The chunk's sharding key range does not change.

To limit connectivity errors while the chunk is unavailable during a chunk migration or split partitionset, developers can set the ChunkMigrationConnectionTimeout property to wait for the set time or whenever the operation completes, whichever comes first. Users then do not receive connection timeout errors while the migration or split occurs if the operation completes in the expected time frame.

A key benefit for developers is they do not have to maintain and track split partitionset activity in their applications. ODP.NET performs this activity for them automatically and routes connections appropriately. It has a shard routing cache to manage the sharding topology and record changes. The ODP.NET connection pool will dispense the appropriate connection for a given sharding key and super sharding key, including after a split partitionset.

ODP.NET split partitionset is supported for hash sharding only with either list or range super shard keys.

See Also: