What is the difference between sharding and partitioning in Oracle 19c?

Both sharding and partitioning are techniques for dividing large tables into smaller pieces. However, they differ in how the data is divided and how it is managed.

Sharding

Sharding divides data by logical units called shards. Each shard is stored on a separate Oracle Database instance. When a user queries a sharded table, the Oracle Database will automatically route the query to the shard that contains the requested data.

Sharding can be used to improve performance, scalability, and resilience for applications. For example, if you have an application that queries a customer table with millions of rows, you can use sharding to improve the performance of the queries by distributing the data across multiple Oracle Database instances.

Partitioning

Partitioning divides data by physical units called partitions. Each partition is stored on the same Oracle Database instance. When a user queries a partitioned table, the Oracle Database will automatically route the query to the partition that contains the requested data.

Partitioning can be used to improve performance, manageability, and compliance for applications. For example, if you have an application that needs to be able to handle a high volume of updates, you can use partitioning to improve the performance of the updates by dividing the table into smaller partitions.

Comparison of sharding and partitioning

The following table compares sharding and partitioning:

Feature	Sharding	Partitioning
Data division	Logical	Physical
Data storage	Multiple Oracle Database instances	Single Oracle Database instance
Query routing	Automatic	Manual
Benefits	Improved performance, scalability, and resilience	Improved performance, manageability, and compliance
Drawbacks	Complexity, cost	Complexity, data movement

Which one to use?

The best approach for dividing your data depends on your specific needs. If you need to improve performance, scalability, and resilience, then sharding is a good option. If you need to improve performance, manageability, and compliance, then partitioning is a good option.

Conclusion

Sharding and partitioning are both powerful techniques for dividing large tables into smaller pieces. However, they differ in how the data is divided and how it is managed. The best approach for dividing your data depends on your specific needs.