Worked in Database technology for fixed the issues faced in daily activities in Oracle, Sql Server, MySQL etc.

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: 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))
);