再上一篇:10.7有序散列聚簇表
上一篇:10.8嵌套表
主页
下一篇:10.10对象表
再下一篇:10.11小结
文章列表

10.9临时表

Oracle 9i 10g编程艺术:深入数据库体系结构

临 时表(Temporary table)用于保存事务或会话期间的中间结果集。临时表中保存的数据只对当前 会话可见,所有会话都看不到其他会话的数据;即使当前会话已经提交 (COMMIT)了数据,别的会话也看 不到它的数据。对于临时表,不存在多用户并发问题,因为一个会话不会因为使用一个临时表而阻塞另一 个会话。即使我们 “锁住”了临时表,也不会妨碍其他会话使用它们自己的临时表。我们在第 9章了解到 , 临时表比常规表生成的redo少得多。不过,由于临时表必须为其中包含 的数据生成undo信息,所以也会 生成一定的redo。UPDATE和DELETE会生成最多的undo;INSERT和SELECT生成的undo最 少。
临 时表会从当前登录用户的临时表空间分配存储空间,或者如果从一个定义者权限(definer right) 过程访问临时表,就会使用该过程所有者的临时表空间。全局临时表实际上是表本身的一个模板。创建临 时表的动作不涉及存储空间分配;不会为此分 配初始(INITIAL)区段,这与常规表有所不同。对于临时 表,运行时当一个会话第一次在临时表中放入数据时,才会为该会话创建一个临时段。由于每个会 话会得 到其自己的临时段(而不是一个现有段的一个区段),每个用户可能在不同的表空间为其临时表分配空间。 USER1的临时表空间可能设置为TEMP1, 因此他的临时表会从这个表空间分配。USER2可能把TEMP2作为 其临时表空间,他的临时表就会从那里分配。
Oracle的 临时表与其他关系数据库中的临时表类似,这样区别只是:Oracle的临时表是“静态”定 义的。每个数据库只创建一次临时表,而不是为数据库中的每个存储 过程都创建一次。在Oracle中,临 时表一定存在,它们作为对象放在数据字典中,但是在会话向临时表中放入数据之前,临时表看上去总是 空。由于临时表是 静态定义的,所以你能创建引用临时表的视图,还可以创建存储过程使用静态 SQL来引 用临时表,等等。临时表可以是基于会话的(临时表中的数据可以跨提交存 在,即提交之前仍然存在,但 是断开连接后再连接后再连接时数据就没有了),也可以是基于事务的(提交之后数据就消失)。下面这 个例子显示了这两种不同的临 时表。我使用SCOTT.EMP表作为一个模板:

ops$tkyte@ORA10G> create global temporary table temp_table_session
2 on commit preserve rows
3 as
4 select * from scott.emp where 1=0
5 /
Table created.
ON COMMIT PRESERVE ROWS子句使得这是一个基于会话的临时表。在我的会话断开连接之前,或者我 通过一个DELETE或TRUNCATE物理地删除行之前,这些行会一直存在于这个临时表中。只有我的会话能看 到这些行;即使我已经提交(COMMIT),其他会话也无法看到“我的”行。

ops$tkyte@ORA10G> create global temporary table temp_table_transaction

2 on commit delete rows
3 as
4 select * from scott.emp where 1=0
5 /
Table created.
ON COMMIT DELETE ROWS子句使得这是一个基于事务的临时表。我的会话提交时,临时表中的行就不 见了。只需把分配给这个表的临时区段交回,这些行就会消失,在临时表的自动清除过程中不存在开销。
下面来看这两种类型之间的区别:

ops$tkyte@ORA10G> insert into temp_table_session select * from scott.emp;
14 rows created.
ops$tkyte@ORA10G> insert into temp_table_transaction select * from scott.emp;
14 rows created.
我们在各个TEMP表中方便放了14行,以下显示出我们可以“看到”这些行:

ops$tkyte@ORA10G> 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
---------------------- -----------------------------
14 14

ops$tkyte@ORA10G> commit; 由于我们已经提交,所以仍可以看到基于会话的临时表行,但是看不到基于事务的临时表行: ops$tkyte@ORA10G> 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
----------- ---------------
14 0 ops$tkyte@ORA10G>

ops$tkyte@ORA10G> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.3.0
With the Partitioning, OLAP and Data Mining options ops$tkyte@ORA10G> connect /

