The user-defined method considers that the user maps data to individual shards. This method is necessary when there is a strict requirement to have some type of data on a particular shard.
The benefit of this method is that the user knows what data becomes unavailable during a particular sharding failure. The disadvantage is that the database administrator (DBA) must maintain the balance distribution of the data.
Partitioning can be performed either by list or by range in Oracle Sharding. It is important to note that no tablespace set is used; the method requires the manual creation of tablespaces associated with the shardspace. A shardspace is a set of shards that store data corresponding to a range or list of key values.
The DBA must create shardspaces using the GDSCTL command and populate the command with specific shards, as shown here:
GDSCTL> ADD SHARDSPACE -SHARDSPACE east
GDSCTL> ADD SHARDSPACE -SHARDSPACE west
GDSCTL> ADD CDB -CONNECT cdb1
GDSCTL> ADD CDB -CONNECT cdb2
GDSCTL> ADD SHARD –CONNECT shard1 -CDB cdb1 –SHARDSPACE east; GDSCTL> ADD SHARD –CONNECT shard2 -CDB cdb2 –SHARDSPACE west;
Tablespaces must be created individually and explicitly associated with a shardspace.
SQL> CREATE TABLESPACE ts1 IN SHARDSPACE east;
SQL> CREATE TABLESPACE ts2 IN SHARDSPACE west;
The following is the SQL statement for creating a user-defined table:
CREATE SHARDED TABLE int_students
( id NUMBER
- student_number NUMBER
…
, state VARCHAR(2) NOT NULL
)
PARTITION BY LIST (state)
( PARTITION p_northeast VALUES (‘NY’, ‘VM’, ‘NJ’) TABLESPACE ts1
- PARTITION p_southeast VALUES (‘FL’, ‘GA’) TABLESPACE ts1
- PARTITION p_northwest VALUES (‘OR’, ‘WA’) TABLESPACE ts2
- PARTITION p_southwest VALUES (‘AZ’, ‘UT’, ‘NM’) TABLESPACE ts2
);
Composite sharding is a combination of the system-managed and user-defined sharding methods.
In this method, data is first partitioned by list or by range across shardspaces and then partitioned by a consistent hash algorithm across multiple shards in each shardspace.
Using GDSCTL, the DBA must create shardspaces and populate them with specific shards.
GDSCTL> ADD SHARDSPACE –SHARDSPACE shspace1;
GDSCTL> ADD SHARDSPACE –SHARDSPACE shspace2;
GDSCTL> ADD CDB -CONNECT cdb1
GDSCTL> ADD CDB -CONNECT cdb2
GDSCTL> ADD SHARD –CONNECT shard1 -CDB cdb1 –SHARDSPACE shspace1; GDSCTL> ADD SHARD –CONNECT shard2 -CDB cdb2 –SHARDSPACE shspace2;
A separate tablespace set must be created in each shardspace.
CREATE TABLESPACE SET ts1 IN SHARDSPACE shspace1;
CREATE TABLESPACE SET ts2 IN SHARDSPACE shspace2;
The following is an example of creating a table using this method:
CREATE SHARDED TABLE int_students
( id NUMBER
- student_number NUMBER
…
, state VARCHAR(2) NOT NULL
)
PARTITION BY LIST (state)
PARTITION BY CONSISTENT HASH (id)
PARTITIONS AUTO
(PARTITIONSET p_northeast VALUES (‘NY’, ‘VM’, ‘NJ’) TABLESPACE SET ts1
,PARTITIONSET p_southeast VALUES (‘FL’, ‘GA’) TABLESPACE SET ts1
,PARTITIONSET p_northwest VALUES (‘OR’, ‘WA’) TABLESPACE SET ts2
,PARTITIONSET p_southwest VALUES (‘AZ’, ‘UT’, ‘NM’) TABLESPACE SET ts2
);