TimesTen 应用层数据库缓存学习：9. 一些独特的缓存组定义选项

2024-02-04 13:48
文章标签 学习 定义 数据库 缓存 应用层 选项 timesten 独特

本文主要是介绍TimesTen 应用层数据库缓存学习：9. 一些独特的缓存组定义选项，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

在缓存组定义中，有一些选项，它们平常很少会用到，但在一些特定的场合也许有用。
这些选项是：
* ON DELETE CASCADE
* UNIQUE HASH ON
* Where 条件中的SYNONYM
* 缓存LOB数据

ON DELETE CASCADE

on delete cascade由于涉及到delete操作，因此一般用于AWT缓存组。不过只读缓存组也可以用。

设定了ON DELETE CASCADE的只读缓存组

对于只读缓存组，在Oracle端可以不指定on delete cascade，而且可以没有主外键约束。在Oracle端删除root table的数据时，通过autorefresh反馈到timesten端，然后timesten执行on delete cascade。 

CREATE TABLE customer
(cust_num NUMBER(6) NOT NULL PRIMARY KEY,
region VARCHAR2(10),
name VARCHAR2(50),
address VARCHAR2(100));
CREATE TABLE orders
(ord_num NUMBER(10) NOT NULL PRIMARY KEY,
cust_num NUMBER(6) NOT NULL,
when_placed DATE NOT NULL,
when_shipped DATE NOT NULL);insert into customer values ( 1, 'West', 'Frank Edwards', '100 Pine St. Portland OR' );
insert into customer values ( 2, 'East', 'Angela Wilkins', '356 Olive St. Boston MA' );
insert into customer values ( 3, 'Midwest', 'Stephen Johnson', '7638 Walker Dr. Chicago IL' );
INSERT INTO customer VALUES (4, 'East', 'Roberta Simon', '3667 Park Ave. New York NY');INSERT INTO orders VALUES(1,1, DATE '2012-01-25', DATE '2012-01-30');
commit;cacheadm> 
CREATE READONLY CACHE GROUP customer_orders
FROM tthr.customer
(cust_num NUMBER(6) NOT NULL,
region VARCHAR2(10),
name VARCHAR2(50),
address VARCHAR2(100),
PRIMARY KEY(cust_num)),
tthr.orders
(ord_num NUMBER(10) NOT NULL,
cust_num NUMBER(6) NOT NULL,
when_placed DATE NOT NULL,
when_shipped DATE NOT NULL,
PRIMARY KEY(ord_num),
FOREIGN KEY(cust_num) REFERENCES tthr.customer(cust_num) ON DELETE CASCADE);cacheadm>load cache group customer_orders commit every 256 rows;
4 cache instances affected.
tthr>select * from customer;
< 1, West, Frank Edwards, 100 Pine St. Portland OR >
< 2, East, Angela Wilkins, 356 Olive St. Boston MA >
< 3, Midwest, Stephen Johnson, 7638 Walker Dr. Chicago IL >
< 4, East, Roberta Simon, 3667 Park Ave. New York NY >
4 rows found.
tthr>select * from orders;
< 1, 1, 2012-01-25 00:00:00, 2012-01-30 00:00:00 >
1 row found.
SQL> delete from customer where cust_num = 1;1 row deleted.SQL> commit;Commit complete.SQL> select * from orders;ORD_NUM   CUST_NUM WHEN_PLAC WHEN_SHIP
---------- ---------- --------- ---------1          1 25-JAN-12 30-JAN-125分钟后，autorefresh生效，着急的话可以手工refresh一次。
tthr>select * from customer;
< 2, East, Angela Wilkins, 356 Olive St. Boston MA >
< 3, Midwest, Stephen Johnson, 7638 Walker Dr. Chicago IL >
< 4, East, Roberta Simon, 3667 Park Ave. New York NY >
3 rows found.
tthr>select * from orders; <- on delete cascade生效了
0 rows found.不过此时Oracle与TimesTen的数据已经不一致了。
SQL> select * from orders;ORD_NUM   CUST_NUM WHEN_PLAC WHEN_SHIP
---------- ---------- --------- ---------1          1 25-JAN-12 30-JAN-12

设定了ON DELETE CASCADE的AWT缓存组

