Oracle_12c_Sharding功能PL使用调研

发表于 | 分类于 | 次

环境信息

1
2
3
4
5
6
7
8
9
10
11
12
master：
100.88.11.91 1521 SID=catadb
user:app_schema   password:oracle
system:oracle

shard1:
100.88.11.96 1521 SID=sh1
user:app_schema   password:oracle

shard2:
100.88.8.49 1521 SID=sh2
user:app_schema   password:oracle

PL使用调研

Oracle Sharding

实验环境中Sharding集群有由台机器组成，一台master，两台shard。集群中每一台机器都独立安装OracleSingleinstance database installation版本，也就是说每台机器都可以当做Oracle单节点使用。master节点上多安装了GSM（Global Service Mananger），GSM类似于监听器，将客户端对SDB的请求路由到对应的shard，通过它来实现对Sharding的集中部署和管理。从安装层面可以看出，如果想要使用Oracle Sharding的新特性，就一定需要在master上面进行操作。

分区在表空间级别跨shards分布，每个表空间关联一个特定的shard。一个shard表的每一个分区放单独的表空间，并且每个表空间关联到一个特定的shard。根据不同的sharding方法，这个关联可以自动建立或者根据定义创建。尽管一个shard表的多个分区放在多个单独主机的数据库上（这些数据库完全独立，不共享CPU、内存等软件和硬件），但是应用访问表时就如同访问一个单独数据库中的分区表一样。应用发出的SQL语句不需要依赖shard号和shard的物理配置。

Oracle Sharding的方法:
Oracle Sharding支持3种方法shard/分片方法：
System-Managed Sharding:这种方法用户不用指定数据存放在哪个shard中。Sharding通过一致性哈希(CONSISTENT HASH)方法将数据分区(partitioning)，并自动分布在不同的Shard。System-managed sharding只能有一个shardspace.
Composite Sharding: 这种方法用户创建多个shardspaces ，每个shardspaces 中存放一定范围(range)或者列表(list)的数据。一般情况下，Shardspace按照区域来划分，比如美国区域的shard属于shardspace cust_america，欧洲的shard属于shardspace cust_europe。
Subpartitions with Sharding: Sharding基于表分区，因此子分区(Subpartitions)技术同样适用于Sharding

建表

Sharding新特性中出现两种新的表模式（sharded table和duplicated table）。在一个session中，需要先执行alter session enable shard ddl;来开启shard ddl操作。在master上创建sharded table和duplicated table时，相同定义的table会出现在shard节点上。在master或者shard上面创建none-sharded table，那么这些table仅出现在单机上。

  • 创建表空间集合
1
2
3
CREATE TABLESPACE SET TSP_SET_1 using template (datafile size 100m autoextend on next 10M maxsize unlimited extent management local segment space management auto);

CREATE TABLESPACE products_tsp datafile size 100m autoextend on next 10M maxsize unlimited extent management local uniform size 1m;
  • 创建sharded table
1
2
3
4
5
6
7
8
9
10
CREATE SHARDED TABLE Customers
  (
    CustId      VARCHAR2(60) NOT NULL,
    FirstName   VARCHAR2(60),
    LastName    VARCHAR2(60),
    Sal         NUMBER,
    Zone        NUMBER,
    CONSTRAINT pk_customers PRIMARY KEY (CustId)
  ) TABLESPACE SET TSP_SET_1
PARTITION BY CONSISTENT HASH (CustId) PARTITIONS AUTO;
  • 创建duplicated table
1
2
3
4
5
6
7
CREATE DUPLICATED TABLE Products
  (
    ProductId  INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    Name       VARCHAR2(128),
    DescrUri   VARCHAR2(128),
    LastPrice  NUMBER(19,4)
  ) TABLESPACE products_tsp;

复制表可以被复制到所有的shard上，这种在每个shard上有相同内容的表叫做复制表（Duplicated Table）
需要经常跟shard表关联的小表适合于作为复制表（Duplicated Table），适用于：
（1）只读表
（2）大量跨shard的读操作

Note:

In Oracle Database 12c Release 2, only a single table family is supported in an SDB.

一个表家族（Table Family）中没有任何父表的表叫做根表（root table），每个表家族中只能有一个根表。

分区键（Sharding Key）必须是UNIQUE索引的关键字列的关键字子集

限制条件：
1、分区键必须是唯一性约束的（主键或者唯一索引）
2、分区键只能指定一列
3、不能既有主键定义，又有唯一索引定义

子表的分区键需要以root table的分区键做为外键

1
2
3
4
5
6
7
8
9
10
11
CREATE SHARDED TABLE Orders
  (
    OrderId     INTEGER NOT NULL,
    CustId      VARCHAR2(60) NOT NULL,
    OrderDate   TIMESTAMP NOT NULL,
    SumTotal    NUMBER(19,4),
    Status      CHAR(4),
    CONSTRAINT  pk_orders PRIMARY KEY (CustId, OrderId),
    CONSTRAINT  fk_orders_parent FOREIGN KEY (CustId) 
    REFERENCES Customers ON DELETE CASCADE
  ) PARTITION BY REFERENCE (fk_orders_parent);

