一文讲透数仓临时表的用法

摘要:临时作为一个SQL标准中的类型,各个厂商在实现时,往往却不相同,甚至行为上也存在差异,本文小结下GaussDB(DWS)的临时使用数据恢复场景

本文分享自华sqlserver数据为云社区​​《GaussDB(DWS)临时表小结》​​,作者: sincatter 。

语法介绍

如下为创建表的基本语法(详见手册):

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name      ({ column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]         | table_constraint         | LIKE source_table [ like_option [...] ] }         [, ... ])     [ WITH ( {storage_parameter = value} [, ... ] ) ]     [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]     [ COMPRESS | NOCOMPRESS ]     [ TABLESPACE tablespace_name ]     [ DISTRIBUTE BY { REPLICATION | { HASH ( column_name [,...] ) } } ]     [ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ];

其中临时表相关的关键字有:

  • [ GLOBAL | LOCAL ]

创建临时表时可以在TEMP或TEMPORARYsqlsever前指定GLOBAL或LOCAL关键字。目前GaussDB(DWS)设立这两个关键字,仅仅是为了兼容SQL标准,实际行为上无论指定的是GLOBAL还是LOCAL数据库增删改查基本语句,GaussDB(DWS)都只会创建为本地临时表,即只有LOCAL关键元数据的作用字是有效的。

  • { TEMPORARY | TEMP }

TEMP和TESQLMPORARY等价。如果指定TEMP或TEMPORARY关键字,则创建的表为临时表。临时表行为上数据分析的主要特征为只在当前会话可见,本会话结束后会自动删除。由于临时表只在当前会话创建,对于涉及苏卿陆容渊免费阅读对临时表操作的DDL语句,很容易产生DDL失败的报错。因此,建议DDL语句中不要对临时表进行操作。

ON COMMIT选项决定在事务中执行创建临时表操作,当事务提交时,此临时表的后续操作。有以舍曲林的功效与作用的副作用下三个选项:

  • PRESERVE ROWS(缺省值):提交时不对临时表做元数据的定义任何操作,临时表及其表数据保持不变。
  • DELETE ROWS:提交时删除临时表中数据。
  • DROP:提交时删除此临时表。

原理介绍

GaussDB(DWS)的临时表机制继承自PostgreSQL,临时表在元数据和数据存储上与普通表基本无差异数据分析师,具体来说,临时表是通过建表时将其Schema指定为与session id相关的一个schema,其他session实际上也是可以在系统表中查看到舍曲林的功效与作用的副作用当前临时表的元数据数据库系统的核心是。Gauss苏卿陆容渊DB(DWS)会利用schema进行临时表的不同session间隔离。这里通过两个现象去说明这个机制:

现象一:

一个session_1创建一个临时表:

postgres=#  create temp table tt1(a int); CREATE TABLE postgres=# \d                                          List of relations                   Schema                  | Name | Type  | Owner |             Storage               ------------------------------------------+------+-------+-------+----------------------------------  pg_temp_coordinator1_2_4_139820525504256 | tt1  | table | xucw  | {orientation=row,compression=no} (1 row) postgres=# select relname,relnamespace from pg_class where relname like 'tt%';  relname | relnamespace  ---------+--------------  tt1     |        24600 (1 row)

另外一个session_2,可以一样可以通过pg_class查数据漫游是什么意思看临时表的表结构:

postgres=#  select relname,relnamespace from pg_class where relname like 'tt%';  relname | relnamespace  ---------+--------------  tt1     |        24600 (1 row)

但session_2中是无法查看当前临sqlserver面试题表中的数据:

postgres=# select * from pg_temp_coordinator1_2_4_139820525504256.tt1; ERROR:  Can only access temp objects of the current session. LINE 1: select * from pg_temp_coordinator1_2_4_139820525504256.tt1;

现象二:

创建一个临时表后,再根据这个临时表的schema,去创建一个相同schemsqlserver面试题a的普通表:

postgres=# create temp table tt1(a int); CREATE TABLE postgres=# \d                                          List of relations                   Schema                  | Name | Type  | Owner |             Storage               ------------------------------------------+------+-------+-------+----------------------------------  pg_temp_coordinator1_2_3_139820525504256 | tt1  | table | xucw  | {orientation=row,compression=no} (1 row) postgres=# create table pg_temp_coordinator1_2_3_139820525504256.tt2(a int); CREATE TABLE postgres=# select relname,relnamespace from pg_class where relname like 'tt%';  relname | relnamespace  ---------+--------------  tt1     |        24592  tt2     |        24592 (2 rows)

随后,退出当前session,重新连接查看表状态,我们会神元数据是指奇发现之前创建的临时表tt1消失的同时,创建的普通表tt2也一样消失了。相关子查询

postgres=# select relname,relnamespace from pg_class where relname like 'tt%';  relname | relnamespace  ---------+-------------- (0 rows)

使用注意

1. 与数据库使SQL+Server用永久表相比,使用临时表可以提高性能,但存在丢失数据的风险。临时表只在当前会话可见,本会话结束后将自动删除。如果数据丢失是不可接受的,请使用永久表。

2. 临时表对应的sechema在搜索数据废土路径中的优先级是高于其他sechma的,连接池的作用及原理即临时表对应schema具有第一搜索优先级。

3. \parallel模式不支持创建元数据是什么意思临时表!如需使用临时表,需要在开启parallel之前就创建好,并在parallel内部使用。parallel内子查询的sql语句部创建的临时表不会生效。

4. PG_T数据库系统的核心是OTAL_USER_RESOURCE_INFO视图中的used_temp_ssqlserver无法连接到服务器pace和total_temp_space可以查看当前临时表的相关空间使用情况

5. 创建临时表时,会同步创建临时Schem宋庆玲a,这些临时Schema的名称类似于pg_temp_*和pg_toast_temp_*

6. CN Retry功能开启时会为临时表数据记录sqlserver创建表日志,子查询为保证数据一致性,在使用临时表时不能切换CN Retry开关状态,保持使用临时表子查询的sql语句的会数据库系统的核心是话中CN Ret汉子查询r鸽子查询y开关始终处于打开状态或者关闭状态。sqlserver创建表

7. 临时表和非日志表的存储方式建议和基表相同。当基表为行存(列sqlserver2008安装步骤教程存)表时,临时表和非日志表也推荐创建为行存(列存)表,可以避免行列混合关联带来的高计算代价

8. 如果上层应用,使用了连接池机制连接GaussDB(DWS),在使用临时表时,强烈建议将连接归还连接池之前,将临时表主动删除,避免造成连接未断开导致的数据异常

9. 在每个会话第一次使用临时表之前可以改变temp_buffers的值,之后的设置将是无效的

10. a子查询和关联查询哪个效率高utoanalyze不支持数据库对带有ONCOMMIT [DELETE ROWS|DROP]选项的临时表触发autoanalyze,如需收集,需用户手动执行analyze操作

11. 如果创建视图时包含临时表,则该视图会自动转为临时视图

典型场景

临时存储

临时表可以减少冗余中间表的存在,在一些复杂操作时,往往需要借助一些中间表去完成功能,但一般来说普通表的创建子查询的sql语句是需要数据库管理员来统计创建维护的。元数据的作用临时表的存在就允许中间表用完即清,减少数据库系统中冗余表的存在。另外,临时表在使用时数据是session间隔离sqlserver和mysql区别的,其他ses骗子查询sion不能看到当前session的数据,数据安全性在一定元数据管理程度上也更好。

提升性能

对于过于复杂并且不易通过普通优化方法调整骗子查询性能的SQLsqlserver怎么读可以考虑拆分的方法,把SQLsql数据库中某一部分拆分成独立的SQL并把执行结果存入临时表,拆分常见的场景包括但不限于:

  • 作业中多个SQL有同样的子查询,并且子查询数据量较大。
  • Plan cost计算不准苏卿陆容渊数据库设计导致子查询hash bucket太小,比如实际数据1000W行,hash bucket只有1000。
  • 函数(如substr,to_number)导致大数据量子查询选择度计算不准。
  • 多DN环境下对大表做broadcast的子查询。

​​点击关注,第一时间了解华为云新鲜技术~​​