对于AWT缓存组，oracle端必须有匹配的主外键关系，必须指定on delete cascade。

CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP customer_orders
FROM tthr.customer
(cust_num NUMBER(6) NOT NULL,
region VARCHAR2(10),
name VARCHAR2(50),
address VARCHAR2(100),
PRIMARY KEY(cust_num)),
tthr.orders
(ord_num NUMBER(10) NOT NULL,
cust_num NUMBER(6) NOT NULL,
when_placed DATE NOT NULL,
when_shipped DATE NOT NULL,
PRIMARY KEY(ord_num),
FOREIGN KEY(cust_num) REFERENCES tthr.customer(cust_num) ON DELETE CASCADE);
cacheadm>CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP customer_orders> FROM tthr.customer> (cust_num NUMBER(6) NOT NULL,> region VARCHAR2(10),> name VARCHAR2(50),> address VARCHAR2(100),> PRIMARY KEY(cust_num)),> tthr.orders> (ord_num NUMBER(10) NOT NULL,> cust_num NUMBER(6) NOT NULL,> when_placed DATE NOT NULL,> when_shipped DATE NOT NULL,> PRIMARY KEY(ord_num),> FOREIGN KEY(cust_num) REFERENCES tthr.customer(cust_num) ON DELETE CASCADE);5164: No matching foreign key with cascade action for table TTHR.ORDERS on Oracle
The command failed.
SQL>
CREATE TABLE customer
(cust_num NUMBER(6) NOT NULL PRIMARY KEY,
region VARCHAR2(10),
name VARCHAR2(50),
address VARCHAR2(100));
CREATE TABLE orders
(ord_num NUMBER(10) NOT NULL PRIMARY KEY,
cust_num NUMBER(6) NOT NULL,
when_placed DATE NOT NULL,
when_shipped DATE NOT NULL,
FOREIGN KEY(cust_num) REFERENCES customer(cust_num));
insert into customer values ( 1, 'West', 'Frank Edwards', '100 Pine St. Portland OR' );
insert into customer values ( 2, 'East', 'Angela Wilkins', '356 Olive St. Boston MA' );
insert into customer values ( 3, 'Midwest', 'Stephen Johnson', '7638 Walker Dr. Chicago IL' );
INSERT INTO customer VALUES (4, 'East', 'Roberta Simon', '3667 Park Ave. New York NY');INSERT INTO orders VALUES(1,1, DATE '2012-01-25', DATE '2012-01-30');
commit;
cacheadm>
CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP "CUSTOMER_ORDERS" FROM"TTHR"."CUSTOMER" ("CUST_NUM" NUMBER(6)          NOT NULL,"REGION"   VARCHAR2(10 BYTE) ,"NAME"     VARCHAR2(50 BYTE) ,"ADDRESS"  VARCHAR2(100 BYTE),PRIMARY KEY("CUST_NUM")),"TTHR"."ORDERS" ("ORD_NUM"      NUMBER(10) NOT NULL,"CUST_NUM"     NUMBER(6)  NOT NULL,"WHEN_PLACED"  DATE       NOT NULL,"WHEN_SHIPPED" DATE       NOT NULL,PRIMARY KEY("ORD_NUM"),FOREIGN KEY("CUST_NUM")REFERENCES "TTHR"."CUSTOMER"("CUST_NUM")ON DELETE CASCADE);5164: No matching foreign key with cascade action for table TTHR.ORDERS on Oracle
The command failed.
CREATE TABLE customer
(cust_num NUMBER(6) NOT NULL PRIMARY KEY,
region VARCHAR2(10),
name VARCHAR2(50),
address VARCHAR2(100));
CREATE TABLE orders
(ord_num NUMBER(10) NOT NULL PRIMARY KEY,
cust_num NUMBER(6) NOT NULL,
when_placed DATE NOT NULL,
when_shipped DATE NOT NULL,
FOREIGN KEY(cust_num) REFERENCES customer(cust_num) ON DELETE CASCADE);
cacheadm>
CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP "CUSTOMER_ORDERS" FROM"TTHR"."CUSTOMER" ("CUST_NUM" NUMBER(6)          NOT NULL,"REGION"   VARCHAR2(10 BYTE) ,"NAME"     VARCHAR2(50 BYTE) ,"ADDRESS"  VARCHAR2(100 BYTE),PRIMARY KEY("CUST_NUM")),"TTHR"."ORDERS" ("ORD_NUM"      NUMBER(10) NOT NULL,"CUST_NUM"     NUMBER(6)  NOT NULL,"WHEN_PLACED"  DATE       NOT NULL,"WHEN_SHIPPED" DATE       NOT NULL,PRIMARY KEY("ORD_NUM"),FOREIGN KEY("CUST_NUM")REFERENCES "TTHR"."CUSTOMER"("CUST_NUM")ON DELETE CASCADE);5188: An oracle index is required on foreign key TTHR.ORDERS(CUST_NUM).
The command failed.SQL> create index idx1 on orders(cust_num);Index created.
CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP "CUSTOMER_ORDERS" FROM"TTHR"."CUSTOMER" ("CUST_NUM" NUMBER(6)          NOT NULL,"REGION"   VARCHAR2(10 BYTE) ,"NAME"     VARCHAR2(50 BYTE) ,"ADDRESS"  VARCHAR2(100 BYTE),PRIMARY KEY("CUST_NUM")),"TTHR"."ORDERS" ("ORD_NUM"      NUMBER(10) NOT NULL,"CUST_NUM"     NUMBER(6)  NOT NULL,"WHEN_PLACED"  DATE       NOT NULL,"WHEN_SHIPPED" DATE       NOT NULL,PRIMARY KEY("ORD_NUM"),FOREIGN KEY("CUST_NUM")REFERENCES "TTHR"."CUSTOMER"("CUST_NUM")ON DELETE CASCADE);
成功！cacheadm>load cache group customer_orders commit every 256 rows;
4 cache instances affected.tthr>select * from customer;
< 1, West, Frank Edwards, 100 Pine St. Portland OR >
< 2, East, Angela Wilkins, 356 Olive St. Boston MA >
< 3, Midwest, Stephen Johnson, 7638 Walker Dr. Chicago IL >
< 4, East, Roberta Simon, 3667 Park Ave. New York NY >
4 rows found.tthr>delete from customer where cust_num = 1;
1 row deleted.
tthr>select * from orders;
0 rows found.SQL> select * from orders;no rows selectedSQL> select count(*) from customer;COUNT(*)
----------3