Connected. 由于此时已经开始了一个新的会话,所以两个表中的行都看不到了: ops$tkyte@ORA10G> 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
如果你曾在SQL Server和/或Sybase中用过临时表,现在所要考虑的主要问题是:不应该执行SELECT X, Y, Z INTO #TEMP FROM SOME_TABLE来动态创建和填充一个临时表,而应该:
q 将所有全局临时表只创建一次,作为应用安装的一部分,就像是创建永久表一样。
q 在你的过程中,只需执行INSERT INTO TEMP(X, Y, Z) SELECT X, Y, Z FROM SOME_TABLE。
归根结底,这里的目标是:不要在运行时在你的存储过程中创建表。这不是Oracle中使用临时表的 正确做法。DDL是一种代价昂贵的操作:你要全力避免在运行时执行这种操作。一个应用的临时表应该在 应用安装期间创建,绝对不要在运行时创建。
临时表可以有永久表的许多属性。它们可以有触发器、检查约束、索引等。但永久表的某些特性在临 时表中并不支持,这包括:
q 不能有引用完整性约束。临时表不能作为外键的目标,也不能在临时表中定义外键。
q 不能有NESTED TABLE类型的列。在Oracle 9i及以前版本中,VARRAY类型的列也不允许; 不过Oracle 10g中去掉了这个限制。
q 不能是IOT。
q 不能在任何类型的聚簇中。
q 不能分区。
q 不能通过ANALYZE表命令生成统计信息。
在 所有数据库中,临时表的缺点之一是优化器不能正常地得到临时表的真实统计。使用基于代价的 优化器(cost-based optimizer,CBO)时,有效的统计对于优化器的成败至关重要。如果没有统计信息,
优化器就只能对数据的分布、数据量以及索引的选择性作出猜测。 如果这些猜测是错的,为查询生成的查 询计划(大量使用临时表)可能就不是最优的。在许多情况下,正确的解决方案是根本不使用临时表,而 是使用一个 INLINE VIEW(要看INLINE VIEW的例子,可以查看前面运行的SELECT,它就有两个内联视图 )。 采用这种方式,Oracle可以访问一个表的所有相关统计信息,而且得出一个最 优计划。
我 经常发现,人们之所以使用临时表,是因为他们在其他数据库中了解到一个查询中联结太多的表 是一件“不好的事情”。但在Oracle开发中,必须把这个知识 忘掉。不要想着你比优化器要聪明,把本 来一个查询分解成3 个或4个查询,将其子结果存储在临时表中,然后再合并这些临时表;正确的做法是 应该编写一个查 询,直接回答最初的问题。在一个查询中引用多个表是可以的;Oracle中在这个方面不 需要临时表的帮助。
不 过在其他情况下,可以在进程中使用临时表,这是一种正确的做法。例如,我曾经编写过一个PALM 同步应用程序,将Palm Pilot上的日期簿与Oracle中存储的日历信息同步。Palm会为我提供自最后一次 热同步以来修改的所有记录的列表,我必须取得这些记录,把它们与 数据库中的当前数据相比较,更新数 据库记录,然后生成一个修改列表,应用到Palm。这是一个展示临时表用处的绝好例子。我使用一个临时 表在数据库中存储 Palm上所做的修改。然后运行一个存储过程,它将Palm生成的修改与当前的永久表(非 常大)相比较,发现需要对Oracle数据做哪些修改,然后找出 Oracle数据库中的哪些修改需要再应用到 Palm上。我必须对这个数据做两趟处理。首先,要发现仅在 Palm上修改的记录,并在Oracle中做相应 的 修改。接下来,要发现自最后一次同步和修改以来Palm和数据库中都经过修改的所有记录。如何发现仅在 数据库中经过修改的所有记录。并将其修改放在临时 表中。最后,Palm同步应用程序从临时表拉出这些 修改,把它们应用于Palm设备本身,断开连接时,临时表会消失。
不 过,我遇到的问题是,由于会分析永久表,所以使用了CBO。但是临时表上没有统计信息(尽管 可以分析临时表,但不会收集统计信息),因此CBO会对它做出 很多“猜测”。作为一名开发人员,我知 道可能的平均行数、数据的分布、查询选择的列等。我需要一种方法来告诉优化器这些更准确的猜测。可 以有3 中种方法向 优化器提供关于全局临时表的统计信息。一种方法是通过动态采样(只是Oracle9i Release 2及以上版本中新增的特性),另一种方法是使用DBMS_STATS包,它有两种做法。下面首先来看 动态采样。

动 态采样(dynamic sampling)是优化器的一种功能,硬解析一个查询时,会扫描数据库中的段(采 样),收集有用的统计信息,来完成这个特定查询的优化。这与硬解析期间 完成一个“缩型收集统计”命 令很类似。Oracle 10g中大量使用了动态采样,因为默认设置已经从level 1提升到level 2,采用 level 2, 优化器在结算查询计划之前,会对优化器处理的查询中引用的所有未分析的对象完成动态采样。9i Release2中则设置为level 1,所以动态采样的使用少得多。在Oracle9i Release 2中可以使用一个ALTER SESSION|SYSTEM命令,从而能有Oracle 10g默认行为,或者可以使用动态采样提示,如下:
ops$tkyte@ORA9IR2> create global temporary table gtt
2 as
3 select * from scott.emp where 1=0; Table created.
ops$tkyte@ORA9IR2> insert into gtt select * from scott.emp;
14 rows created.
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select /*+ first_rows */ * from gtt;
Execution Plan
----------------------------------------------------------

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=17 Card=8168 Bytes...

