How Database Requests are Routed to the Shards

In Oracle Sharding, database query and DML requests are routed to the shards in two main ways, depending on whether a sharding key is supplied with the request.

These two routing methods are called direct routing and proxy routing.

Direct Routing

You can connect directly to the shards to process queries and DML by providing a sharding key with the database request. Direct routing is the preferred way of accessing shards to achieve better performance, among other benefits.

Proxy Routing

Queries that need data from multiple shards, and queries that do not specify a sharding key, cannot be routed directly by the application. Those queries require a proxy to route requests between the application and the shards. Proxy routing is handled by the shard catalog query coordinator.

Routing Queries and DMLs Directly to Shards

Applications can have their requests routed directly to the shards if they provide a sharding key. With the direct routing mechanism, requests can only query and manipulate the data that belongs to the shard they were routed to.

Direct access to the data on the shards has several advantages.

Offers better performance: Overall, applications experience better performance compared to routing requests to the shards indirectly through the shard catalog (by proxy). With direct routing there is no need for the requests and the results to pass through a coordinator database.
Accommodates geographic distribution of shards: Applications can access the data in shards localized in their region.
Eases load balancing: Load balancing application requests across the shards can be easily achieved by moving the data across shards using chunk moves.
Supports all type of queries:
- SELECT, INSERT, and UPDATE on sharded tables: The scope of these requests is the data that belong to the shards accessed.
- SELECT, INSERT, and UPDATE on duplicated tables: The scope of theses requests is all of the data in the duplicated tables. Because the primary copies of a duplicated tables reside in the coordinator database, the DMLs on the duplicated tables are re-routed to the coordinator database.

The following figure illustrates DML on duplicated tables using direct routing to a shard.

The Application sends the DML request directly to one of the shards, Shard DB1.
The DML is forwarded from Shard DB1 to the Coordinator Database, where it is run on the primary duplicated tables.
The Coordinator Database refresh mechanism runs periodically to update the instances of the duplicated tables on all of the shards.

Figure 9-1 DML on a Duplicated Table with Direct Routing

Description of "Figure 9-1 DML on a Duplicated Table with Direct Routing"

For more information about direct routing, see Client Application Request Routing.

For information about developing applications for direct routing, see Developing Applications for the Sharded Database

Routing Queries and DMLs by Proxy

Using the shard catalog query coordinator as a proxy, Oracle Sharding can handle request routing for queries and DMLs that do not specify a sharding key.

By using the coordinator as a proxy, Oracle Sharding provides you with the flexibility to allow any database application to run SQL statements without the need to specify the shards where the query should be processed.

For more information about the coordinator, see Query Processing and the Query Coordinator.

The following figure illustrates DML on duplicated tables using proxy routing.

The Application sends the DML request to the Coordinator Database where it is run on the primary duplicated tables.
The Coordinator Database refresh mechanism runs periodically to update the instances of the duplicated tables on all of the shards.

Figure 9-2 DML on a Duplicated Table with Proxy Routing

Description of "Figure 9-2 DML on a Duplicated Table with Proxy Routing"

The remaining topics in this chapter discuss routing and processing database requests by proxy.