UNIQUE HASH ON

unique hash on 和索引相关

UNIQUE HASH ON specifies that a hash index rather than a range index is created on the primary key columns of the cache table.

TimesTen缺省的索引为range index，比较适合范围查询，而hash index适合于完全匹配查询。
是否使用unique hasn on取决于你的业务行为，即使用哪种查询更频繁些。

Referencing Oracle Database PL/SQL functions in a WHERE clause

TimesTen的缓存组操作不能直接调用Oracle中的函数，但可以通过Synonym间接调用。

A user-defined PL/SQL function in the Oracle database can be invoked indirectly in a WHERE clause within a CREATE CACHE GROUP, LOAD CACHE GROUP, or REFRESH CACHE GROUP (for dynamic cache groups only) statement.

由于函数和Synonym都是定义在Oracle端，因此最常用于只读缓存组，以及AWT缓存组的带where条件的Load和Refresh操作。

SQL>
CREATE OR REPLACE FUNCTION get_customer_name
(c_num customer.cust_num%TYPE) RETURN VARCHAR2 IS
c_name customer.name%TYPE;
BEGIN
SELECT name INTO c_name FROM customer WHERE cust_num = c_num;
RETURN c_name;
END get_customer_name;
/
GRANT EXECUTE ON get_customer_name TO PUBLIC;cacheadm>
CREATE READONLY CACHE GROUP top_customer
FROM tthr.customer
(cust_num NUMBER(6) NOT NULL,
region VARCHAR2(10),
name VARCHAR2(50),
address VARCHAR2(100),
PRIMARY KEY(cust_num))
WHERE name = get_customer_name(100);5221: Oracle syntax error in OCIStmtExecute(): ORA-00904: "GET_CUSTOMER_NAME": invalid identifier rc = -15117: Illegal WHERE clause for cache table TTHR.CUSTOMER
The command failed. <- 不能直接调用Oracle函数SQL> CREATE PUBLIC SYNONYM retname FOR get_customer_name;Synonym created.
cacheadm>
CREATE READONLY CACHE GROUP top_customer
FROM tthr.customer
(cust_num NUMBER(6) NOT NULL,
region VARCHAR2(10),
name VARCHAR2(50),
address VARCHAR2(100),
PRIMARY KEY(cust_num))
WHERE name = retname(100); <- 通过Synonym调用函数成功cacheadm>cachegroups;Cache Group CACHEADM.TOP_CUSTOMER:Cache Group Type: Read OnlyAutorefresh: YesAutorefresh Mode: IncrementalAutorefresh State: PausedAutorefresh Interval: 5 MinutesAutorefresh Status: okAging: No aging definedRoot Table: TTHR.CUSTOMERWhere Clause: name = retname(100)Table Type: Read Only1 cache group found.CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP top_customer
FROM tthr.customer
(cust_num NUMBER(6) NOT NULL,
region VARCHAR2(10),
name VARCHAR2(50),
address VARCHAR2(100),
PRIMARY KEY(cust_num))
WHERE name = retname(100);cacheadm>CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP top_customer> FROM tthr.customer> (cust_num NUMBER(6) NOT NULL,> region VARCHAR2(10),> name VARCHAR2(50),> address VARCHAR2(100),> PRIMARY KEY(cust_num))> WHERE name = retname(100);8268: Asynchronous or Synchronous Writethrough cache group cannot have a WHERE clause
The command failed. <-读写缓存组不允许带where条件，但可以在load/refresh时带where条件。CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP "CUSTOMER_ORDERS" FROM"TTHR"."CUSTOMER" ("CUST_NUM" NUMBER(6)          NOT NULL,"REGION"   VARCHAR2(10 BYTE) ,"NAME"     VARCHAR2(50 BYTE) ,"ADDRESS"  VARCHAR2(100 BYTE),PRIMARY KEY("CUST_NUM")),"TTHR"."ORDERS" ("ORD_NUM"      NUMBER(10) NOT NULL,"CUST_NUM"     NUMBER(6)  NOT NULL,"WHEN_PLACED"  DATE       NOT NULL,"WHEN_SHIPPED" DATE       NOT NULL,PRIMARY KEY("ORD_NUM"),FOREIGN KEY("CUST_NUM")REFERENCES "TTHR"."CUSTOMER"("CUST_NUM"));cacheadm>load cache group customer_orders where customer.name = retname(100) commit every 256 rows;
0 cache instances affected. <-load时调用Synonym成功

