oracle cache table,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中，关联表删除是级联的