联系:手机/微信(+86 17813235971) QQ(107644445)
标题:Multiple-table cache group配置
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
1.准备测试表和数据
SQL> CREATE TABLE customer 2 (cust_num NUMBER(6) NOT NULL PRIMARY KEY, 3 name VARCHAR2(50) 4 ); Table created. SQL> CREATE TABLE orders 2 (ord_num NUMBER(10) NOT NULL PRIMARY KEY, 3 cust_num NUMBER(6) NOT NULL 4 ); Table created. SQL> insert into customer values(1,'wwww.xifenfei.com1'); 1 row created. SQL> insert into customer values(2,'wwww.xifenfei.com2'); 1 row created. SQL> insert into customer values(3,'wwww.xifenfei.com3'); 1 row created. SQL> insert into customer values(4,'wwww.xifenfei.com4'); 1 row created. SQL> insert into orders(cust_num,ord_num) values(1,1); 1 row created. SQL> insert into orders (cust_num,ord_num) values(1,2); 1 row created. SQL> insert into orders (cust_num,ord_num) values(3,5); 1 row created. SQL> insert into orders (cust_num,ord_num) values(3,6); 1 row created. SQL> commit; Commit complete. SQL> select * from customer; CUST_NUM NAME ---------- -------------------------------------------------- 1 wwww.xifenfei.com1 2 wwww.xifenfei.com2 3 wwww.xifenfei.com3 4 wwww.xifenfei.com4 SQL> select * from orders; ORD_NUM CUST_NUM ---------- ---------- 1 1 2 1 5 3 6 3 SQL> grant select on oratt.customer to cacheuser; Grant succeeded. SQL> grant select on oratt.orders to cacheuser; Grant succeeded.
2.创建cache group
[oracle@xifenfei ~]$ ttIsql "DSN=tt_1122;UID=cacheuser;PWD=timesten;OraclePWD=oracle" Command> drop cache group cacheuser.customer_orders; Command> CREATE READONLY CACHE GROUP customer_orders > AUTOREFRESH INTERVAL 5 SECONDS > STATE ON > FROM oratt.customer > (cust_num NUMBER(6) NOT NULL, > name VARCHAR2(50), > PRIMARY KEY(cust_num)), > oratt.orders > (ord_num NUMBER(10) NOT NULL, > cust_num NUMBER(6) NOT NULL, > PRIMARY KEY(ord_num), > FOREIGN KEY(cust_num) REFERENCES oratt.customer(cust_num)); Command> cachegroups; Cache Group CACHEUSER.CUSTOMER_ORDERS: Cache Group Type: Read Only Autorefresh: Yes Autorefresh Mode: Incremental Autorefresh State: On Autorefresh Interval: 5 Seconds Autorefresh Status: ok Aging: No aging defined Root Table: ORATT.CUSTOMER Table Type: Read Only Child Table: ORATT.ORDERS Table Type: Read Only 1 cache groups found.
3.TT中表访问授权
[oracle@xifenfei ~]$ ttisql tt_1122 Command> grant select on oratt.customer to cacheuser; Command> grant select on oratt.orders to cacheuser;
4.测试数据初始化
[oracle@xifenfei ~]$ ttIsql "DSN=tt_1122;UID=cacheuser;PWD=timesten;OraclePWD=oracle" Command> select * from oratt.customer; < 1, wwww.xifenfei.com1 > < 2, wwww.xifenfei.com2 > < 3, wwww.xifenfei.com3 > < 4, wwww.xifenfei.com4 > 4 rows found. Command> select * from oratt.orders; < 1, 1 > < 2, 1 > < 5, 3 > < 6, 3 > 4 rows found.
5.ORACLE修改数据
SQL> update customer set name='xifenfei' where cust_num=2; 1 row updated. SQL> insert into customer values(5,'wwww.xifenfei.com5'); 1 row created. SQL> delete from customer where cust_num=1; 1 row deleted. SQL> commit; Commit complete.
6.TT中验证数据
Command> select * from oratt.customer; < 2, xifenfei > < 3, wwww.xifenfei.com3 > < 4, wwww.xifenfei.com4 > < 5, wwww.xifenfei.com5 > 4 rows found. Command> select * from oratt.orders; < 5, 3 > < 6, 3 > 2 rows found.
7.补充说明
7.1)在oracle中需要授权cacheuser有访问oratt中相关表权限,不然创建cache group失败
7.2)自动刷新数据需要设置AUTOREFRESH STATE ON,其他方法初始化关联表的数据暂未知
7.3)在TT中,关联表删除是级联的