TimesTen Application Layer Database Cache learning: 18. Using TimesTen to implement sharding or data partitioning

Given the speed of recovery and disk loading to memory, the data that a single TimesTen database can cache is typically no more than 100G, and if you need to cache large data, you can generally use multiple timesten to achieve data partitioning or sharding (sharding).
Although the TimesTen has the concept of the cache grid, which is the global cache group, it is generally a manual partitioning approach that is directed to the specified TimesTen through the application server because it is not yet mature.

Examples of read-only and read-write data partitioning are given below.

It is also emphasized that TimesTen does not have the concept of Oracle partition.

TimesTen can cache Oracle Database partitioned tables at the table level, but individual partitions cannot is cached. The following describes how operations on partitioned tables affect cache groups:
DDL operations on a table, the have partitions do not affect the cache group unless there is data loss. For example, if a partition with data are truncated, an AutoRefresh operation does not delete the data from the Correspondi Ng cached table.
WHERE clauses in any cache group operations cannot reference individual partitions or subpartitions. Any attempt to define a single partition of a table returns an error.

Read-only partition

A read-only partition can be used to specify the convenience of the Where condition, and a cache group with a where condition can be created in different timesten to buffer data from different parts of the Oracle database.
The following example, although the table in Oracle has partitions, is only an example, even if no partition results.

Build a table in Oracle, divided into four areas of east

CREATE TABLE Datapart (Region varchar2 (1), IDint, City Varchar2 ( -), primary key (ID)) partition by list (partition part_1Values(' N '), Partition part_2Values(' S '), Partition Part_3Values(' W '), Partition Part_4Values(' E ')); INSERT INTO DatapartValues(' N ',1,' Beijing '); INSERT INTO DatapartValues(' S ',2,' Guangzhou '); INSERT INTO DatapartValues(' E ',3,' Shanghai '); INSERT INTO DatapartValues(' W ',4,' Chengdu '); Sql>Select* FROM Datapart partition (part_2); R ID city-------------------------------S2GuangzhougrantSelect,Delete, UPDATE, insert on Datapart to Cacheadm;

Cache Group was established in two TimesTen databases cachedb1 and CACHEDB2, and data from North and South districts were cached

Cachedb1>createREADONLYCACHEGROUP "DATAPART1"AutoRefresh MODE INCREMENTAL INTERVAL5SECONDS State on  from  "Tthr"."Datapart"("Region"VARCHAR2 (1 BYTE) ,"ID"Number ( -) notNull"City"VARCHAR2 ( - BYTE), PRIMARYKEY("ID")  )WHERE(region=' N ')Cachedb1>Select* fromdatapart;< N, Beijing >cachedb2>createREADONLYCACHEGROUP "DATAPART2"AutoRefresh MODE INCREMENTAL INTERVAL5SECONDS State on  from  "Tthr"."Datapart"("Region"VARCHAR2 (1 BYTE) ,"ID"Number ( -) notNull"City"VARCHAR2 ( - BYTE), PRIMARYKEY("ID")  )WHERE(region=' S ')Cachedb2>Select* fromdatapart;< S, Guangzhou >

Inserting data in Oracle

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

New data can then be automatically obtained in two TimesTen

selectfrom15selectfrom26, Haikou >

Writable partitions

Writable partitions take AWT as an example, unlike read-only partitions, where AWT cannot specify a where condition when it is established, but can specify a where condition for data partitioning during the load operation

The cache Group is established in CACHEDB1, and the data in the buffer zone is specified in load:

CREATE Asynchronous WritethroughCACHE GROUP "AWT1"From"Tthr"."Datapart"("Region"VARCHAR2 (1BYTE),"ID"Number ( -) not NULL,"City"VARCHAR2 ( -BYTE), PRIMARY KEY ("ID")) CACHEDB1>Call TTREPSTART;CACHEDB1>LoadCache GroupAwt1whereRegion= ' N 'Commit every the rows; cachedb1> Select *From Datapart;<N1, Beijing><N5, Qingdao>Cachedb1>TRUNCATE TABLE Datapart;8238: Cannot truncateCache GroupTable Datapart; Please use Drop/refresh/unloadCACHE GROUPInsteadinsert intoDatapart VALUES (' N ',7,' tianjing ');

In Cachedb2, the cache Group is created, and the data for the southern region is specified in load:

CREATE Asynchronous Writethrough CACHEGROUP "AWT2"   from  "Tthr"."Datapart"("Region"VARCHAR2 (1 BYTE) ,"ID"Number ( -) notNull"City"VARCHAR2 ( - BYTE), PRIMARYKEY("ID")) cachedb2>PagerTtrepstart;cachedb2> Load CacheGroupAwt2whereRegion =' S ' commit every to the rows;Cachedb2>Select* fromdatapart;< S,2, Guangzhou >< S,6, Haikou >cachedb2> Insert intoDatapart VALUES (' S ', 8, ' Changsha ');

In Oracle, you can see the newly inserted data from each TimesTen

SQL> select * from datapart order by id; R         ID CITY- ---------- --------------------N          1 BeijingS          2 GuangzhouE          3 ShanghaiW          4 ChengduN          5 QingdaoS          6 HaikouN          7 TianjingS          8 Changsha8 rows selected.

Insert data in Oracle, and then each cache group gets new data through the Load/refresh operation.
Note that for explicitly load's cache group, load and refresh function the same.

Sql>insert intoDatapart VALUES (' N ',9,' Baoding '); insert intoDatapart VALUES (' S ',Ten,' Wuhan ');commit;cachedb1>Select* fromdatapart;< N,1, Beijing >< N,5, Qingdao >< N,7, Tianjing >cachedb1> Refresh CacheGroupAwt1whereRegion =' N 'Commit every theRows;cachedb1>Select* fromdatapart;< N,1, Beijing >< N,5, Qingdao >< N,7, Tianjing >< N,9, Baoding >cachedb2>Select* fromdatapart;< S,2, Guangzhou >< S,6, Haikou >< S,8, Changsha >cachedb2> Load CacheGroupAwt2whereRegion =' S 'Commit every theRows;cachedb2>Select* fromdatapart;< S,2, Guangzhou >< S,6, Haikou >< S,8, Changsha >< S,Ten, Wuhan >

Reference

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

TimesTen Application Layer Database Cache learning: 18. Using TimesTen to implement sharding or data partitioning