从Oracle到PG：比较PostgreSQL和Oracle GTT的临时表

开源软件联盟PostgreSQL分会 2019-08-26

1597

翻译：魏波审核：刘彦红

Hugo Dias

Hugo Dias是Severalnines的客座作家。多年来一直使用各种编程语言（如C，C ++，Python和Perl）开发软件，拥有PostgreSQL管理员的经验。

前言

临时表是大多数数据库中存在的概念，但通常有不同的工作方式。

本博客通过一些具体示例介绍了PostgreSQL（版本11）或Oracle（版本12c）数据库中此类表的技术特性。虽然这些表的目的基本相同，但它们的具体情况或实现和操作方式完全不同。

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

PostgreSQL中的临时表

在PostgreSQL中，这些对象仅对当前会话有效：它们是在同一会话中创建，使用和删除的：表的结构和数据仅对当前会话可见，因此其它会话无权访问在本会话上创建的临时表。

下面是一个创建临时表的简单示例：

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

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

creation index  tt_cusomer_idx_1 on tt_customer(customer_id);

由于这些表上的数据行也可以删除，因此可以通过执行vaccum命令释放占用的存储：

VACUUM VERBOSE tt_customer;

以下分析命令，对临时表收集统计信息：

ANALYZE VERBOSE tt_customer;

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

另一个重要的考虑因素是临时表与永久表可以重名：这种情况下，永久表仅在其模式作为前缀调用时被执行操作。

web_db=# BEGIN TRANSACTION;
BEGIN
web_db=# SELECT COUNT(*) FROM customers;
  count 
---------
 1030056
(1 row)
 
web_db=# CREATE TEMPORARY TABLE customers(
web_db(#   id INTEGER
web_db(# )
web_db-# ON COMMIT PRESERVE ROWS;
CREATE TABLE
web_db=# INSERT INTO customers(id) VALUES(1023);
INSERT 0 1
web_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 TABLE
web_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;
BEGIN
web_db=# CREATE TEMPORARY TABLE tt_customers(
web_db(#   id INTEGER
web_db(# )
web_db-# ON COMMIT DELETE ROWS;
CREATE TABLE
web_db=# SELECT COUNT(*) FROM tt_customers;
 count
-------
     0
(1 row)
web_db=# INSERT INTO tt_customers(id)
web_db-# SELECT customer_id
web_db-#   FROM web_app.orders
web_db-# WHERE order_dt <= NOW()-INTERVAL '6 MONTH';
INSERT 0 1030056
web_db=# SELECT COUNT(*) FROM tt_customers;
  count 
---------
 1030056
(1 row)
web_db=# DELETE FROM tt_customers c
web_db-# WHERE EXISTS(SELECT 1 
web_db(#                FROM web_app.users u JOIN web_app.login l 
web_db(#                       ON (l.user_id=u.user_id) 
web_db(#               WHERE u.customer_id=c.id 
web_db(#                 AND l.login_dt > NOW()-INTERVAL '6 MONTH'
web_db(#                 );
DELETE 194637
web_db=# SELECT COUNT(*) FROM tt_customers;
 count 
--------
 835419
(1 row)
web_db=# UPDATE web_app.customers as c SET BONUS=5
web_db-# FROM tt_customers t
web_db-# WHERE t.id = c.id;
UPDATE 835419
web_db=# SELECT COUNT(*) FROM tt_customers;
 count 
--------
 835419
(1 row)
web_db=# COMMIT TRANSACTION;
COMMIT
web_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保留行是默认行为

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

· 自动清理守护程序无法访问这些临时表，因此，不能vacuum 或analyze 临时表，但是会话中执行vacuum 或analyze命令可以达到清理或分析的目的。

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(*)
----------
         0
SQL>
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(*)
----------
   1030056
SQL>
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(*)
----------
    835419
SQL>
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(*)
----------
    835419
SQL>
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视图中，这一视图将查询的临时表tt_customers通过函数调用web_app.select_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）为使用这些表的查询选择最佳计划。

欢迎投稿

中国开源软件推进联盟PostgreSQL分会，欢迎大家积极投稿，向PGer分享自己的实践经验、心得体会，共建PG中国生态。

投稿邮箱：

press@postgresqlchina.com

原文请点击下方“阅读原文”获取，建议PC端阅读更佳！

postgresql

最后修改时间：2019-11-06 10:14:57

文章转载自开源软件联盟PostgreSQL分会，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

从Oracle到PG：比较PostgreSQL和Oracle GTT的临时表

评论