PostgreSQL 和 Oracle GTT 临时表对比

在大多数SGBD中，经常用到临时表，尽管它们的工作方式不同。

本博客描述了PostgreSQL (version 11)或Oracle (version 12c)数据库中此类表的技术特性，并给出了一些具体的例子。虽然这些表的目的对于所有SGBD都是相同的，但是它们的具体内容，或者实现和操作的方式是完全不同的。

开发人员或数据库管理员可以使用该特性来存储进一步处理所需的中间结果，以便提供良好的性能指标。

postgresql中的临时表

在PostgreSQL中，临时表只有在当前会话是有效的:它们在同一会话中创建、使用和删除:表的结构和数据只对当前会话是可见的,因此一个会话无法获得在另一个会话创建的临时表。

下面是一个创建临时表的简单例子:

CREATE TEMPORARY TABLE tt_customer(     customer_id INTEGER)ON COMMIT DELETE ROWS;
复制

临时表是在临时模式pg_temp_nn中创建的，可以在这些表上创建索引:

create index  tt_cusomer_idx_1 on tt_customer(customer_id)
复制

由于这些表上的数据行也可以删除，所以可以通过执行vaccum命令释放被占用的存储空间:

VACUUM VERBOSE tt_customer
复制

ANALYZE 命令也可以在临时表上执行，以便收集统计数据:

ANALYZE VERBOSE tt_customer;
复制

这两个命令都可以作为SQL命令对这类表执行，但是执行它们的autovaccum守护进程不会对临时表执行操作。

另一个需要考虑的要点是，它与具有相同名称的永久表和临时表有关:一旦发生这种情况，只有在以模式作为前缀调用永久表时才会考虑到它。

web_db=# BEGIN TRANSACTION;BEGINweb_db=# SELECT COUNT(*) FROM customers;  count--------- 1030056(1 row) web_db=# CREATE TEMPORARY TABLE customers(web_db(#   id INTEGERweb_db(# )web_db-# ON COMMIT PRESERVE ROWS;CREATE TABLEweb_db=# INSERT INTO customers(id) VALUES(1023);INSERT 0 1web_db=# SELECT COUNT(*) FROM customers; count-------     1(1 row)web_db=# \dt *customers*                  List of relations  Schema   |         Name         | Type  |  Owner  -----------+----------------------+-------+---------- pg_temp_5 | customers            | table | postgres web_app   | customers            | table | postgres web_app   | customers_historical | table | postgres(3 rows)web_db=# DROP TABLE customers;DROP TABLEweb_db=# \dt *customers*                 List of relations Schema  |         Name         | Type  |  Owner  ---------+----------------------+-------+---------- web_app | customers            | table | postgres web_app | customers_historical | table | postgres(2 rows)web_db=# SELECT COUNT(*) FROM web_app.customers;  count--------- 1030056(1 row)web_db=# SELECT COUNT(*) FROM customers;  count--------- 1030056(1 row)
复制

临时表的开发人员提示

此示例的目的是为一年以上未购买或登录的客户分配奖金,所以脚本开发人员转而使用子查询作为一个可能的解决方案(或CTE语句)可以使用临时表(通常是速度比使用子查询更快):

web_db=# BEGIN TRANSACTION;BEGINweb_db=# CREATE TEMPORARY TABLE tt_customers(web_db(#   id INTEGERweb_db(# )web_db-# ON COMMIT DELETE ROWS;CREATE TABLEweb_db=# SELECT COUNT(*) FROM tt_customers; count-------     0(1 row)web_db=# INSERT INTO tt_customers(id)web_db-# SELECT customer_idweb_db-#   FROM web_app.ordersweb_db-# WHERE order_dt <= NOW()-INTERVAL '6 MONTH';INSERT 0 1030056web_db=# SELECT COUNT(*) FROM tt_customers;  count--------- 1030056(1 row)web_db=# DELETE FROM tt_customers cweb_db-# WHERE EXISTS(SELECT 1web_db(#                FROM web_app.users u JOIN web_app.login lweb_db(#                       ON (l.user_id=u.user_id)web_db(#               WHERE u.customer_id=c.idweb_db(#                 AND l.login_dt > NOW()-INTERVAL '6 MONTH'web_db(#                 );DELETE 194637web_db=# SELECT COUNT(*) FROM tt_customers; count-------- 835419(1 row)web_db=# UPDATE web_app.customers as c SET BONUS=5web_db-# FROM tt_customers tweb_db-# WHERE t.id = c.id;UPDATE 835419web_db=# SELECT COUNT(*) FROM tt_customers; count-------- 835419(1 row)web_db=# COMMIT TRANSACTION;COMMITweb_db=# SELECT COUNT(*) FROM tt_customers; count-------     0(1 row)
复制

