TimesTen中的passthrough模式

TimesTen没有象Oracle中的DB Link，但可以通过设置passthrough来实现类似的功能。
使用passthrough模式时，需要指定OracleNetServiceName和OraclePWD
passthrough的好处在于简单，只需要建立一个到TimesTen的连接，而后端访问Oracle是透明的；但是如果需要频繁访问Oracle，出于性能考虑，还是建议建立单独建立一个到Oracle的连接。
passthrough可以设置0-5六种模式，其中0是缺省，即所有的操作都在TimesTen中执行，例如对于cachegroup。

passthrough=0的描述为：

is the default setting and specifies that all statements are to be executed in the TimesTen database.

[oracle@localhost ~]$ ttisql -v1 plsqldb
Command> passthrough
PassThrough = 0

passthrough=1针对timesten中没有的表，其描述为：

specify that a statement that references a table that does not exist in the TimesTen database is passed through to the Oracle database for execution.

在下面的例子中，employees表只在Oracle中存在，tt_tables只在TimesTen中存在

[oracle@localhost ~]$ ttisql -v1 plsqldb
Command> create user hr identified by timesten;
Command> grant create session, create table to hr;
Command> disconnect
Command> connect "dsn=plsqldb;uid=hr;pwd=timesten;oraclepwd=hr";
Command> use
Command> create table tt_table(a int);
Command> select count(*) from tt_table;
< 0 >
Command> select count(*) from employees;
 2206: Table HR.EMPLOYEES not found
Command> passthrough 1
Command> select count(*) from employees;
< 107 >
Command> select count(*) from tt_table;
< 0 >
Command> passthrough 0

passthrough=2的描述为:

INSERT, UPDATE and DELETE statements are passed through to the Oracle database for read-only cache groups and user managed cache groups that use the READONLY cache table attribute. Otherwise, Passthrough=1 behavior applies.

主要针对只读的cache group，因为对于只读的CG，更新语句是不允许的，这时可以pass-through到oracle执行.

Command> cachegroups;

Cache Group CACHEUSER.READCACHE:

  Cache Group Type: Read Only
  Autorefresh: Yes
  Autorefresh Mode: Incremental
  Autorefresh State: Paused
  Autorefresh Interval: 30 Minutes
  Autorefresh Status: ok
  Aging: No aging defined

  Root Table: HR.READTAB
  Table Type: Read Only

1 cache group found.

Command> passthrough
PassThrough = 0
Command> select * from readtab;
< 1, REFRESH >
< 2, REFRESH >
< 3, REFRESH >
< 4, new >
4 rows found.
Command> update readtab set str='ok';
 8225: Table READTAB is read only
The command failed.
Command> passthrough 2
Passthrough command has set autocommit off.
Command> update readtab set str='ok';
5 rows updated.
Command> commit;
Command> passthrough;
PassThrough = 0

在Oracle中：
SQL> select * from readtab;

    KEYVAL STR
---------- --------------------------------
     3 ok
     4 ok
     1 ok
     2 ok
     5 ok

回到TimesTen:
Command> select * from readtab;
< 1, REFRESH >
< 2, REFRESH >
< 3, REFRESH >
< 4, new >
4 rows found.
数据没有改变是因为refresh间隔太长，只好手工refresh一次( refresh cache group readcache commit every 256 rows;)

这时数据有了
Command> select * from readtab;
< 1, ok >
< 2, ok >
< 3, ok >
< 4, ok >
< 5, ok >
5 rows found.

passthrough=3的说明为：

all (注意，是所有) statements are passed through to the Oracle database for execution, except that INSERT, UPDATE and DELETE statements issued on cache tables in a dynamic AWT global cache group result in a TimesTen error.

此描述中 except后面部分着实有些难懂，不过下图中自带的描述就明白多了，也就是说，对于global cache group, 修改数据是不允许的

下面的例子说明了passthrough=3时，图左的情况：

Command> select * from readtab;
< 1, ok >
< 2, ok >
< 3, ok >
< 4, ok >
< 5, ok >
5 rows found.
Command> passthrough 3
Passthrough command has set autocommit off.
Command> update readtab set str='3';
5 rows updated.
Command> commit;
Command> passthrough
PassThrough = 0
Command> select * from readtab;
< 1, ok >
< 2, ok >
< 3, ok >
< 4, ok >
< 5, ok >
5 rows found.
Command> passthrough 3
Command> select * from readtab; <- select 也passthrough到oracle来做，这是与passthrough=2的区别
< 3, 3 >
< 4, 3 >
< 1, 3 >
< 2, 3 >
< 5, 3 >
5 rows found.
Command> 

