临时表

最新推荐文章于 2024-07-26 14:20:58 发布

weixin_34262482

最新推荐文章于 2024-07-26 14:20:58 发布

阅读量94

点赞数

文章标签：数据库 python 嵌入式

原文链接：https://my.oschina.net/u/1862478/blog/1833541

版权

2019独角兽企业重金招聘Python工程师标准>>>

临时表（Temporary table）用于保存事务或会话期间的中间结果集。临时表中保存的数据只对当前会话可见，所有会话都看不到其他会话的数据；即使当前会话已经提交（COMMIT）了数据，别的会话也看不到它的数据。对于临时表，不存在多用户并发问题，因为一个会话不会因为使用一个临时表而阻塞另一个会话。临时表比常规表生成的redo少得多。不过，由于临时表必须为其中包含的数据生成undo信息，所以也会生成一定的redo。UPDATE和DELETE会生成最多的undo；INSERT和SELECT生成的undo最少。

临时表会从当前登录用户的临时表空间分配存储空间，或者如果从一个定义者权限（definer right）过程访问临时表，就会使用该过程所有者的临时表空间。全局临时表实际上是表本身的一个模板。创建临时表的动作不涉及存储空间分配；不会为此分配初始（INITIAL）区段，这与常规表有所不同。对于临时表，运行时当一个会话第一次在临时表中放入数据时，才会为该会话创建一个临时段。由于每个会话会得到其自己的临时段（而不是一个现有段的一个区段），每个用户可能在不同的表空间为其临时表分配空间。

Oracle的临时表是“静态”定义的。每个数据库只创建一次临时表，而不是为数据库中的每个存储过程都创建一次。在Oracle中，临时表一定存在，它们作为对象放在数据字典中，但是在会话向临时表中放入数据之前，临时表看上去总是空。由于临时表是静态定义的，所以你能创建引用临时表的视图，还可以创建存储过程使用静态SQL来引用临时表，等等。临时表可以是基于会话的（临时表中的数据可以跨提交存在，即提交之前仍然存在，但是断开连接后再连接后再连接时数据就没有了），也可以是基于事务的（提交之后数据就消失）。下面这个例子显示了这两种不同的临时表。我使用SCOTT.EMP表作为一个模板：

scott@ORCL>create global temporary table temp_table_session
  2  on commit preserve rows
  3  as
  4  select * from scott.emp where 1=0
  5  /

表已创建。

ON COMMIT PRESERVE ROWS子句使得这是一个基于会话的临时表。在我的会话断开连接之前，或者我通过一个DELETE或TRUNCATE物理地删除行之前，这些行会一直存在于这个临时表中。只有我的会话能看到这些行；即使我已经提交（COMMIT），其他会话也无法看到“我的”行。

scott@ORCL>create global temporary table temp_table_transaction
  2  on commit delete rows
  3  as
  4  select * from scott.emp where 1=0
  5  /

表已创建。

ON COMMIT DELETE ROWS子句使得这是一个基于事务的临时表。我的会话提交时，临时表中的行就不见了。只需把分配给这个表的临时区段交回，这些行就会消失，在临时表的自动清除过程中不存在开销。

下面来看这两种类型之间的区别：

scott@ORCL>insert into temp_table_session select * from scott.emp;

已创建15行。

scott@ORCL>insert into temp_table_transaction select * from scott.emp;

已创建15行。

我们在各个TEMP表中方便放了15行，以下显示出我们可以“看到”这些行：

scott@ORCL>select session_cnt, transaction_cnt
  2  from ( select count(*) session_cnt from temp_table_session ),
  3  ( select count(*) transaction_cnt from temp_table_transaction );

SESSION_CNT TRANSACTION_CNT
----------- ---------------
         15              15

scott@ORCL>commit;

提交完成。

由于我们已经提交，所以仍可以看到基于会话的临时表行，但是看不到基于事务的临时表行：

scott@ORCL>select session_cnt, transaction_cnt
  2  from ( select count(*) session_cnt from temp_table_session ),
  3  ( select count(*) transaction_cnt from temp_table_transaction );

SESSION_CNT TRANSACTION_CNT
----------- ---------------
         15               0

scott@ORCL>disconnect

scott@ORCL>connect scott/123456
已连接。
scott@ORCL>select session_cnt, transaction_cnt
  2  from ( select count(*) session_cnt from temp_table_session ),
  3  ( select count(*) transaction_cnt from temp_table_transaction );

SESSION_CNT TRANSACTION_CNT
----------- ---------------
          0               0

由于此时已经开始了一个新的会话，所以两个表中的行都看不到了。

q 将所有全局临时表只创建一次，作为应用安装的一部分，就像是创建永久表一样。
q 在你的过程中，只需执行INSERT INTO TEMP(X, Y, Z) SELECT X, Y, Z FROM SOME_TABLE。