DBA临时表技巧

数据库管理员的一个典型任务是清除包含不再需要的数据的任何大型表。这需要非常快地完成，而且经常发生。标准方法是将此数据移动到另一个模式中的历史表或访问频率较低的数据库。

因此，为了执行这种移动，由于性能问题，最好的解决方案可能是使用临时表:

CREATE TEMPORARY TABLE tt_customer(     customer_id INTEGER)ON COMMIT DROP;
复制

在本例中，使用DROP选项创建了临时表，这意味着将在当前事务块的末尾删除临时表。

以下是PostgreSQL临时表的一些其他重要信息：

• 临时表在会话结束时自动删除，或者如上例所示，在当前事务结束时自动删除

• 当临时表存在时，具有相同名称的永久表对当前会话不可见，除非它们使用模式限定名称引用

• 在临时表上创建的任何索引也是临时的

• ON COMMIT preserve rows是默认行为

• 可选的，可以在TEMPORARY或TEMP之前写入GLOBAL或LOCAL。目前这在PostgreSQL中没有任何区别，并且已被弃用

• autovacuum守护程序无法访问这些表，因此无法对临时表进行vacuum或者analyze，但是，如前所示，autovacuum和analyze命令可用作SQL命令。

Oracle中的全局临时表（GTT）

这种表在Oracle中称为全局临时表（或GTT）。这些对象在数据库中是持久的，可以通过以下特征进行汇总：

• 该结构对所有用户都是静态且可见的，但其内容仅对当前会话可见

• 它可以在特定模式中创建（默认情况下将由发出命令的用户拥有）并且它们构建在TEMP表空间中

• 在数据库中创建后，无法在每个会话中再次创建，但会话管理的数据对其他会话不可见

• 可以创建索引和生成统计信息

• 由于这些表的结构也在数据库中定义，因此无法将其名称分配给永久表（在Oracle中，两个对象即使是不同的类型也不能具有相同的名称）

• 不要生成太多的重做日志和撤销开销-与永久表相比(仅出于这些原因，在12c之前的任何版本中，GTT的使用都更快)。从12c版本开始，就有了临时撤消的概念，允许将GTT的撤销写入临时表空间，从而减少撤销和重做。

按照PostgreSQL中提供的相同示例，GTT的创建非常相似：

CREATE GLOBAL TEMPORARY TABLE tt_customer ( customer_id NUMBER ) ON COMMIT DELETE ROWS;
复制

也可以创建索引的。

creation index tt_cusomer_idx_1 on tt_customer(customer_id)
复制

在Oracle 12c之前，全局临时表的统计信息生成具有全局方式的行为：在特定会话中为特定GTT生成的统计信息是可见的，并用于其他会话，但是，从版本12c开始，每个会话都可以生成自己的统计信息。

首先，要将首选项global_temp_table_stats设置为session ：

exec dbms_stats.set_table_prefs(USER,'TT_CUSTOMER','GLOBAL_TEMP_TABLE_STATS','SESSION');
复制

然后生成统计数据：

exec dbms_stats.gather_table_stats(USER,'TT_CUSTOMER');
复制

可以通过执行以下查询来检查现有的全局临时表：

select table_name from all_tables where temporary = 'Y';
复制

全局临时表（GTT）的开发人员提示

遵循PostgreSQL部分的示例：要为一年以上未购买或登录的客户分配奖金，在Oracle中使用全局临时表具有与PostgreSQL相同的目标：在资源使用或执行速度方面实现更好的性能。

SQL> SELECT COUNT(*) FROM tt_customers;  COUNT(*)----------         0SQL>SQL> INSERT INTO tt_customers(id)  2  SELECT customer_id  3    FROM orders  4  WHERE order_dt <= ADD_MONTHS(SYSDATE,-6);1030056 rows created.SQL>SQL> SELECT COUNT(*) FROM tt_customers;  COUNT(*)----------   1030056SQL>SQL> DELETE FROM tt_customers c  2  WHERE EXISTS(SELECT 1  3                 FROM users u JOIN login l  4                        ON (l.user_id=u.user_id)  5                WHERE u.customer_id=c.id  6                  AND l.login_dt > ADD_MONTHS(SYSDATE,-6)  7                  );194637 rows deleted.SQL>SQL> SELECT COUNT(*) FROM tt_customers;  COUNT(*)----------    835419SQL>SQL> UPDATE CUSTOMERS c SET BONUS=5  2  WHERE EXISTS(SELECT 1 FROM tt_customers tc WHERE tc.id=c.id);835419 rows updated.SQL>SQL> SELECT COUNT(*) FROM tt_customers;  COUNT(*)----------    835419SQL>SQL> COMMIT;Commit complete.SQL>SQL> SELECT COUNT(*) FROM tt_customers;  COUNT(*)----------         0 SQL>
复制

默认情况下，在Oracle中，SQL PLSQL块/语句隐式启动事务。

全局临时表（GTT）的DBA技巧

由于全局临时表不存在语句drop ，因此创建表的命令与前一个表相同：

CREATE GLOBAL TEMPORARY TABLE tt_customer(     customer_id NUMBER)ON COMMIT DELETE ROWS;
复制

Oracle中用于清除客户表的等效代码片段如下：

SQL> INSERT INTO tt_customers(id)  2  SELECT l.user_id  3    FROM users u JOIN login l  4           ON (l.user_id=u.user_id)  5   WHERE l.login_dt < ADD_MONTHS(SYSDATE,-12);194637 rows created.SQL>SQL> INSERT INTO tt_customers(id)  2  SELECT user_id  3    FROM web_deactive;2143 rows created.SQL>SQL> INSERT INTO tt_customers(id)  2  SELECT user_id  3    FROM web_black_list;4234 rows created.SQL>SQL> INSERT INTO customers_historical(id,name)  2  SELECT c.id,c.name  3  FROM customers c,  4  tt_customers tc  5  WHERE tc.id = c.id;201014 rows created.SQL>SQL> DELETE FROM customers c  2  WHERE EXISTS (SELECT 1 FROM  tt_customers tc WHERE tc.id = c.id );201014 rows deleted.
复制

pg_global_temp_tables库

如上所述，PostgreSQL中的临时表不能使用符号schema.table调用，因此pg_global_temp_tables库 （github上有一些类似的库）这是一种非常有用的解决方法，可用于从Oracle到PostgreSQL的数据库迁移。

为了在查询或存储过程中保留Oracle表示法schema.temporary_table ：

SELECT c.id,c.nam    FROM web_app.tt_customers tc,                 Web_app.customers c    WHERE c.id = tc.id
复制

它允许使用模式表示法保留代码上的临时表。

基本上，它包含一个视图：web_app.tt_customers在它应该具有临时表的模式下创建，该视图将通过名为web_app.select_tt_customers的函数查询临时表tt_customers ：

CREATE OR REPLACE VIEW WEB_APP.TT_CUSTOMERS AS  SELECT * FROM WEB_APP.SELECT_TT_CUSTOMERS();
复制

此函数返回临时表的内容：

CREATE OR REPLACE FUNCTION WEB_APP.SELECT_TT_CUSTOMERS() RETURNS TABLE(ID INR, NAME VARCHAR) AS $$BEGIN    CREATE TEMPORARY TABLE IF NOT EXISTS TT_CUSTOMERS(ID INT, NAME) ON COMMIT DROP;    RETURN QUERY SELECT * FROM TT_CUSTOMERS;END;
$$ LANGUAGE PLPGSQL;
复制

摘要

临时表主要用于存储中间结果，从而避免复杂和繁重的计算，

以下是PostgreSQL或Oracle中临时表的一些特性：

• 它可以在视图中使用

• 它可以使用TRUNCATE命令

• 它无法分区

• 不允许对临时表使用外键约束

• 这种表是CTE（公用表表达式）的替代方案，也称为Oracle专业人员的WITH子句

• 在安全性和隐私方面，这些表是有价值的，因为数据仅对当前会话可见

• 会话/事务结束后，临时表将自动删除（在PostgreSQL中）或删除（在Oracle中）。

对于PostgreSQL中的临时表，建议不要在临时表中使用永久表的相同名称。在Oracle方面，最好为GTT中包含大量数据的会话生成统计数据，以迫使基于成本的优化器（CBO）为使用这些表的查询选择最佳计划。

本文翻译自：https://severalnines.com/database-blog/comparing-temporary-tables-postgresql-oracle-gtt