缓存LOB数据

SQL>
CREATE TABLE t (
i INT NOT NULL PRIMARY KEY
, c CLOB
, b BLOB
, nc NCLOB);INSERT INTO t VALUES (1
, RPAD('abcdefg8', 2048, 'abcdefg8')
, HEXTORAW(RPAD('123456789ABCDEF8', 4000, '123456789ABCDEF8'))
, RPAD('abcdefg8', 2048, 'abcdefg8')
);
commit;grant select on t to cacheadm;
grant delete, update, insert on t to cacheadm;cacheadm>
CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH CACHE GROUP cg1
FROM tthr.t
(i INT NOT NULL PRIMARY KEY
, c CLOB
, b BLOB
, nc NCLOB);5115: Unsupported type mapping for column
<-按照user guide中的做法是错的，是文档bug，改为以下通过CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH CACHE GROUP cg1
FROM tthr.t
(i INT NOT NULL PRIMARY KEY
, c VARCHAR2(128)
, b VARBINARY(128)
, nc NVARCHAR2(128));

这篇关于TimesTen 应用层数据库缓存学习：9. 一些独特的缓存组定义选项的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/677713。

相关文章

数据库oracle用户密码过期查询及解决方案

数据库oracle用户密码过期查询及解决方案

《数据库oracle用户密码过期查询及解决方案》：本文主要介绍如何处理ORACLE数据库用户密码过期和修改密码期限的问题,包括创建用户、赋予权限、修改密码、解锁用户和设置密码期限,文中通过代码介绍... 目录前言一、创建用户、赋予权限、修改密码、解锁用户和设置期限二、查询用户密码期限和过期后的修改1.查询用
阅读更多...
mysql数据库分区的使用

