In the previous post of the 18c blog post series, we looked at the Oracle RAC Sharding feature that allows you to virtually shard data across instances of an Oracle RAC database. In this post, I will discuss the Multi-shard query enhancements –  in Oracle Sharding 18c.

Multi-shard Query Enhancements

In Oracle Sharding 12.2, multi-shard queries (via proxy routing) supported system-managed sharding method. In Oracle Sharding 18c, multi-shard queries have been enhanced to now support the composite and user-defined sharding methods as well.

For multi-shard queries, one can set different consistency levels by the initialization parameter MULTISHARD_QUERY_DATA_CONSISTENCY.

The query explain plan is also enhanced to display the information for all shards participating in the multi-shard query.

For centralized diagnostics, the SQL SHARDS() clause can be leveraged to query the V$, DBA/USER/ALL views, dictionary tables across all shards.

For example: