什么是私有临时表？

简单来说，私有临时表就像是你的私人“草稿纸”。它只在你的数据库会话（一次连接）或一个事务（一组操作）期间存在，用完后Oracle会自动帮你清理干净，数据和表结构都会消失。它是Oracle 18c版本引入的新功能。

它的核心特点：

  • 私密性：只有创建它的会话能看到和使用它，其他用户完全看不见，非常安全。
  • 内存存储：数据主要存放在内存中，访问速度快。
  • 自动清理：无需手动删除，不会产生垃圾数据。

如何创建和使用？

创建这种表有个硬性规定：表名必须以 ORA$PTT_ 开头（这是默认设置，也可以更改）。

创建时的关键是指定它的生命周期，通过 ON COMMIT 子句来决定：

  1. ON COMMIT DROP DEFINITION - 事务级临时表
  • 生命期：仅存在于一个事务内。只要你执行了 COMMIT（提交）或 ROLLBACK（回滚），这个表就会立刻自动消失。
  • 适用场景：用于一些非常临时的中间计算或数据转换，操作完就扔。

示例：

-- 创建一个事务级的私有临时表
CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp1 (id INT, name VARCHAR2(100)) ON COMMIT DROP DEFINITION;

-- 插入数据并查询
INSERT INTO ora$ptt_temp1 VALUES (1, '张三');
SELECT * FROM ora$ptt_temp1; -- 此时能查到数据

-- 提交事务
COMMIT;

-- 提交后表就不存在了
SELECT * FROM ora$ptt_temp1; -- 会报错：表不存在
  1. ON COMMIT PRESERVE DEFINITION - 会话级临时表
  • 生命期：存在于整个数据库连接期间。即使你多次提交事务，表也一直在。直到你断开与数据库的连接，它才会被自动删除。
  • 适用场景：需要在一次连接中多次使用的中间结果集。

示例：

-- 创建一个会话级的私有临时表
CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp2 (id INT, name VARCHAR2(100)) ON COMMIT PRESERVE DEFINITION;

-- 插入数据并提交
INSERT INTO ora$ptt_temp2 VALUES (1, '李四');
COMMIT;

-- 提交后表依然存在，可以查询
SELECT * FROM ora$ptt_temp2; -- 仍然能查到数据

-- 断开数据库连接后再重新连接
-- 再次查询会发现表已经自动消失了
SELECT * FROM ora$ptt_temp2; -- 会报错：表不存在

私有临时表 vs. 全局临时表：我该用哪个？

Oracle其实有两种临时表，另一种叫“全局临时表”（Global Temporary Table）。它们的对比如下：

特性

全局临时表

私有临时表

命名

没有特殊要求

必须以 ORA$PTT_ 之类的前缀开头

谁可见

所有会话都看得到表结构（但只能看到自己的数据）

只有创建它的会话能看到，对别人完全隐形

表结构存储

永久存储在数据字典中

仅在内存中，会话结束就消失

能否建索引

可以

不可以

能否收集统计信息

可以

不可以

选择建议：

  • 选择私有临时表，如果：你需要一个完全私密、用完即弃的临时存储空间，而且不需要索引来加速查询。它非常轻量，适合短平快的操作。
  • 选择全局临时表，如果：
  • 你需要多个会话共享同一个临时表的结构。
  • 你的临时数据量很大，需要创建索引来提升查询性能。
  • 你希望优化器能有更准确的统计信息来生成高效的执行计划。
  • 你需要这个临时表的结构在数据库重启后依然存在。

重要提醒和总结

  • 不要滥用：在Oracle中，试图把一个大查询拆成多个步骤，用临时表来分步处理，通常比直接执行一个完整的大查询要慢。临时表应该用在合适的场景，比如存储真正的中间结果供后续处理。
  • redo日志：临时表虽然产生的重做日志（redo）很少，但并非为零。主要是为了回滚所需。如果你只进行插入和查询，影响极小。大量的更新和删除操作会产生更多日志。从12c开始，可以通过设置 TEMP_UNDO_ENABLED = TRUE 将undo信息也写入临时表空间，从而大幅减少redo日志的生成。
  • 统计信息：对于全局临时表，可以从12c开始设置会话级的统计信息，让优化器为不同会话中不同的数据量生成更合适的执行计划。

总而言之，私有临时表是一个轻量、私密、自动管理的临时工具，非常适合在单个会话或事务中存放短暂的中间数据。但对于更复杂或需要共享的场景，传统的全局临时表仍然是更强大的选择。

------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等