1 0 TABLE ACCESS (FULL) OF 'GTT' (Cost=17 Card=8168 Bytes=710616)

ops$tkyte@ORA9IR2> select /*+ first_rows dynamic_sampling(gtt 2) */ * from gtt; Execution Plan
----------------------------------------------------------

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=17 Card=14 Bytes=1218)

1 0 TABLE ACCESS (FULL) OF 'GTT' (Cost=17 Card=14 Bytes=1218)

ops$tkyte@ORA9IR2> set autotrace off

在 此,我们在这个查询中把表GTT的动态采样设置为level 2.在此之前,优化器猜测会从表GTT返

回8,168行。通过使用动态采样,估计的基数会与实际更为接近(这会得到总体上更好的查询计划)。使 用 level 2设置,优化器会很快地扫描表,对表的真实大小得出更实际的估计。在Oracle 10g中,这应 该不成问题,因为默认就会发生动态采样:
ops$tkyte@ORA10G> create global temporary table gtt
2 as
3 select * from scott.emp where 1=0; Table created.
ops$tkyte@ORA10G> insert into gtt select * from scott.emp;
14 rows created.
ops$tkyte@ORA10G> set autotrace traceonly explain

ops$tkyte@ORA10G> select * from gtt;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=14 Bytes=1218)
1 0 TABLE ACCESS (FULL) OF 'GTT' (TABLE (TEMP)) (Cost=2 Card=14 Bytes=1218)
ops$tkyte@ORA10G> 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表上不可行)。考虑以下情况(注意这在一个空模式中完成: 除了你创建的对象之外没有其他对象):

ops$tkyte@ORA10G> create table emp as select * from scott.emp; Table created.
ops$tkyte@ORA10G> create global temporary table gtt1 ( x number )
2 on commit preserve rows; Table created.
ops$tkyte@ORA10G> create global temporary table gtt2 ( x number )
2 on commit delete rows; Table created.
ops$tkyte@ORA10G> insert into gtt1 select user_id from all_users;
38 rows created.
ops$tkyte@ORA10G> insert into gtt2 select user_id from all_users;
38 rows created.

ops$tkyte@ORA10G> exec dbms_stats.gather_schema_stats( user ); PL/SQL procedure successfully completed.
ops$tkyte@ORA10G> select table_name, last_analyzed, num_rows from user_tables; TABLE_NAME LAST_ANAL NUM_ROWS
------------------------------ --------- ----------
EMP 01-MAY-05 14
GTT1
GTT2
可以看到,在这种情况下,只会分析EMP表:两个全局临时表将被忽略。可以如下调用 GATHER_SCHEMA_STATS(带GATHER_TEMP => TRUE)来改变这种行为:

ops$tkyte@ORA10G> insert into gtt2 select user_id from all_users;
38 rows created.
ops$tkyte@ORA10G> exec dbms_stats.gather_schema_stats( user, gather_temp=>TRUE ); PL/SQL procedure successfully completed.
ops$tkyte@ORA10G> select table_name, last_analyzed, num_rows from user_tables; TABLE_NAME LAST_ANAL NUM_ROWS
------------------------------ --------- ---------- EMP 01-MAY-05 14
GTT1 01-MAY-05 38
GTT2 01-MAY-05 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:

ops$tkyte@ORA10G> create global temporary table t ( x int, y varchar2(100) ); Table created.
ops$tkyte@ORA10G> 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 procedure successfully completed.
ops$tkyte@ORA10G> 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中如果将一个查询分解为较小的临时表查询,与原来的一个查询 相比,只会执行得更慢。我就经常看到人们这样做,如果有可能把对临时表的 一系列INSERT重写为一个 大查询(SELECT),所得到的单个查询会比原来的多步过程快得多。
临 时表会生成少量的redo,但是确实还是会生成redo,而且没有办法避免。这些redo是为回滚数 据生成的,而且在最典型的情况下,可以忽略不计。如果 只是对临时表执行INSERT和SELETE,生成的redo 量几乎注意不到。只有对临时表执行大量DELETE和UPDATE时,才会看到生成大量的 redo。
如果精心设计,可以在临时表上生成CBO使用的统计信息;不过,可以使用DBMS_STATS包对临时表 上的统计给出更好的猜测,或者由优化器使用动态采样在硬解析时动态收集。