Sharding Methods in Oracle

Sharding Methods in Oracle

Sharding is a data tier architecture in which data is horizontally partitioned across independent databases. Each database in such a 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). Oracle Sharding is implemented based on the Oracle Database partitioning feature.

Sharding has following methods for implementation sharding in Oracle Database:

System Managed Sharding

User does not require specific mapping of data to shards. In this data is automatically distributed on basis of consistent hash.
The partitioning method automatically distributed data across shards.
It automatically maintain the balanced distribution of chunks when shards are added or removed from environment.

Example of Employee table created on Shard database. This table used partitioned by consistent hash method.
PARTITION AUTO in the statements means that number of partitions is automatically set in number of tablespace in tablespace set ts; ts is name of tablespace already created in Shard database.


CREATE TABLESPACE SET ts;

CREATE SHARDED TABLE employees
( emp_id NUMBER NOT NULL
, name VARCHAR2(50)
, address VARCHAR2(250)
, location_id VARCHAR2(20)
, class VARCHAR2(3)
, signup DATE
, CONSTRAINT cust_pk PRIMARY KEY(cust_id)
)
PARTITION BY CONSISTENT HASH (cust_id)
PARTITIONS AUTO
TABLESPACE SET ts;

Note: Each tablespace in tablespace set belong to distinct chunk.
Same tablespace is used for multiple table belong to same table family.

User Defined Sharding
In this you can define the mapping of data to individual shards. In this DBA manages & monitor the data across shards.

Example, In this user defined sharding , a sharded table has partitioned of data by range or list.
Example show us that tablespace is defined on each shared server separately. We have divided the shard database in 4 region : esst ,west, south and north.
A shardspace is set of shards that store data that corresponds to a range or list of key values. In this we need to create shardspace for define range in shard database wiht different shard(standalon database server)
First create shardspace cooresponding with shared server location

-- We have 4 shard db which we create sharedspace
ADD SHARDSPACE –SHARDSPACE west, north, east, south;
ADD SHARD –CONNECT shard-1 –SHARDSPACE west;
ADD SHARD –CONNECT shard-2 –SHARDSPACE norht;
ADD SHARD –CONNECT shard-3 –SHARDSPACE east;
ADD SHARD –CONNECT shard-4 –SHARDSPACE south;

Second, Create the shard tablespace as region on different shard server. All tablespace is different in all shard Server.

CREATE TABLESPACE tbs1 IN SHARDSPACE west;
CREATE TABLESPACE tbs2 IN SHARDSPACE North;
CREATE TABLESPACE tbs3 IN SHARDSPACE east;
CREATE TABLESPACE tbs4 IN SHARDSPACE south;

Third, Manually define the table and its partition value as range or list partition.

CREATE SHARDED TABLE accounts
( id NUMBER
, account_number NUMBER
, customer_id NUMBER
, branch_id NUMBER
, state VARCHAR(2) NOT NULL
, status VARCHAR2(1)
)
PARTITION BY LIST (state)
( PARTITION p_west VALUES ('OR', 'WA') TABLESPACE ts1
, PARTITION p_north VALUES ('SD', 'WI') TABLESPACE ts2
, PARTITION p_east VALUES ('NY', 'VM', 'NJ') TABLESPACE ts3
, PARTITION p_south VALUES ('FL', 'GA') TABLESPACE ts4)
;

Note: MOVE CHUNK command for each chunk that needs to be migrated. No chunk migration is automatically started when a shard is added to the SDB.
SPLIT CHUNK command, which is used to split a chunk in the middle of the hash range for system-managed sharding,

Composite Sharding

Composite Sharding is combination of user defined and system managed sharding. provides benefits of both methods.
Data is first partitioned by list or range across multiple shardspaces, and then further partitioned by consistent hash across multiple shards in each shardspace.
The two levels of sharding make it possible to automatically maintain balanced distribution of data across shards in each shardspace, and, at the same time, partition data across shardspaces.

Example: you can allocate the three shards to the gold class and two shards to the silver class customer.
In this case you can create two shardsspace for each catagory and assign tablespace to them and define the table according to you need with composite sharding.

First create the two shardspace and assign the server according to gold or silver catagory:

ADD SHARDSPACE –SHARDSPACE shspace1;
ADD SHARDSPACE –SHARDSPACE shspace2;
-- gold customer
ADD SHARD –CONNECT shard1 –SHARDSPACE shspace1;
ADD SHARD –CONNECT shard2 –SHARDSPACE shspace1;
ADD SHARD –CONNECT shard3 –SHARDSPACE shspace1;
-- Silver customer
ADD SHARD –CONNECT shard4 –SHARDSPACE shspace2;
ADD SHARD –CONNECT shard5 –SHARDSPACE shspace2;

Second, create two separate tablespace for them.

CREATE TABLESPACE SET tbs1 IN SHARDSPACE shspace1;
CREATE TABLESPACE SET tbs2 IN SHARDSPACE shspace2;

Third, Create table that use composite sharding

CREATE SHARDED TABLE customers
( cust_id NUMBER NOT NULL
, name VARCHAR2(50)
, address VARCHAR2(250)
, location_id VARCHAR2(20)
, class VARCHAR2(3)
, signup_date DATE
, CONSTRAINT cust_pk PRIMARY KEY(cust_id, class)
)
PARTITIONSET BY LIST (class)
PARTITION BY CONSISTENT HASH (cust_id)
PARTITIONS AUTO
(PARTITIONSET gold VALUES ('gld') TABLESPACE SET tbs1,
PARTITIONSET silver VALUES ('slv') TABLESPACE SET tbs2)
;

Subpartitions with Sharding
Subpartitioning splits each partition into smaller parts and may be beneficial for efficient parallel execution within a shard, especially in the case of sharding by range or list when the number of partitions per shard may be small.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s