Procedure / Function创建

在master节点上创建Procedure或者Function，shard节点上会自动创建相同的Procedure或者Function。在shard节点上创建Procedure或者Function，这些Procedure或者Function只会存在shard节点上，不会同步到其他节点上。

Insert操作

在master节点上插入数据，对于sharded table，数据会分布到shard节点上，在master上可以看到全部数据，shard节点上只能看到属于该shard节点上的数据；对于duplicated table，每个shard节点上都有一个完整数据的副本。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create or replace PROCEDURE sp_insert(custid VARCHAR2, firstname VARCHAR2, sal number, zone number) IS
BEGIN
    INSERT into CUSTOMERS (CUSTID, FIRSTNAME, Sal, Zone) values (custid, firstname, sal, zone);
END;

create or replace PROCEDURE insert_batch(custid VARCHAR2, firstname VARCHAR2, sal number, zone number) IS
BEGIN
    INSERT into CUSTOMERS (CUSTID, FIRSTNAME, Sal, Zone) values (custid, firstname, sal, zone);
    INSERT into CUSTOMERS (CUSTID, FIRSTNAME, Sal, Zone) values ('40', 'a', 3000, 0);
    INSERT into CUSTOMERS (CUSTID, FIRSTNAME, Sal, Zone) values ('41', 'a', 4000, 1);
    INSERT into CUSTOMERS (CUSTID, FIRSTNAME, Sal, Zone) values ('42', 'a', 5000, 2);
END;

SQL> call sp_insert('39', 'b', 2000, 1);
SQL> call insert_batch('39', 'b', 2000, 1);

可以看出，PL可以实现对不同shard的插入操作。

先在shard2上插入一行数据，然后删除，把相同的数据插入到shard1上，发现本来应该属于shard2上的数据，并不能插入到shard1上面

1
2
3
4
shard2 SQL> insert into Customers (CustId, Zone) values ('6', 0);
shard2 SQL> delete from Customers where Custid = '6';
shard1 SQL> insert into Customers (CustId, Zone) values ('6', 0);
shard1 SQL> ORA-14466: 无法修改只读分区或子分区中的数据。

Select操作

  • 普通查询

通过执行Select解释计划发现，master上执行的是remote计划。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
--单条查询
create or replace PROCEDURE sp_query(name VARCHAR2) AS
id VARCHAR(60);
begin
select CUSTID into id from CUSTOMERS WHERE FIRSTNAME = name;
dbms_output.put_line(id);
end;

--带游标的多条查询
create or replace PROCEDURE sp_query_all AS
TYPE sp_cursor is REF CURSOR;
id CUSTOMERS.CUSTID%TYPE;
sp sp_cursor;
BEGIN
OPEN sp FOR select CUSTID into id from CUSTOMERS; 
FOR cur IN (select CUSTID into id from CUSTOMERS) LOOP
    dbms_output.put_line(': ' || cur.CUSTID);
END LOOP;
END;

--执行
SQL> call SP_QUERY('10a');
SQL> call SP_QUERY_ALL();

因此，单条的查询和游标及循环操作在Sharding的PL是支持的。

  • 连接查询

使用建表部分中建立的Customers和Orders两个表，插入一些数据，做连接测试。

1
2
3
4
5
6
7
8
9
insert into Customers (CustId, FIRSTNAME, SAL, ZONE) values ('a100', 'a', 1000, 0);
insert into Customers (CustId, FIRSTNAME, SAL, ZONE) values ('b100', 'b', 2000, 1);
insert into Customers (CustId, FIRSTNAME, SAL, ZONE) values ('c100', 'c', 3000, 2);
insert into Customers (CustId, FIRSTNAME, SAL, ZONE) values ('d100', 'd', 2000, 3);

insert into Orders (OrderId, CustId, OrderDate, SumTotal, Status) Values (1001, 'a100', to_timestamp('2013-9-16 17:03:00.29999', 'yyyy-mm-dd hh24:mi:ss:ff'), 10, 0);
insert into Orders (OrderId, CustId, OrderDate, SumTotal, Status) Values (1002, 'a100', to_timestamp('2014-9-16 17:03:00.29999', 'yyyy-mm-dd hh24:mi:ss:ff'), 20, 0);
insert into Orders (OrderId, CustId, OrderDate, SumTotal, Status) Values (1003, 'a100', to_timestamp('2015-9-16 17:03:00.29999', 'yyyy-mm-dd hh24:mi:ss:ff'), 30, 0);
insert into Orders (OrderId, CustId, OrderDate, SumTotal, Status) Values (1004, 'd100', to_timestamp('2015-9-16 17:03:00.29999', 'yyyy-mm-dd hh24:mi:ss:ff'), 30, 0);