不要在运行时在你的存储过程中创建表。DDL是一种代价昂贵的操作：你要全力避免在运行时执行这种操作。一个应用的临时表应该在应用安装期间创建，绝对不要在运行时创建。
临时表可以有永久表的许多属性。它们可以有触发器、检查约束、索引等。但永久表的某些特性在临时表中并不支持，这包括：
q 不能有引用完整性约束。临时表不能作为外键的目标，也不能在临时表中定义外键。
q 不能有NESTED TABLE类型的列。
q 不能是IOT。
q 不能在任何类型的聚簇中。
q 不能分区。
q 不能通过ANALYZE表命令生成统计信息。

在所有数据库中，临时表的缺点之一是优化器不能正常地得到临时表的真实统计。使用基于代价的优化器（cost-based optimizer，CBO）时，有效的统计对于优化器的成败至关重要。如果没有统计信息，优化器就只能对数据的分布、数据量以及索引的选择性作出猜测。如果这些猜测是错的，为查询生成的查询计划（大量使用临时表）可能就不是最优的。在许多情况下，正确的解决方案是根本不使用临时表，而是使用一个 INLINE VIEW（要看INLINE VIEW的例子，可以查看前面运行的SELECT，它就有两个内联视图）。采用这种方式，Oracle可以访问一个表的所有相关统计信息，而且得出一个最优计划。

人们之所以使用临时表，是因为他们在其他数据库中了解到一个查询中联结太多的表是一件“不好的事情”。但在Oracle开发中，必须把这个知识忘掉。不要想着你比优化器要聪明，把本来一个查询分解成3个或4个查询，将其子结果存储在临时表中，然后再合并这些临时表；正确的做法是应该编写一个查询，直接回答最初的问题。在一个查询中引用多个表是可以的；Oracle中在这个方面不需要临时表的帮助。

不过在其他情况下，可以在进程中使用临时表，这是一种正确的做法。

由于会分析永久表，所以使用了CBO。但是临时表上没有统计信息（尽管可以分析临时表，但不会收集统计信息），因此CBO会对它做出很多“猜测”。作为一名开发人员，我知道可能的平均行数、数据的分布、查询选择的列等。我需要一种方法来告诉优化器这些更准确的猜测。可以有3中种方法向优化器提供关于全局临时表的统计信息。一种方法是通过动态采样，另一种方法是使用DBMS_STATS包，它有两种做法。下面首先来看动态采样。

动态采样（dynamic sampling）是优化器的一种功能，硬解析一个查询时，会扫描数据库中的段（采样），收集有用的统计信息，来完成这个特定查询的优化。这与硬解析期间完成一个“缩型收集统计”命令很类似。默认设置已经从level 1提升到level 2，采用level 2，优化器在结算查询计划之前，会对优化器处理的查询中引用的所有未分析的对象完成动态采样。在Oracle9i Release 2中可以使用一个ALTER SESSION|SYSTEM命令，从而能有Oracle 10g默认行为，或者可以使用动态采样提示，如下：

scott@ORCL>create global temporary table gtt
  2  as
  3  select * from scott.emp where 1=0;

表已创建。

scott@ORCL>insert into gtt select * from scott.emp;

已创建15行。

scott@ORCL>set autotrace traceonly explain
scott@ORCL>select /*+ first_rows */ * from gtt;

执行计划
----------------------------------------------------------
Plan hash value: 917624683

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    15 |  1305 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| GTT  |    15 |  1305 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

scott@ORCL>select /*+ first_rows dynamic_sampling(gtt 2) */ * from gtt;

执行计划
----------------------------------------------------------
Plan hash value: 917624683

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    15 |  1305 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| GTT  |    15 |  1305 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

scott@ORCL>set autotrace off

通过使用动态采样，估计的基数会与实际更为接近（这会得到总体上更好的查询计划）。使用 level 2设置，优化器会很快地扫描表，对表的真实大小得出更实际的估计。默认就会发生动态采样：

scott@ORCL>set autotrace traceonly explain
scott@ORCL>select * from gtt;

执行计划
----------------------------------------------------------
Plan hash value: 917624683

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    15 |  1305 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| GTT  |    15 |  1305 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

scott@ORCL>set autotrace off

动态采样不是免费的，由于必须在查询解析时完成，所以存在相当大的代价。如果能提前收集适当的代表性统计信息，就可以避免在硬解析时执行动态采样。为此可以使用DBMS_STATS。

使用DBMS_STATS收集代表性统计信息有3种方法。第一种方法是利用GATHER_SCHEMA_STATS或 GATHER_DATABASE_STATS调用来使用DBMS_STATS。这些过程允许你传入一个参数GATHER_TEMP，这是一个布尔值，默认为FALSE。设置为TRUE时，所有ON COMMIT PRESERVE ROWS全局临时表都会收集和存储统计信息（这个技术在ON COMMIT DELETE ROWS表上不可行）。考虑以下情况（注意这在一个空模式中完成：除了你创建的对象之外没有其他对象）：

