Oracle Sharding 18c – Multi-shard Query Enhancements

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:

Screen Shot 2018-12-04 at 1.13.36 PM

For in-depth collateral on Sharding, visit the Oracle Sharding OTN portal – https://www.oracle.com/goto/sharding

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s