Orcle 12C Sharing--Introduction to Sharded and Duplicated tables

1 Description

There are two types of tables in SDB: sharded tables and duplicated tables.

  • Shared tables are equi-partitioned on a sharding key. --Store data on each shards separately with the sharding key.
    S=S1 U S2 U ... U Sn

  • Duplicated tables are identical on all shards. - A database on each shards is a complete piece of data.
    R = R1 = ... = Rn

Set up the Sharing database: https://blog.csdn.net/qianglei6077/article/details/90405726

2 Experiments

2.1 Create the SHARDED table to insert data

SQL> CREATE SHARDED TABLE LEI
(USERID NUMBER NOT NULL
,NAME VARCHAR2(30)
)
PARTITION BY CONSISTENT HASH (USERID)
PARTITIONS AUTO
TABLESPACE SET TPS_1;

Table created.

insert into LEI values(1,'sihong');
insert into LEI values(2,'suyi');
insert into LEI values(3,'ruyan');
insert into LEI values(4,'dongsheng');
insert into LEI values(5,'wenqing');
commit;

SQL> select * from lei;
    USERID NAME
---------- ------------------------------
 5 wenqing
 2 suyi
 1 sihong
 3 ruyan
 4 dongsheng

- shard Node 1 Query

SQL> select * from lei;
    USERID NAME
---------- ------------------------------
 5 wenqing
 2 suyi

- shard Node 2 Query

SQL> select * from lei;

    USERID NAME
---------- ------------------------------
 1 sihong
 3 ruyan
 4 dongsheng

2.2 Create a duplicated table

For experimentation, just change the table type

SQL> CREATE DUPLICATED  TABLE CNDBA
(USERID NUMBER NOT NULL
,NAME VARCHAR2(30)
);

Table created.

insert into CNDBA values(1,'sihong');
insert into CNDBA values(2,'suyi');
insert into CNDBA values(3,'ruyan');
insert into CNDBA values(4,'dongsheng');
insert into CNDBA values(5,'wenqing');
commit;

- shard Node 1 Query

SQL> select * from cndba;
    USERID NAME
---------- ------------------------------
 1 sihong
 2 suyi
 4 dongsheng
 5 wenqing
 3 ruyan

- shard Node 2 Query

SQL> select * from cndba;
    USERID NAME
---------- ------------------------------
 1 sihong
 2 suyi
 4 dongsheng
 5 wenqing
 3 ruyan

You can see that the data on each shard node is the same and is a complete data.

Tags: SQL Database

Posted on Thu, 07 Nov 2019 13:04:06 -0800 by marcela1637