Sharding is a data tier architecture in which data is horizontally partitioned across independent databases.

Each database is hosted on dedicated server with its own local resources - CPU, memory, flash, or disk. Each database in such configuration is called a shard. All of the shards together make up a single logical database, which is referred to as a sharded database (SDB).

Horizontal partitioning involves splitting a database table across shards so that each shard contains the table with the same columns but a different subset of rows. A table split up in this manner is also known as a sharded table.

The following figure shows a table horizontally partitioned across three shards.

Sharding is based on shared-nothing hardware infrastructure and it eliminates single points of failure because shards do not share physical resources such as CPU, memory, or storage devices. Shards are also loosely coupled in terms of software; they do not run clusterware.

Shards are typically hosted on dedicated servers. These servers can be commodity hardware or engineered systems. The shards can run on single instance or Oracle RAC databases. They can be placed on-premises, in a cloud, or in a hybrid on-premises and cloud configuration.

From the perspective of a database administrator, an SDB consists of multiple databases that can be managed either collectively or individually. However, from the perspective of the application, an SDB looks like a single database: the number of shards and distribution of data across those shards are completely transparent to database applications.

Sharding is intended for custom OLTP applications that are suitable for a sharded database architecture. Applications that use sharding must have a well-defined data model and data distribution strategy (consistent hash, range, list, or composite) that primarily accesses data using a sharding key. Examples of a sharding key include customer_id, account_no, or country_id.

Sharding provides linear scalability and complete fault isolation for the most demanding OLTP applications.

Key benefits of sharding include:

• Linear Scalability. Sharding eliminates performance bottlenecks and makes it possible to linearly scale performance and capacity by adding shards.

• Fault Containment. Sharding is a shared nothing hardware infrastructure that eliminates single points of failure, such as shared disk, SAN, and clusterware, and provides strong fault isolation—the failure or slow-down of one shard does not affect the performance or availability of other shards.

• Geographical Distribution of Data. Sharding makes it possible to store particular data close to its consumers and satisfy regulatory requirements when data must be located in a particular jurisdiction.

• Rolling Upgrades. Applying configuration changes on one shard at a time does not affect other shards, and allows administrators to first test the changes on a small subset of data.

• Simplicity of Cloud Deployment. Sharding is well suited to deployment in the cloud. Shards may be sized as required to accommodate whatever cloud infrastructure is available and still achieve required service levels. Oracle Sharding supports on-premises, cloud, and hybrid deployment models.

Unlike NoSQL data stores that implement sharding, Oracle Sharding provides the benefits of sharding without sacrificing the capabilities of an enterprise RDBMS. For example, Oracle Sharding supports:

• Relational schemas

• Database partitioning

• ACID properties and read consistency

• SQL and other programmatic interfaces

• Complex data types

• Online schema changes

• Multi-core scalability

• Advanced security

• Compression

• High Availability features

• Enterprise-scale backup and recovery

Oracle Sharding is a scalability and availability feature for suitable OLTP applications. It enables distribution and replication of data across a pool of Oracle databases that share no hardware or software.

Applications perceive the pool of databases as a single logical database. Applications can elastically scale data, transactions, and users to any level, on any platform, by adding databases (shards) to the pool. Oracle Database supports scaling up to 1000 shards.

The following figure illustrates the major architectural components of Oracle Sharding:

• Sharded database (SDB) – a single logical Oracle Database that is horizontally partitioned across a pool of physical Oracle Databases (shards) that share no hardware or software

• Shards - independent physical Oracle databases that host a subset of the sharded database

• Global service - database services that provide access to data in an SDB

• Shard catalog – an Oracle Database that supports automated shard deployment, centralized management of a sharded database, and multi-shard queries

• Shard directors – network listeners that enable high performance connection routing based on a sharding key

• Connection pools - at runtime, act as shard directors by routing database requests across pooled connections

• Management interfaces - GDSCTL (command-line utility) and Oracle Enterprise Manager (GUI)

Oracle Sharding is for OLTP applications that are suitable for a sharded database.

Existing applications that were never intended to be sharded will require some level of redesign to achieve the benefits of a sharded architecture. In some cases it may be as simple as providing the sharding key, in other cases it may be impossible to horizontally partition data and workload as required by a sharded database.

Many customer-facing web applications, such as e-commerce, mobile, and social media are well suited to sharding. Such applications have a well defined data model and data distribution strategy (hash, range, list, or composite) and primarily access data using a sharding key. Examples of sharding keys include customer ID, account number, and country_id. Applications will also usually require partial de-normalization of data to perform well with sharding.

OLTP transactions that access data associated with a single value of the sharding key are the primary use-case for a sharded database, such as lookup and update of a customer’s records, subscriber documents, financial transactions, e-commerce transactions, and the like. Because all the rows in a sharded schema that have the same value of the sharding key are guaranteed to be on the same shard, such transactions are always single-shard and executed with the highest performance and provide the highest level of consistency. Multi-shard operations are supported, but with a reduced level of performance and consistency. Such transactions include simple aggregations, reporting, and the like, and play a minor role in a sharded application relative to workloads dominated by single-shard OLTP transactions.