database Oracle 中的临时表与全局临时表有什么区别？

What is the difference between a temporary table vs global temporary table in Oracle?

提问by MOZILLA

I have heard these two terms "temporary table" and "global temporary table" used pretty much in similar context.

我听说过“临时表”和“全局临时表”这两个术语在类似的上下文中使用得非常多。

What is the difference between the two?

两者有什么区别？

回答by Thomas Jones-Low

In Oracle there isn't any difference. When you create a temporary table in an Oracle database, it is automatically global, and you are required to include the "Global" key word.

在 Oracle 中没有任何区别。在 Oracle 数据库中创建临时表时，它会自动成为全局表，并且需要包含“全局”关键字。

The SQL standard, which defines how the term "GLOBAL TEMPORARY TABLE" is interpreted, allows for either a LOCAL or GLOBAL scope. This would allow for either a user specific table (LOCAL) or everyone (GLOBAL). Oracle implements only the GLOBAL version.

SQL 标准定义了如何解释术语“GLOBAL TEMPORARY TABLE”，允许使用 LOCAL 或 GLOBAL 范围。这将允许用户特定表 (LOCAL) 或每个人 (GLOBAL)。Oracle 仅实现了 GLOBAL 版本。

The data you put into an Oracle Temporary table is specific to your session. That is, only you can see your data even if there are 100 users all using the same table, and your data is deleted from the table when you disconnect (or when you commit the current transaction) depending upon table settings.

您放入 Oracle 临时表的数据特定于您的会话。也就是说，即使有 100 个用户都在使用同一张表，也只有您可以看到您的数据，并且您的数据会在您断开连接（或提交当前事务时）时从表中删除，具体取决于表设置。

Contrast this with MS SQL-Server, where temporary tables are local. If you create one, no one besides you knows that your temporary table exists. In Oracle, creating the temporary table allows everyone (well everyone with access to your schema) to see the table. When you log out of your session, the SQL-Server table is deleted and will need to be recreated for the next session. In Oracle, the temporary table is now a permanent part of your schema, even if the data isn't.

将此与 MS SQL-Server 进行对比，其中临时表是本地的。如果您创建一个，除了您之外没有人知道您的临时表存在。在 Oracle 中，创建临时表允许每个人（以及可以访问您的架构的每个人）查看该表。当您注销会话时，SQL-Server 表将被删除并且需要为下一个会话重新创建。在 Oracle 中，临时表现在是模式的永久部分，即使数据不是。

回答by Joby Thomas

Contrast this with MS SQL-Server, where temporary tables are local. If you create one, no one besides you knows that your temporary table exists. In Oracle, creating the temporary table allows everyone (well everyone with access to your schema) to see the table. When you log out of your session, the SQL-Server table is deleted and will need to be recreated for the next session. In Oracle, the temporary table is now a permanent part of your schema, even if the data isn't (if not so you can decide whether to preserve it). The Oracle supports only global temporary table saves you from having to create the table in each session; it 'exists' but it is empty, and its content is unique (and private) per session.

将此与 MS SQL-Server 进行对比，其中临时表是本地的。如果您创建一个，除了您之外没有人知道您的临时表存在。在 Oracle 中，创建临时表允许每个人（以及可以访问您的架构的每个人）查看该表。当您注销会话时，SQL-Server 表将被删除并且需要为下一个会话重新创建。在 Oracle 中，临时表现在是模式的永久部分，即使数据不是（如果不是，您可以决定是否保留它）。Oracle 只支持全局临时表，使您不必在每个会话中创建表；它“存在”但它是空的，并且它的内容在每个会话中都是唯一的（并且是私有的）。

回答by David Aldridge

Be aware that a global temporary table has no statistics associated with it, so look into whether the dynamic sampling level for the instance should be set to ensure that unanalyzed tables are sampled at parse time. Otherwise the heuristics can lead to a nasty execution plan.

请注意，全局临时表没有与之关联的统计信息，因此请查看是否应设置实例的动态采样级别以确保在解析时对未分析的表进行采样。否则，启发式方法可能会导致糟糕的执行计划。

回答by Lukasz Szozda

Just to add to existing answers about local and global temporary tables, from Oracle 18cthere will be trully "Private Temporary Tables":

只是为了添加有关本地和全局临时表的现有答案，从Oracle 18c那里将是真正的“私人临时表”：

Private temporary tables are temporary database objects that are automatically dropped at the end of a transaction or a session.A private temporary table is stored in memory and is visible only to the session that created it.

A private temporary table confines the scope of a temporary table to a session or a transaction, thus providing more flexibility in application coding, leading to easier code maintenance and a better ready-to-use functionality.

私有临时表是在事务或会话结束时自动删除的临时数据库对象。私有临时表存储在内存中，仅对创建它的会话可见。

私有临时表将临时表的范围限制为会话或事务，从而在应用程序编码方面提供更大的灵活性，从而更容易代码维护和更好的即用功能。

Demo from Oracle Live SQL: 18c private temporary tables:

来自Oracle Live SQL 的演示：18c 私有临时表：

-- Private temporary tables must be prefixed as per the database parameter 
-- 'private_temp_table_prefix' 

create private temporary table ORA$PTT_MY_TT ( x int );

-- The table is truly private. 
-- It does not even exist in the the data dictionary, only your session 

-- By default, the moment you commit, the table ceases to exist 
commit;

select * from ORA$PTT_MY_TT;
-- ORA-00942: table or view does not exist

-- This can be changed by specifying that the definition should be preserved 
create private temporary table ORA$PTT_MY_TT ( x int )  
on commit preserve definition;

insert into ORA$PTT_MY_TT  
select rownum from dual  
connect by level <= 30;

commit;

select count(*) from ORA$PTT_MY_TT;
-- 30 

db<>fiddle demo

db<>小提琴演示

回答by Stew S

Additionally, Oracle (global) temp tables are veryuseful when each of your users/sessions need to each see a different set of data. Just INSERT the records to your global temp table and let Oracle manage keeping one user's set from another's, as well as the cleanup. You don't need to query them with the user's ID, a session id or whatever.

此外，当您的每个用户/会话都需要查看不同的数据集时，Oracle（全局）临时表非常有用。只需将记录插入到您的全局临时表中，并让 Oracle 管理保持一个用户的设置与另一个用户的设置以及清理工作。您不需要使用用户 ID、会话 ID 或其他任何内容来查询它们。

We find them very handy.

我们发现它们非常方便。

回答by Nisar

There is no temporary table, only global temporary table. The idea of a global temporary table is that the definition exists and can be seen by all, but data is private for each session. You can also configure if the data is cleaned upon commit or only when the session ends.

没有临时表，只有全局临时表。全局临时表的想法是定义存在并且所有人都可以看到，但是每个会话的数据都是私有的。您还可以配置是在提交时清除数据还是仅在会话结束时清除数据。