Understanding Oracle Sharding: Table Families, Chunks, and Deployment

(1) Table Family

A table family groups related tables such as customers, order, and LineItems that share foreign‑key relationships. Two ways to define the family are:

Using

CONSTRAINT [FK_name] FOREIGN KEY (FK_column) REFERENCES [R_table_name]([R_table_column])

, which can cascade.

Using the PARENT keyword to explicitly state a parent‑child link; this supports only a single‑level relationship without cascade.

In the example, the root table is customers (partitioned by CONSISTENT HASH on CustId), the next level is order (partitioned by the foreign key CustId), and the third level is LineItems (partitioned by the composite foreign key CustId+OrderId).

(2) Sharded Table vs Duplicated Table

Sharded tables have their partitions distributed across different shard nodes, effectively slicing the table’s data across machines. Duplicated tables contain the same data on every node, implemented as read‑only materialized views; synchronization is performed via materialized view refresh.

(3) Chunk

A chunk is a single partition from each table in a table family, guaranteeing that related rows from different sharded tables can be moved together. For example, customers 1‑1,000,000 and their corresponding orders and line items reside in the same chunk, allowing re‑sharding to move the whole group and avoid cross‑shard joins.

Chunk count is defined in CREATE SHARDCATALOG; if omitted, the default is 120 chunks per shard.

(4) Chunk Move

Chunk move occurs when re‑sharding changes the number of shards or when a DBA manually initiates it (e.g., move chunk -chunk 7 -source sh2 -target sh1).

During migration, chunks are mostly online; a brief period makes the chunk read‑only. The migration process combines RMAN incremental backup with TTS:

level 0 backup of source chunk TS → restore to new shard → FAN wait → set source TS read‑only → level 1 backup/restore → update routing table to new shard (chunk up) → update routing table to remove source shard (chunk down) → FAN wait → delete old chunk on source shard

(5) Shardspace

When creating a tablespace set, a shardspace can be specified; this is primarily used in composite sharding architectures that employ multiple shardspaces.

(6) Sharding Methods

Two main methods are supported:

System‑Managed Sharding : partitions data by consistent hash, spreading it across shards.

Composite Sharding : creates multiple shard groups for different data subsets, allowing different shardspaces, tablespace sets, and hardware resources.

All Oracle sub‑partitioning techniques are also available for finer‑grained partitioning.

(7) Deploying Sharding

Deployment is performed via gsdctl commands:

CREATE SHARDCATALOG

ADD GSM; START GSM

(create and start shard directors) CREATE SHARD for each shard

DEPLOY