下面的例子说明了passthrough=3时，图右的情况：

[oracle@localhost ~]$ ttisql "dsn=cachedb2;uid=tthr;pwd=tthr;oraclepwd=tthr"

Command> select * from employees;
0 rows found.
Command> select * from employees where employee_id = 100;
< 100, Steven, King, SKING, 515.123.4567, 1987-06-17 00:00:00, AD_PRES, 24000, <NULL>, <NULL>, 90 >
1 row found.
Command> select * from employees;
< 100, Steven, King, SKING, 515.123.4567, 1987-06-17 00:00:00, AD_PRES, 24000, <NULL>, <NULL>, 90 >
1 row found.
Command> passthrough 3
Passthrough command has set autocommit off.
Command> desc employees;
Retrieving objects from Oracle...

Table TTHR.EMPLOYEES:
  Columns:
   *EMPLOYEE_ID                     NUMBER (6) NOT NULL
    FIRST_NAME                      VARCHAR2 (20)
    LAST_NAME                       VARCHAR2 (25) NOT NULL
    EMAIL                           VARCHAR2 (25) NOT NULL
    PHONE_NUMBER                    VARCHAR2 (20)
    HIRE_DATE                       DATE NOT NULL
    JOB_ID                          VARCHAR2 (10) NOT NULL
    SALARY                          NUMBER (8,2)
    COMMISSION_PCT                  NUMBER (2,2)
    MANAGER_ID                      NUMBER (6)
    DEPARTMENT_ID                   NUMBER (4)

1 table found.
(primary key columns are indicated with *)
Command> update employees set salary = 0 where employee_id = 100;
 3340: Passthrough delete/update/insert/truncate of cache grid tables are not allowed
The command failed.

passthrough=4针对在Global AWT CG中，select不满足dynamic load条件时，其描述为：

SELECT statements issued on cache tables in a dynamic AWT global cache group that do not satisfy the criteria for a dynamic load query are passed through to the Oracle database for execution

左边的图好理解，就是在passthough=4时，对于非global cache group，相当于passthrough = 0; 主要是右图，虚线部分表示满足dynamic load，实现部分表示不满足
注意这里指的是global cache group，就是有多个TimesTen member的情况，由于每个member只缓存Oracle的一部分数据，因此有时需要回到源表中查询。例如：

[oracle@localhost ~]$ ttisql "dsn=cachedb1;uid=tthr;pwd=tthr;oraclepwd=tthr"

Command> select * from employees;
0 rows found.
Command> select count(*) from employees;
< 0 >
1 row found.
Command> select * from employees where employee_id = 101;
< 101, Neena, Kochhar, NKOCHHAR, 650.555.5555, 1989-09-21 00:00:00, AD_VP, 18000, <NULL>, 100, 90 >
1 row found. <-满足dynamic load的条件
Command> select count(*) from employees;
< 1 >
1 row found.
Command> passthrough 4
Passthrough command has set autocommit off.
Command> select count(*) from employees; <-不满足dynamic load的条件
< 107 >
1 row found.
Command> select avg(salary) from employees; <-不满足dynamic load的条件
< 6471.02803738317757009345794392523364486 >
1 row found.
Command> 

passthrough=5和4很类似，唯一的区别是其确认对于cache table的更新已经同步到了Oracle，好处是可以保证Oracle中的数据是最新的，其描述为：

SELECT statements issued on cache tables in a dynamic AWT global cache group that do not satisfy the criteria for a dynamic load query are passed through to the Oracle database for execution when all committed updates on cache tables in dynamic AWT global cache groups by previous transactions within the connection have been propagated to the Oracle database. Otherwise, statements are executed in the TimesTen database.

在所有的passthrough模式中，感觉3应该是最常用的，即在连接到TimesTen后需要临时切换到Oracle进行操作。例如：

[oracle@localhost ~]$ ttisql -v1 "dsn=plsqldb;uid=pls;pwd=pls;oraclepwd=pls"
Command> passthrough 3
Command> create table oracle_table(a int);
Command> insert into oracle_table values(1);
Command> passthrough 0
Command> select * from oracle_table;
 2206: Table PLS.ORACLE_TABLE not found

本文参考了Oracle® TimesTen Application-Tier Database Cache User’s Guide|5 Cache Group Operations|Setting a passthrough level