- VisualStudio2022插件的安装及使用-编程手把手系列文章
- pprof-在现网场景怎么用
- C#实现的下拉多选框,下拉多选树,多级节点
- 【学习笔记】基础数据结构:猫树
Oracle临时表在处理临时数据、会话数据隔离和复杂查询优化方面非常有用.
其底层逻辑是通过Oracle特殊的临时表来减少I/O操作和日志开销,提高了数据库性能和查询效率。开发者可以根据具体需求和场景,合理使用临时表来简化数据处理逻辑和提高系统性能.
早期开发人员在使用Oracle数据库时,经常因为不熟悉或不了解全局临时表(Global Temporary Table,下文简称GTT)的特性,因而自行定义了所谓的“临时表”,不但增加了开发复杂度,比如需要自行做数据清理和会话隔离等问题,还因高频操作这类表产生了大量重做日志(redo logs),进而增加了I/O负载和系统开销,主要代价这么多,最终的应用性能还不够好.
所幸这类问题随着用户量的提升,大家口口相传这个最佳实践,后续开发已经很少会犯这类低级问题.
那是不是用了Oracle的临时表就可以高枕无忧了呢?
最近笔者在某客户遇到一个临时表的问题,在分析这个客户问题的过程,也和大家一起来回顾下有关Oracle临时表的知识.
本次遇到问题的临时表,是使用的Oracle的GTT,且定义表中数据是基于session-specific的类型,脱敏后的创建语句为:
CREATE GLOBAL TEMPORARY TABLE "JINGYU"."G_T_T1"
("ID" NUMBER(10,0) NOT NULL ENABLE,
"NAME" VARCHAR2(30) NOT NULL ENABLE)
ON COMMIT PRESERVE ROWS;
下面是官方文档截图,比较了GTT和PTT的差异:
除了上面提到的命名规则等差异之外,还要补充一点: GTT是8i后就已经支持的技术,而PTT要在18c及以后版本才支持.
关于GTT的两种类型,文档说明如下:
根据你的应用需求选择,简单说就是如果想在事务结束就清空表,选择DELETE ROWS类型;如果想在会话结束才清空表,就选择PRESERVE ROWS类型.
临时表也是有统计信息的,而且临时表统计信息的机制在不同版本也有差异。 先看下在19c版本下表现:
我先在session1中插入两条测试数据,收集统计信息:
INSERT INTO G_T_T1 VALUES (1, 'Alfred');
INSERT INTO G_T_T1 VALUES (2, 'Mcdull');
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'JINGYU',
tabname => 'G_T_T1',
cascade => TRUE
);
END;
/
SELECT owner, table_name, num_rows, blocks, empty_blocks, avg_space
FROM dba_tab_statistics
WHERE table_name = 'G_T_T1';
紧接着在session2中插入一条数据,收集统计信息:
INSERT INTO G_T_T1 VALUES (3, 'Test');
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'JINGYU',
tabname => 'G_T_T1',
cascade => TRUE
);
END;
/
SELECT owner, table_name, num_rows, blocks, empty_blocks, avg_space
FROM dba_tab_statistics
WHERE table_name = 'G_T_T1';
两个查询结果是不一样的,两行结果,分别显示为2条和1条数据的统计信息。 --result1
08:52:42 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO1> SELECT owner, table_name, num_rows, blocks, empty_blocks, avg_space
FROM dba_tab_statistics
WHERE table_name = 'G_T_T1';08:53:39 2 08:53:39 3
OWNER TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE
------------------------------ ------------------------------ ---------- ---------- ------------ ----------
JINGYU G_T_T1
JINGYU G_T_T1 2 1 0 0
Elapsed: 00:00:00.02
--result2
08:53:35 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO1> SELECT owner, table_name, num_rows, blocks, empty_blocks, avg_space
FROM dba_tab_statistics
WHERE table_name = 'G_T_T1';08:53:44 2 08:53:44 3
OWNER TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE
------------------------------ ------------------------------ ---------- ---------- ------------ ----------
JINGYU G_T_T1
JINGYU G_T_T1 1 1 0 0
Elapsed: 00:00:00.01
这说明全局临时表在19c版本的默认统计信息是session级别.
--查看全局临时表的统计信息首选项设置
SELECT DBMS_STATS.GET_PREFS('GLOBAL_TEMP_TABLE_STATS', 'JINGYU', 'G_T_T1') AS global_temp_table_stats
FROM dual;
09:04:59 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO1> --查看全局临时表的统计信息首选项设置
SELECT DBMS_STATS.GET_PREFS('GLOBAL_TEMP_TABLE_STATS', 'JINGYU', 'G_T_T1') AS global_temp_table_stats
FROM dual;
09:05:00 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO1> 09:05:00 2
GLOBAL_TEMP_TABLE_STATS
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SESSION
Elapsed: 00:00:00.01
具体查了下文档,说是在Oracle 12c及之后的版本中,全局临时表(GTT)的统计信息确实有可能是会话级别的。这意味着每个会话可以有自己的统计信息,这与全局共享的统计信息不同.
Default in 12c is now SESSION global temporary table statistics. Consider whether your database application depends on SHARED global temporary table statistics.
而如果你的数据库还是11g版本,那么这个统计信息的机制就是不同的,也就是SHARED,这里模拟将19c的这个也修改为SHARED模式,看看表现:
BEGIN
DBMS_STATS.SET_TABLE_PREFS(
ownname => 'JINGYU',
tabname => 'G_T_T1',
pname => 'GLOBAL_TEMP_TABLE_STATS',
pvalue => 'SHARED'
);
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'JINGYU',
tabname => 'G_T_T1',
cascade => TRUE
);
END;
/
成功修改为shared后,当表中有3条数据时收集统计信息后再次查询,会发现这个统计信息在其他会话也可以访问到:
09:10:46 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO1> r
1 SELECT owner, table_name, num_rows, blocks, empty_blocks, avg_space
2 FROM dba_tab_statistics
3* WHERE table_name = 'G_T_T1'
OWNER TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE
------------------------------ ------------------------------ ---------- ---------- ------------ ----------
JINGYU G_T_T1 3 1 0 0
Elapsed: 00:00:00.02
09:10:48 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO1>
GTT是可以创建索引的,当然这个索引也是临时的属性,另外还可以在GTT上创建视图和触发器.
You can create indexes for global (not private) temporary tables with the CREATE INDEX
statement. These indexes are also temporary. The data in the index has the same
session or transaction scope as the data in the temporary table. You can also create a
view or trigger on a global temporary table.
使用ALTER TABLE ... CACHE可以提高GTT查询性能.
ALTER TABLE G_T_T1 CACHE;
在Oracle中,通过使用ALTER TABLE ... CACHE语句可以在创建GTT时指定缓存属性。GTT是一种特殊类型的数据库表,用于存储临时数据,数据在会话结束或事务完成时被清除。CACHE关键字在这里的作用是指定GTT的缓存属性.
具体来说,CACHE关键字指示Oracle数据库将GTT的数据块缓存在内存中,而不是直接写入磁盘。这样做的好处是可以提高查询临时表数据的性能,因为访问内存通常比访问磁盘要快得多.
需要注意的是,使用CACHE会占用更多的内存空间,因为临时表的数据在会话结束或事务完成后会被清除,所以对于大型数据量或长时间运行的会话,可能需要权衡内存利用和性能.
临时表是否设置了CACHE属性是可以通过dbms_metadata.get_ddl中看到cache标识的:
select dbms_metadata.get_ddl('TABLE','G_T_T1','JINGYU') from dual;
CREATE GLOBAL TEMPORARY TABLE "JINGYU"."G_T_T1"
( "ID" NUMBER(10,0) NOT NULL ENABLE,
"NAME" VARCHAR2(30) NOT NULL ENABLE
) ON COMMIT PRESERVE ROWS
CACHE
特别需要注意的是,这个简单的cache命令,在线直接执行很可能会失败:
alter table G_T_T1 cache;
会报错ORA-14450:
14:46:14 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO1> alter table G_T_T1 cache;
alter table G_T_T1 cache
*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use
Elapsed: 00:00:00.02
这是因为有会话在使用GTT,确保没有使用的会话重新执行才能成功:
14:51:14 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO1> alter table G_T_T1 cache;
Table altered.
Elapsed: 00:00:00.10
顺便查了一些相关问题,作为扩展供大家学习参考:
FYI
最后此篇关于Oracle优化神技之临时表的文章就讲到这里了,如果你想了解更多关于Oracle优化神技之临时表的内容请搜索CFSDN的文章或继续浏览相关文章,希望大家以后支持我的博客! 。
我有一台 MySQL 服务器和一台 PostgreSQL 服务器。 需要从多个表中复制或重新插入一组数据 MySQL 流式传输/同步到 PostgreSQL 表。 这种复制可以基于时间(Sync)或事
如果两个表的 id 彼此相等,我尝试从一个表中获取数据。这是我使用的代码: SELECT id_to , email_to , name_to , status_to
我有一个 Excel 工作表。顶行对应于列名称,而连续的行每行代表一个条目。 如何将此 Excel 工作表转换为 SQL 表? 我使用的是 SQL Server 2005。 最佳答案 这取决于您使用哪
我想合并两个 Django 模型并创建一个模型。让我们假设我有第一个表表 A,其中包含一些列和数据。 Table A -------------- col1 col2 col3 col
我有两个表:table1,table2,如下所示 table1: id name 1 tamil 2 english 3 maths 4 science table2: p
关闭。此题需要details or clarity 。目前不接受答案。 想要改进这个问题吗?通过 editing this post 添加详细信息并澄清问题. 已关闭 1 年前。 Improve th
下面两个语句有什么区别? newTable = orginalTable 或 newTable.data(originalTable) 我怀疑 .data() 方法具有性能优势,因为它在标准 AX 中
我有一个表,我没有在其中显式定义主键,它并不是真正需要的功能......但是一位同事建议我添加一个列作为唯一主键以随着数据库的增长提高性能...... 谁能解释一下这是如何提高性能的? 没有使用索引(
如何将表“产品”中的产品记录与其不同表“图像”中的图像相关联? 我正在对产品 ID 使用自动增量。 我觉得不可能进行关联,因为产品 ID 是自动递增的,因此在插入期间不可用! 如何插入新产品,获取产品
我有一个 sql 表,其中包含关键字和出现次数,如下所示(尽管出现次数并不重要): ____________ dog | 3 | ____________ rat | 7 | ____
是否可以使用目标表中的LAST_INSERT_ID更新源表? INSERT INTO `target` SELECT `a`, `b` FROM `source` 目标表有一个自动增量键id,我想将其
我正在重建一个搜索查询,因为它在“我看到的”中变得多余,我想知道什么 (albums_artists, artists) ( ) does in join? is it for boosting pe
以下是我使用 mysqldump 备份数据库的开关: /usr/bin/mysqldump -u **** --password=**** --single-transaction --databas
我试图获取 MySQL 表中的所有行并将它们放入 HTML 表中: Exam ID Status Assigned Examiner
如何查询名为 photos 的表中的所有记录,并知道当前用户使用单个查询将哪些结果照片添加为书签? 这是我的表格: -- -- Table structure for table `photos` -
我的网站都在 InnoDB 表上运行,目前为止运行良好。现在我想知道在我的网站上实时发生了什么,所以我将每个页面浏览量(页面、引荐来源网址、IP、主机名等)存储在 InnoDB 表中。每秒大约有 10
我在想我会为 mysql 准备两个表。一个用于存储登录信息,另一个用于存储送货地址。这是传统方式还是所有内容都存储在一张表中? 对于两个表...有没有办法自动将表 A 的列复制到表 B,以便我可以引用
我不是程序员,我从这个表格中阅读了很多关于如何解决我的问题的内容,但我的搜索效果不好 我有两张 table 表 1:成员 id*| name | surname -------------------
我知道如何在 ASP.NET 中显示真实表,例如 public ActionResult Index() { var s = db.StaffInfoDBSet.ToList(); r
我正在尝试运行以下查询: "insert into visits set source = 'http://google.com' and country = 'en' and ref = '1234
我是一名优秀的程序员,十分优秀!