Sharding Oracle Database

Sharding Oracle DB

Source: https://www.oracle.com/technetwork/database/availability/sharding-faq-3610620.pdf

•Sharding is for OLTP apps

•Shards are distributed (but not necessarily replicated –unless you use Data Guard or GoldenGate for some Oracle instances) partitions (horizontal partitioning).

•Shards reside on different Oracle instances, that may be of different releases (as long as the app is backward compatible with orlder Oracle DB versions)

•The application believes there is only one Oracle instance: a sharded DB (SDB); but for better performances, it has to specify a shard-key (which is pretty much a partitioning key -e.g. customer ID, account nb, etc.) in the connection-string of the JDBC driver. The JDBC driver or connection pool will use this shard-key to provide a connection object for the relevant shard which contains the data pertinent to the required transaction [1] (i.e. not the whole Oracle instance). Note that there is a shard-routing-cache at the connection pool level.
Also, note that you could decide not to target the shards, by setting up a coordinator node; but this involves reduced performances, as it is more likely that your transactions will span multiple shards.
Besides this targeting of a shard via the connection string, I think the DML queries (submitted by the apps) remain the same [2].

--

1: “In direct, key-based, routing to a shard, a connection is established to a single, relevant shard which contains the data pertinent to the required transaction using a sharding key.” [source]
2: ”Once the session is established with a shard, all SQL queries and DMLs are supported and executed in the scope of the given shard.”  [source]

•The DDL is changed: you will use a specific type of DDL (“CREATE SHARDED TABLE”); and will add some commonly accessed read-only tables to all your shards, to avoid having to access several shards for a single transaction. Those tables are called “duplicated tables” (“CREATE DUPLICATED TABLE”).
Duplicated tables are implemented under the hood thanks to materialized views.

•You define your sharding according to principles similar to partitioning:

– you can choose to have your data evenly and randomly distributed across shards, in order to ensure load balancing across your Oracle instances.

–But you can also choose to first partition your tables based on a list (e.g. date, location) or range of rows in your table.

Licensing:

•For a sharded database (SDB) with 3 or fewer primary shards (in contrast with replicated shards, thanks to DataGuard or Golden Gate), Oracle Sharding is included with EE (includes Data Guard). No limit on number of standby shards.

•For an SDB with more than 3 primary shards, in addition to EE, all shards must be licensed either for Active Data Guard, Oracle GoldenGate or RAC.