In the first post of the Oracle Database 18c Sharding features blog post series, I covered User-defined sharding method and discussed in detail on how on it can be leveraged for global data distribution and hybrid cloud deployment. In the second post, I  discussed the PDB Sharding feature with which we can distribute data with Sharding and yet consolidate databases with Multitenant – a best of both worlds. In this post, I will discuss the Oracle RAC Sharding feature that allows you to virtually shard data across instances of an Oracle RAC database.

#3:  Oracle RAC Sharding

Oracle RAC Sharding is a feature that logically establishes affinity for table partitions to  RAC instances. This reduces block pings across instances while yielding better cache affinity.

Figure 1.  High-performance for shard-aware RAC applications with RAC Sharding

This feature which is applicable to non-sharded RAC databases, takes advantage of direct routing API of Oracle Sharding – to specify the partitioning key as part of the check-out of a given connection from the connection pool. The application must use integrated Oracle clients such as Oracle Universal Connection Pool (UCP), Oracle Call Interface (OCI) Session Pool, Oracle Data Provider for .NET (ODP.NET) Connection Pool etc. Apache Tomcat, JBoss, IBM WebSphere and Oracle WebLogic can use UCP support for sharding. PHP, Python, Perl, and Node.js can use OCI support.

Requests that specify partitioning key are routed to the instance that logically holds the corresponding partition while the requests that don’t specify partitioning key still work transparently.

This capability empowers Oracle RAC with the performance and linear scalability of an Oracle Sharded Database with minimal application changes. The application just specifies the sharding key (as part of the connection check-out) for the most performance critical operations.  Here is an example on how to use Oracle Universal Connection Pooling (UCP) Sharding API for RAC Sharding feature.

OracleShardingKey keyMaryEmail =
pds.createShardingKeyBuilder()
.subkey(“mary.smith@xyz.com”, OracleType.VARCHAR2)
.build();

Connection connection =
pds.createConnectionBuilder()
.shardingKey(keyMaryEmail)
.build();

The RAC Sharding feature is enabled on the RAC database by executing the following:

alter system enable affinity <TableName>;

No changes to the database schema are required. There is no requirement to deploy Sharding infrastructure as well.

Note: Application changes which are required to supply the partition key, don’t have to affect all modules of the application – can be done only for the most performance intensive operations. Access via partitioning  key will go to the RAC instance that holds the logical subset of the data. All instances can still access all the data. Requests that don’t provide the partitioning key will be routed based on the default load balancing policy. 

For more information on RAC Sharding, take a look at the following documentation Links:

Oracle RAC Sharding 

https://docs.oracle.com/en/database/oracle/oracle-database/18/racad/workload-management-with-dynamic-database-services.html#GUID-1100990E-18BD-4C92-A0C7-3C08C40AAD14

UCP APIs for Database Sharding Support

https://docs.oracle.com/en/database/oracle/oracle-database/18/jjucp/ucp-sharding-api.html#GUID-3B91E8F7-AE45-4CC8-B87F-6F4FC9EEA001

JDBC Support for Database Sharding

https://docs.oracle.com/en/database/oracle/oracle-database/18/jjdbc/database-sharding.html#GUID-1D7795CA-79DC-452B-9FCC-0EF430F87461

For in-depth collateral on Sharding, visit the Oracle Sharding OTN portal – https://www.oracle.com/goto/sharding and do follow me on Twitter  – https://twitter.com/nageshbattula

In the next blog post, I will discuss the Multi-shard query enhancements in Oracle Sharding 18c.