TimesTen 应用层数据库缓存学习：18. 利用TimesTen实现Sharding或数据分区

), id int, city varchar2(20), primary key(id) ) partition by list(region) ( partition part_1 values ( ‘N‘ ), partition part_2 values ( ‘S‘ ), partition part_3 values ( ‘W‘ ), partition part_4 values ( ‘E‘ ) ); insert into datapart values (‘N‘, 1, ‘Beijing‘); insert into datapart values (‘S‘, 2, ‘Guangzhou‘); insert into datapart values (‘E‘, 3, ‘Shanghai‘); insert into datapart values (‘W‘, 4, ‘Chengdu‘); SQL> select * from datapart partition(part_2); R ID CITY - ---------- -------------------- S 2 Guangzhou grant select, delete, update, insert on datapart to cacheadm;

分别在两个TimesTen数据库cachedb1和cachedb2中建立Cache Group，缓存北区和南区的数据

cachedb1>
CREATE READONLY CACHE GROUP "DATAPART1" 
 AUTOREFRESH MODE INCREMENTAL INTERVAL 5 SECONDS
 STATE ON
 FROM
  "TTHR"."DATAPART" (
    "REGION" VARCHAR2(1 BYTE) ,
    "ID"     NUMBER(38)        NOT NULL,
    "CITY"   VARCHAR2(20 BYTE),
    PRIMARY KEY("ID")
  )
    WHERE (REGION=‘N‘)

cachedb1> select * from datapart;
< N, Beijing >

cachedb2>
CREATE READONLY CACHE GROUP "DATAPART2" 
 AUTOREFRESH MODE INCREMENTAL INTERVAL 5 SECONDS
 STATE ON
 FROM
  "TTHR"."DATAPART" (
    "REGION" VARCHAR2(1 BYTE) ,
    "ID"     NUMBER(38)        NOT NULL,
    "CITY"   VARCHAR2(20 BYTE),
    PRIMARY KEY("ID")
  )
    WHERE (REGION=‘S‘)

cachedb2> select * from datapart;
< S, Guangzhou >

在Oracle中插入数据

insert into datapart values (‘N‘, 5, ‘Qingdao‘);
insert into datapart values (‘S‘, 6, ‘Haikou‘);

然后在两个TimesTen中可自动得到新数据

cachedb1> select * from datapart;
< N, 1, Beijing >
< N, 5, Qingdao >

cachedb2> select * from datapart;
< S, 2, Guangzhou >
< S, 6, Haikou >

可写分区

可写分区以AWT为例，和只读分区不一样，AWT在建立时不能指定where条件，但是可以在LOAD操作时指定where条件来进行数据分区

在cachedb1中建立Cache Group, LOAD时指定缓存北区的数据:

CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP "AWT1" 
 FROM
  "TTHR"."DATAPART" (
    "REGION" VARCHAR2(1 BYTE) ,
    "ID"     NUMBER(38)        NOT NULL,
    "CITY"   VARCHAR2(20 BYTE),
    PRIMARY KEY("ID")
  )

cachedb1> call ttrepstart;

cachedb1> load cache group awt1 where region = ‘N‘ commit every 256 rows;
cachedb1> select * from datapart;
< N, 1, Beijing >
< N, 5, Qingdao >

cachedb1> truncate table datapart;
 8238: Cannot truncate cache group table DATAPART; please use DROP/REFRESH/UNLOAD CACHE GROUP instead

insert into datapart values (‘N‘, 7, ‘Tianjing‘);

在cachedb2中建立Cache Group, LOAD时指定缓存南区的数据:

CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP "AWT2" 
 FROM
  "TTHR"."DATAPART" (
    "REGION" VARCHAR2(1 BYTE) ,
    "ID"     NUMBER(38)        NOT NULL,
    "CITY"   VARCHAR2(20 BYTE),
    PRIMARY KEY("ID")
  )

cachedb2> call ttrepstart;

cachedb2> load cache group awt2 where region = ‘S‘ commit every 256 rows;
cachedb2> select * from datapart;
< S, 2, Guangzhou >
< S, 6, Haikou >
cachedb2> 

insert into datapart values (‘S‘, 8, ‘Changsha‘);

在Oracle中可以看到由各个TimesTen新插入的数据

SQL> select * from datapart order by id; 

R         ID CITY
- ---------- --------------------
N          1 Beijing
S          2 Guangzhou
E          3 Shanghai
W          4 Chengdu
N          5 Qingdao
S          6 Haikou
N          7 Tianjing
S          8 Changsha

8 rows selected.

在Oracle中插入数据，然后各个缓存组通过LOAD/REFRESH操作得到新数据。
注意，对于Explicitly load的缓存组，LOAD和REFRESH的作用是一样的。

SQL>
insert into datapart values (‘N‘, 9, ‘Baoding‘);
insert into datapart values (‘S‘, 10, ‘Wuhan‘);
commit;

cachedb1> select * from datapart;
< N, 1, Beijing >
< N, 5, Qingdao >
< N, 7, Tianjing >

cachedb1> refresh cache group awt1 where region = ‘N‘ commit every 256 rows;
cachedb1> select * from datapart;
< N, 1, Beijing >
< N, 5, Qingdao >
< N, 7, Tianjing >
< N, 9, Baoding >

cachedb2> select * from datapart;
< S, 2, Guangzhou >
< S, 6, Haikou >
< S, 8, Changsha >

cachedb2> load cache group awt2 where region = ‘S‘ commit every 256 rows;

cachedb2> select * from datapart;
< S, 2, Guangzhou >
< S, 6, Haikou >
< S, 8, Changsha >
< S, 10, Wuhan >

参考

https://community.oracle.com/thread/3594994?start=0&tstart=0

TimesTen 应用层数据库缓存学习：18. 利用TimesTen实现Sharding或数据分区

标签：