Oracle 12.2 Sharding-1-basic concepts learning Notes

Oracle 12.2beta version was released in January (the latest internal release version was released in August, and is available for both windows and Linux). Currently, according to the introduction in the 12.2beta document, oracle Introduces sharding. Compared with other NOSQL sharding structures, Oracle Sharding provides enterprise-level RDBMS sharding technology.
.
Advantages of Oracle Sharding:
• Relational schemas
• Database partitioning
• ACID properties and read consistency
• SQL and other programmatic interfaces
• Complex data types
• Online schema changes
• Multi-core scalability
• Advanced security
• Compression
• High Availability features
• Enterprise-scale backup and recovery
.
Up to 1000 shards are supported in Oracle RDBMS 12.2.0.1.
.
Oracle Sharding uses the GDS (Global Data Services) architecture to automatically deploy and manage sharding and replication technologies.
GDS (a new feature of Oracle RDBMS 12.1) also provides location-based routing in server load balancer and SDB (sharded database.
.
The Shard Directory (Shard directors) uses the global service manager component of GDS framework to provide a direct route from application layer requests to shard. The shard Directory (Shard directors) is a separate database that stores the configuration data of SDB (Sharding database) and provides other related functions, such as cross-query and centralized management of shard. GDS is a GDSCTL tool that can be used to configure SDB.
Partition structure of Oracle Sharding)
Partitions are distributed across shards at the tablespace level. Each tablespace is associated with a specific shard. Each partition of a shard table is placed in a separate tablespace, and each tablespace is associated with a specific shard. Based on different sharding methods, this association can be automatically created or created according to definitions. Although multiple shards of a shard table are placed on databases of multiple individual hosts (these databases are completely independent and do not share software and hardware such as CPU and memory ), however, when an application accesses a table, it accesses a partition table in a separate database. The SQL statement sent by the application does not need to depend on the shard number and shard physical configuration.
.
Oracle Sharding uses familiar SQL syntax to create table partitions, specifying how each row of data in a partition table is partitioned.
The partition key of a shard table is called a sharding key. For example, the following syntax is typical for creating a sharding table:
CREATE SHARDED TABLE MERS
(Cust_id NUMBER NOT NULL
, Name VARCHAR2 (50)
And address VARCHAR2 (250)
, Region VARCHAR2 (20)
, Class VARCHAR2 (3)
, Signup DATE
CONSTRAINT cust_pk primary key (cust_id)
)
Partition by consistent hash (cust_id)
Tablespace set ts1
Partitions auto;
This data shard (shard) is based on the key value cust_id. The partition adopts the "consistent hash", which is a specific hash partition type and is usually used in distributed systems.
.
Sharding a Table Family
A Table without any parent table in a Table Family is called a root table. Each Table Family can have only one root table.
All tables in the table family are sharding based on the primary key of the root table. According to the structure of tables at all levels, the related data can be stored on the same shard.
In 12.2, only one table family is supported in an SDB.
.
The following example shows the Table Family composed of three tables)
Customer table, order table, and order list.
Each customer can have multiple orders, and each order can have multiple items. Therefore, the order details record multiple items in each order. Their specific data is as follows:
1
In this table family, the data numbered 123 is as follows:
2
.
Sharded Table Family is usually created using the following two methods:
Method 1: Do not display the specified parent-child relationship, but create a table family through the primary-foreign key relationship between tables. The table family created in this way is a multi-level tree structure.
The root table is a customer table:
-The primary key of the customer table is CustNo, and the partitioning method is "consistent hash (CustNo )"
-Save in the tablespace set ts1.
CREATE SHARDED TABLE MERS
(CustNo NUMBER NOT NULL
, Name VARCHAR2 (50)
And Address VARCHAR2 (250)
, CONSTRAINT RootPK primary key (CustNo)
)
Partition by consistent hash (CustNo)
PARTITIONS AUTO
Tablespace set ts1
;
-The Order table is the word table of the customer table. The subtable (order table) is associated with the parent table (customer table) according to CustNo ):
-The primary key of the order table is (CustNo, OrderNo). The foreign key (CustNo) references the primary table MERS (CustNo)
-The partition mode is based on the order table's foreign key constraints (CustFK)
Create sharded table Orders
(OrderNo NUMBER NOT NULL
, CustNo NUMBER NOT NULL
, OrderDate DATE
, CONSTRAINT OrderPK primary key (CustNo, OrderNo)
, CONSTRAINT CustFK foreign key (CustNo) references mers (CustNo)
)
Partition by reference (CustFK)
;
-The order list is the word table of the order table. The subtable (order list) is associated with the parent table (order table) according to CustNo)
-The primary keys of the order list are (CustNo, OrderNo, LineNo). The foreign keys (CustNo, OrderNo) reference the parent table Orders (OrderNo) and Orders (CustNo, OrderNo)
-The partitioning method is based on the order list's foreign key constraints (LineFK)
Create sharded table LineItems
(CustNo NUMBER NOT NULL
, LineNo NUMBER (2) NOT NULL
, OrderNo NUMBER (5) NOT NULL
, StockNo NUMBER (4)
, Quantity NUMBER (2)
, CONSTRAINT LinePK primary key (CustNo, OrderNo, LineNo)
, CONSTRAINT LineFK foreign key (CustNo, OrderNo) REFERENCES Orders (OrderNo)
REFERENCES Orders (CustNo, OrderNo)
)
Partition by reference (LineFK)
;
Therefore, in the preceding example, all data in this table family is stored in the same tablespace set ts1.
When a partition is added to the root table, the corresponding partition is automatically added to the associated table.
.
Method 2: Display the specified parent-child relationship in the partition table to create a table family.
This partitioning method only supports two levels of table families (two-level table families). All child tables must have the same parent table, the partition column of the parent table exists in each sub-table, for example, the following CustNo.
.
-The Root table without the keyword "PARENT" (or the constraint keyword referenced above) is the customer table (Mers MERS)
CREATE SHARDED TABLE MERS
(CustNo NUMBER NOT NULL
, Name VARCHAR2 (50)
And Address VARCHAR2 (250)
, Region VARCHAR2 (20)
, Class VARCHAR2 (3)
, Signup DATE
)
Partition by consistent hash (CustNo)
Tablespace set ts1
PARTITIONS AUTO
;
-Based on the keyword "PARENT Customers", the PARENT table of the order table (Orders) is the customer table (Mers MERS)
Create sharded table Orders
(OrderNo NUMBER
, CustNo NUMBER
, OrderDate DATE
)
PARENT MERS
Partition by consistent hash (CustNo)
Tablespace set ts1
PARTITIONS AUTO
;
-Based on the keyword "parent mers", the PARENT table of the order list (LineItems) is the customer table (Mers MERS)
Create sharded table LineItems
(LineNo NUMBER
, OrderNo NUMBER
, CustNo NUMBER
, StockNo NUMBER
, Quantity NUMBER
)
)
PARENT MERS
Partition by consistent hash (CustNo)
Tablespace set ts1
PARTITIONS AUTO
;
Creating a Duplicated Table Using CREATE TABLE
A duplicate Table can be copied to all shard tables. A Table with the same content on each shard is called a Duplicated Table)
Small tables that often need to be associated with the shard Table are suitable for copying the Table (Duplicated Table), applicable:
(1) read-only table
(2) a large number of cross-shard read operations
.
Oracle Sharding uses Materialized View Replication to synchronize the content of a table (duplicated tables)
The content of duplicated tables on each shard is a read-only materialized view (read-only materialized view .)
The main table of the materialized view (materialized views) is stored in a dedicated database called the shard catalog.
Materialized views on all shard instances are automatically refreshed based on the configured frequency.
The statement "create duplicated table" for creating a replication TABLE automatically creates the master TABLE, Materialized view, and other objects required for copying the Materialized view.
Take the above customer order relationship as an example. Here we define the product table (Products) as the copy table:
3
Create duplicated table Products
(StockNo NUMBER PRIMARY KEY
, Description VARCHAR2 (20)
, Price NUMBER (6, 2 ))
)
;
Based on the sharding mechanism, the sharding design has a very significant impact on the subsequent system performance.
Once sharding is created and there is already a lot of data, the relevant attributes cannot be modified, for example, whether a table is a copy table, a sharding table, or a sharding key.
Therefore, the design of SDB is crucial, and the re-design of sharding needs to be considered as follows:
Tables to be designed as sharding tables
Tables to be copied
Which shard tables are root tables?
How can I associate a table with another table or root table?
Which sharding method should be used?
Used seat sharding key