test@ORCL>create table emp as select * from scott.emp;

表已创建。

test@ORCL>create global temporary table gtt1 ( x number )
  2  on commit preserve rows;

表已创建。

test@ORCL>create global temporary table gtt2 ( x number )
  2  on commit delete rows;

表已创建。

test@ORCL>insert into gtt1 select user_id from all_users;

已创建41行。

test@ORCL>insert into gtt2 select user_id from all_users;

已创建41行。

test@ORCL>exec dbms_stats.gather_schema_stats( user );

PL/SQL 过程已成功完成。

test@ORCL>select table_name, last_analyzed, num_rows from user_tables;

TABLE_NAME                     LAST_ANALYZED    NUM_ROWS
------------------------------ -------------- ----------
EMP                            21-6月 -18             15
GTT1
GTT2

可以看到，在这种情况下，只会分析EMP表：两个全局临时表将被忽略。

可以如下调用GATHER_SCHEMA_STATS（带GATHER_TEMP => TRUE）来改变这种行为：

test@ORCL>insert into gtt2 select user_id from all_users;

已创建41行。

test@ORCL>exec dbms_stats.gather_schema_stats( user, gather_temp=>TRUE );

PL/SQL 过程已成功完成。

test@ORCL>select table_name, last_analyzed, num_rows from user_tables;

TABLE_NAME                     LAST_ANALYZED    NUM_ROWS
------------------------------ -------------- ----------
EMP                            21-6月 -18             15
GTT1                           21-6月 -18             41
GTT2                           21-6月 -18              0

注意，ON COMMIT PRESERVE ROWS表会有正确的统计，但是ON COMMIT DELETE ROWS表没有。DBMS_STATS将提交，而这会擦除ON COMMIT DELETE ROWS表中的所有信息。不过，要注意，现在GTT2确实有统计信息了，这本身并不好，因为统计信息太离谱了！运行时表居然只有0行，这实在是让人怀疑。所以，如果使用这种方法，要注意两点：
q 要保证在收集统计信息的会话中用代表性数据填充全局临时表。如果做不到，在DBMS_STATS看来它们就是空的。
q 如果有ON COMMIT DELETE ROWS全局临时表，就不应该使用这种方法，因为这样会收集到不正确的值。

对于ON COMMIT PRESERVE ROWS全局临时表，还可以采用第二种技术：直接在表上使用GATHER_TABLE_STATS。要像我们刚才那样填充全局临时表，然后在这个全局临时表上执行GATHER_TABLE_STATS。注意还是像前面一样，对于ON COMMIT DELETE ROWS全局临时表，这种技术还是不能用，同样是因为存在前面所述的问题。

使用DBMS_STATS的最后一种技术是通过一个手动过程用临时表的代表性统计信息填充数据字典。例如，如果平均来讲临时表中的行数是500，而且行的平均大小是100字节，块数为7，则只需如下使用DBMS_STATS：

test@ORCL>create global temporary table t ( x int, y varchar2(100) );

表已创建。

test@ORCL>begin
  2  dbms_stats.set_table_stats( ownname => USER,
  3                                             tabname => 'T',
  4                                             numrows => 500,
  5                                             numblks => 7,
  6                                             avgrlen => 100 );
  7  end;
  8  /

PL/SQL 过程已成功完成。

test@ORCL>select table_name, num_rows, blocks, avg_row_len
  2  from user_tables
  3  where table_name = 'T';

TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
T                                     500          7         100

现在，优化器不会使用它自己的最优猜测，而会使用我们给出的最优猜测。

临时表小结
如果应用中需要临时存储一个行集由其他表处理（可能对应一个会话，也可能对应一个事务），临时表就很有用。不要把临时表作为一个分解大查询的方法，即拿到一个大查询，把它“分解”为几个较小的结果集，然后再把这些结果集合并在一起。Oracle中如果将一个查询分解为较小的临时表查询，与原来的一个查询相比，只会执行得更慢。

临时表会生成少量的redo，但是确实还是会生成redo，而且没有办法避免。这些redo是为回滚数据生成的，而且在最典型的情况下，可以忽略不计。如果只是对临时表执行INSERT和SELETE，生成的redo量几乎注意不到。只有对临时表执行大量DELETE和UPDATE时，才会看到生成大量的 redo。
如果精心设计，可以在临时表上生成CBO使用的统计信息；不过，可以使用DBMS_STATS包对临时表上的统计给出更好的猜测，或者由优化器使用动态采样在硬解析时动态收集。

转载于:https://my.oschina.net/u/1862478/blog/1833541