Sharding Database in Oracle

Sharding Database 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.

SHARDED DATABASE:
Database is host independently with all resources like CPU, disk, memory as standalone server. This database is called shared.
All together make a single logical database called sharded database.
SHAREDED TABLE:
Splitting a 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.
A sharded table is a table that is partitioned into smaller and more manageable pieces among multiple databases, called shards.

Note: Oracle Database supports scaling up to 1000 shards.

Terms Used in Shard Environment

Sharded database (SDB) – A SDB is collection of shards represent as a single logical Oracle Database.
Shards – independent physical Oracle databases
Global service – database services that provide access to data in an SDB
Shard catalog – Stored SDB configuration data and manage centralized of sharded database. shard catalog also contains the master copy of all duplicated tables in an SDB. The shard catalog uses materialized views to automatically replicate changes to duplicated tables in all shards.
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)

Defining and creating process of Objects in Shared

Shared table
A sharded table is a table that is partitioned into smaller and more manageable pieces among multiple databases, called shards.

Example
creates a sharded table having horizontally partitioning the table across shards based on sharding key cust_id:


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

Note: Global index is not supported in Shared.
Oracle Sharding creates and manages tablespaces as a unit called a tablespace set.
The PARTITIONS AUTO clause specifies that the number of partitions should be automatically determined.

CHUNK
The unit of data migration between shards is a chunk. A chunk is a set of tablespaces that store corresponding partitions of all tables in a table family.

Table Family
parent-child relationship between database tables with a referential constraint (foreign key). A set of such tables is referred to as a table family. A
table in a table family that has no parent is called the root table.
In this example customer table is parent and order table is child

CREATE SHARDED TABLE Customers
( CustNo NUMBER NOT NULL
, Name VARCHAR2(50)
, Address VARCHAR2(250)
, region VARCHAR2(20)
, class VARCHAR2(3)
, signup DATE
)
PARTITION BY CONSISTENT HASH (CustNo)
PARTITIONS AUTO
TABLESPACE SET ts1
;
CREATE SHARDED TABLE Orders
( OrderNo NUMBER
, CustNo NUMBER NOT NULL
, OrderDate DATE
)
PARENT Customers
PARTITION BY CONSISTENT HASH (CustNo)
PARTITIONS AUTO
TABLESPACE SET ts1
;

Duplicate Table
A table with the same contents in each shard is called a duplicated table. It simple presents on all the database(shards)
Oracle Sharding synchronizes the contents of duplicated tables using Materialized View Replication. A duplicated table on each shard is represented by a materialized view.
Note: refresh frequency of all duplicated tables is controlled by the database initialization parameter SHRD_DUPL_TABLE_REFRESH_RATE. Default value is 60 seconds

CREATE DUPLICATED TABLE Products
( StockNo NUMBER PRIMARY KEY
, Description VARCHAR2(20)
, Price NUMBER(6,2))
);

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.