mysql数据库分区的使用

《mysql数据库分区的使用》MySQL分区技术通过将大表分割成多个较小片段,提高查询性能、管理效率和数据存储效率,本文就来介绍一下mysql数据库分区的使用,感兴趣的可以了解一下... 目录【一】分区的基本概念【1】物理存储与逻辑分割【2】查询性能提升【3】数据管理与维护【4】扩展性与并行处理【二】分区的
阅读更多...
IDEA如何切换数据库版本mysql5或mysql8

IDEA如何切换数据库版本mysql5或mysql8

《IDEA如何切换数据库版本mysql5或mysql8》本文介绍了如何将IntelliJIDEA从MySQL5切换到MySQL8的详细步骤,包括下载MySQL8、安装、配置、停止旧服务、启动新服务以及... 目录问题描述解决方案第一步第二步第三步第四步第五步总结问题描述最近想开发一个新应用，想使用mysq
阅读更多...
Oracle数据库使用 listagg去重删除重复数据的方法汇总

Oracle数据库使用 listagg去重删除重复数据的方法汇总

《Oracle数据库使用listagg去重删除重复数据的方法汇总》文章介绍了在Oracle数据库中使用LISTAGG和XMLAGG函数进行字符串聚合并去重的方法,包括去重聚合、使用XML解析和CLO... 目录案例表第一种：使用wm_concat() + distinct去重聚合第二种：使用listagg，
阅读更多...
Redis缓存问题与缓存更新机制详解

Redis缓存问题与缓存更新机制详解

《Redis缓存问题与缓存更新机制详解》本文主要介绍了缓存问题及其解决方案,包括缓存穿透、缓存击穿、缓存雪崩等问题的成因以及相应的预防和解决方法,同时,还详细探讨了缓存更新机制,包括不同情况下的缓存更... 目录一、缓存问题1.1 缓存穿透1.1.1 问题来源1.1.2 解决方案1.2 缓存击穿1.2.1
阅读更多...
Java读取InfluxDB数据库的方法详解

Java读取InfluxDB数据库的方法详解

《Java读取InfluxDB数据库的方法详解》本文介绍基于Java语言,读取InfluxDB数据库的方法,包括读取InfluxDB的所有数据库,以及指定数据库中的measurement、field、... 首先，创建一个Java项目，用于撰写代码。接下来，配置所需要的依赖；这里我们就选择可用于与Infl
阅读更多...
详谈redis跟数据库的数据同步问题

详谈redis跟数据库的数据同步问题

《详谈redis跟数据库的数据同步问题》文章讨论了在Redis和数据库数据一致性问题上的解决方案,主要比较了先更新Redis缓存再更新数据库和先更新数据库再更新Redis缓存两种方案,文章指出,删除R... 目录一、Redis 数据库数据一致性的解决方案1.1、更新Redis缓存、删除Redis缓存的区别二
阅读更多...
oracle数据库索引失效的问题及解决

oracle数据库索引失效的问题及解决

《oracle数据库索引失效的问题及解决》本文总结了在Oracle数据库中索引失效的一些常见场景,包括使用isnull、isnotnull、!=、、、函数处理、like前置%查询以及范围索引和等值索引... 目录oracle数据库索引失效问题场景环境索引失效情况及验证结论一结论二结论三结论四结论五总结ora
阅读更多...
Redis与缓存解读

Redis与缓存解读

《Redis与缓存解读》文章介绍了Redis作为缓存层的优势和缺点,并分析了六种缓存更新策略,包括超时剔除、先删缓存再更新数据库、旁路缓存、先更新数据库再删缓存、先更新数据库再更新缓存、读写穿透和异步... 目录缓存缓存优缺点缓存更新策略超时剔除先删缓存再更新数据库旁路缓存(先更新数据库，再删缓存）先更新数
阅读更多...
el-select下拉选择缓存的实现

el-select下拉选择缓存的实现

《el-select下拉选择缓存的实现》本文主要介绍了在使用el-select实现下拉选择缓存时遇到的问题及解决方案,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的... 目录项目场景：问题描述解决方案：项目场景：从左侧列表中选取字段填入右侧下拉多选框，用户可以对右侧
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2