执行上述操作后，发现数据分散到shard1和shard2上面，在master进行join操作。

1
2
3
4
5
6
7
8
SQL> select * from Customers join Orders using(CustId);
SQL> select * from Customers left join Orders using (CustId);
SQL> select * from Customers join Orders on Customers.Zone = Orders.Status and Customers.Custid = Orders.Custid;

SQL> select * from Customers join Orders on Customers.Zone = Orders.Status;
SQL> ORA-02674: 分片关键字上的联接谓词非法
SQL> select * from Customers, Orders;
SQL> ORA-02674: 分片关键字上的联接谓词非法

发现连接的条件必须含有分区键。

Update操作

在master节点上更新数据，update的等值条件必须是分区键，否则会提示ORA-02671: 不允许在多个分片上使用 DML语法错误；而shard节点上对update的等值条件无特殊限制。

1
2
3
4
5
6
7
8
9
10
11
--分区键更新
create or replace PROCEDURE sp_update(id VARCHAR2, name VARCHAR2) IS
BEGIN
    update CUSTOMERS set FIRSTNAME = name where CUSTID = id;
END;

--非分区键更新
create or replace PROCEDURE sp_update2(fname VARCHAR2, lname VARCHAR2) IS
BEGIN
    update CUSTOMERS set FIRSTNAME = fname where LASTNAME = lname;
END;

其中非分区键更新Procedure只能在shard节点上正确运行。

Delete操作

使用限制类似于Update，master上删除数据的等值条件必须是分区键。

Function执行

  • 创建一个根据主键查询FIRSTNAME的Function，并在另一个Procedure中调用该Function，执行替换该条数据的LASTNAME为FIRSTNAME的业务逻辑
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create or replace FUNCTION fetch_fname(id VARCHAR2) RETURN VARCHAR2 AS
fname VARCHAR2(20);
BEGIN
    select FIRSTNAME into fname from Customers where Custid = id;
    RETURN fname;
END;

create or replace procedure duplicate_fname2lname(id varchar2) AS
fname varchar2(20);
lname varchar2(20);
begin
lname := Fetch_Fname(id);
Update_Lname(id, lname);
end;

SQL> call duplicate_fname2lname('14');
  • 在select中执行自定义Function，master上不支持提示ORA-02670: 不受支持的 SQL 构造: PL/SQL function异常；shard节点上可以正确执行
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--定义一个计算税率的函数
create or replace function sal_tax (v_sal number) 
return number 
is 
    begin 
        if(v_sal < 2000) then 
            return 0.10; 
        elsif(v_sal < 2750) then 
            return 0.15; 
        else 
            return 0.20; 
        end if; 
    end;

--执行
master SQL> select Sal_Tax(sal) from Customers;
master SQL> ORA-02670: 不受支持的 SQL 构造: PL/SQL function
shard SQL> select Sal_Tax(sal) from Customers;
shard SQL> successful

分布式事务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create or replace PROCEDURE update_sal(id VARCHAR2, sal_ number) IS
BEGIN
    update CUSTOMERS set sal = sal_ where CUSTID = id;
END;

--执行
begin
update_sal('10', 0);
update_sal('16', 0);
savepoint A;
update_sal('40', 0);
rollback to savepoint A;
commit;
end;

Custid为10或40的数据分布在shard1节点上，Custid为16的数据分布在shard2节点上。该涉及分布式事务的匿名块可以在master节点上正确执行。

程序包

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
--创建包的声明部分——包头
create or replace package pkg as
    b varchar2(10);
    c_id constant number := 100;
    procedure sp_insert(custid VARCHAR2, firstname VARCHAR2, sal number, zone number);
    function fetch_fname(id VARCHAR2) RETURN VARCHAR2;
end;

--创建包的定义部分——包体
create or replace package body pkg as
    default_salary constant int := 5000;    --package的私有变量
    PROCEDURE sp_insert(custid VARCHAR2, firstname VARCHAR2, sal number, zone number) IS
        BEGIN
            INSERT into CUSTOMERS (CUSTID, FIRSTNAME, Sal, Zone) values (custid, firstname, sal, zone);
        END;
    FUNCTION fetch_fname(id VARCHAR2) RETURN VARCHAR2 AS
        fname VARCHAR2(20);
        BEGIN
            select FIRSTNAME into fname from Customers where Custid = id;
        RETURN fname;
        END;
END;

--执行
SQL> call pkg.sp_insert('50', 'A', 2000, 0);

begin
DBMS_OUTPUT.put_line(Pkg.Fetch_Fname('10'));
DBMS_OUTPUT.put_line(Pkg.c_id);
end;

package中可以定义变量、procedure和function。

分享到： 收藏夹 复制网址 邮件 微信 QQ空间 腾讯微博 豆瓣 一键分享 更多