临时表是数据库当中也充当比较重要的角色，用于保存事务或者会话期间的中间结果集，而且只对当前会话可见，无论数据提交与否，其他任何会话都看不到当前会话临时表的数据。所以临时表也不会存在阻塞的问题。
分配临时表的空间是从当前用户的临时表空间分配的，当有数据第一次插入到临时表中才会创建一个临时段，这点和现在版本的表差不多，在第一次插入后，才会为表分配段。oracle中的临时表有2种类型：一种是基于会话的临时表，这种临时表中的数据提交之前任然存在，但是在断开连接后，数据就没有了。另一种的基于事务的临时表，这种临时表的数据提交之后就消失了。下面看简单的例子
Oracle PL/SQL
SQL> create global temporary table jerry_temp_test1
2 on commit preserve rows
3 as select * from scott.emp where 1=0;
Table created.
1
2
3
4
SQL>createglobaltemporarytablejerry_temp_test1
2oncommitpreserverows
3asselect*fromscott.empwhere1=0;
Tablecreated.
这里指定on commit preserve rows子句表示这是一个基于会话的临时表，只要当前会话一直没有断开，这个会话就可以看到临时表中的数据，其他会话始终无法查看。
Oracle PL/SQL
create global temporary table jerry_temp_test2
on commit delete rows
as select * from scott.emp where 1=0;
1
2
3
createglobaltemporarytablejerry_temp_test2
oncommitdeleterows
asselect*fromscott.empwhere1=0;
这里指定on commit delete rows子句表示这是一个基于事务的临时表，当会话commit之后，数据就不存在了。下面简单的测试一下如上所说的功能
Oracle PL/SQL
SQL> insert into jerry_temp_test1 select * from scott.emp;
14 rows created.
SQL> insert into jerry_temp_test2 select * from scott.emp;
14 rows created.
1
2
3
4
SQL>insertintojerry_temp_test1select*fromscott.emp;
14rowscreated.
SQL>insertintojerry_temp_test2select*fromscott.emp;
14rowscreated.
分别向这2种临时表插入了数据，但还未提交，查看2个临时表的数据，然后提交数据，再次查看临时表中的数据
Oracle PL/SQL
SQL> select test1_count,test2_count from
2 (select count(*) test1_count from jerry_temp_test1),
3 (select count(*) test2_count from jerry_temp_test2);
TEST1_COUNT TEST2_COUNT
----------- -----------
14 14
SQL> commit;
Commit complete.
SQL> select test1_count,test2_count from
2 (select count(*) test1_count from jerry_temp_test1),
3 (select count(*) test2_count from jerry_temp_test2);
TEST1_COUNT TEST2_COUNT
----------- -----------
14 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL>selecttest1_count,test2_countfrom
2(selectcount(*)test1_countfromjerry_temp_test1),
3(selectcount(*)test2_countfromjerry_temp_test2);
TEST1_COUNTTEST2_COUNT
----------- -----------
1414
SQL>commit;
Commitcomplete.
SQL>selecttest1_count,test2_countfrom
2(selectcount(*)test1_countfromjerry_temp_test1),
3(selectcount(*)test2_countfromjerry_temp_test2);
TEST1_COUNTTEST2_COUNT
----------- -----------
140
由于jerry_temp_test2临时表是基于事务的，所以在commit之后就不能查看到数据了，断开连接后，临时表的数据都不能查看了
既然这里谈到了临时表，还有一个关于临时表的问题就不容忽略了，临时表的缺点就是优化器不能正常的得到真实的统计信息，这样是CBO中，可能执行计划就不正确，当然这在oracle中并不是什么困难的事情。我们可以使用动态采样和dbms_stats包来解决这个问题。不过在oracle11g版本中，如果一个表没有统计信息，oracle会默认采用动态采样，所以我们无需手动指定动态采样，除非需要指定采样的级别。
Oracle PL/SQL
SQL> create global temporary table jerry_test3
2 as
3 select * from scott.emp where 0=1;
Table created.
SQL> insert into jerry_test3 select * from scott.emp;
14 rows created.
SQL> set autot traceonly explain
SQL> select * from jerry_test3;
Execution Plan
----------------------------------------------------------
Plan hash value: 2379358155
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| JERRY_TEST3 | 14 | 1218 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SQL>createglobaltemporarytablejerry_test3
2as
3select*fromscott.empwhere0=1;
Tablecreated.
SQL>insertintojerry_test3select*fromscott.emp;
14rowscreated.
SQL>setautottraceonlyexplain
SQL>select*fromjerry_test3;
ExecutionPlan
----------------------------------------------------------
Planhashvalue:2379358155
---------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
---------------------------------------------------------------------------------
|0|SELECTSTATEMENT||14|1218|2(0)|00:00:01|
|1|TABLEACCESSFULL|JERRY_TEST3|14|1218|2(0)|00:00:01|
---------------------------------------------------------------------------------
Note
-----
-dynamicsamplingusedforthisstatement(level=2)
虽然动态采样能解决这样的问题，但凡是也是有一定代价的，动态采样必须在查询解析时完成，所以如果能提前手机适当的统计信息，就可以避免是硬解析的时候使用动态采样，这里就可以使用dbms_stats包了。
在使用dbms_stats包收集统计信息的时候也需要注意，需要指定GATHER_TEMP参数为true，才会收集全局临时表的统计信息，另外2种不同的临时表类型收集的结果也不一样，下面看一下例子
Oracle PL/SQL
SQL> create table jerry_test as select * from scott.emp;
Table created.
SQL> create global temporary table jerry_temp_test4(id number)
2 on commit preserve rows;
Table created.
SQL> create global temporary table jerry_temp_test5(id number)
2 on commit delete rows;
Table created.
SQL> insert into jerry_temp_test4 select object_id from dba_objects;
86971 rows created.
SQL> insert into jerry_temp_test5 select object_id from dba_objects;
86971 rows created.
SQL> exec dbms_stats.gather_schema_stats('SCOTT');
PL/SQL procedure successfully completed.
SQL> select table_name,last_analyzed,num_rows from user_tables;
TABLE_NAME LAST_ANAL NUM_ROWS
------------------------------ --------- ----------
JERRY_TEMP_TEST4
JERRY_TEMP_TEST5
JERRY_TEST 30-JUN-14 14
12 rows selected.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
SQL>createtablejerry_testasselect*fromscott.emp;
Tablecreated.
SQL>createglobaltemporarytablejerry_temp_test4(idnumber)
2oncommitpreserverows;
Tablecreated.
SQL>createglobaltemporarytablejerry_temp_test5(idnumber)
2oncommitdeleterows;
Tablecreated.
SQL>insertintojerry_temp_test4selectobject_idfromdba_objects;
86971rowscreated.
SQL>insertintojerry_temp_test5selectobject_idfromdba_objects;
86971rowscreated.
SQL>execdbms_stats.gather_schema_stats('SCOTT');
PL/SQLproceduresuccessfullycompleted.
SQL>selecttable_name,last_analyzed,num_rowsfromuser_tables;
TABLE_NAMELAST_ANALNUM_ROWS
------------------------------ --------- ----------
JERRY_TEMP_TEST4
JERRY_TEMP_TEST5
JERRY_TEST30-JUN-1414
12rowsselected.
因为这里在收集统计信息的时候，没有指定GATHER_TEMP参数，所以临时表的统计信息被忽略
Oracle PL/SQL
SQL> exec dbms_stats.gather_schema_stats('SCOTT',gather_temp=>true);
PL/SQL procedure successfully completed.
SQL> select table_name,last_analyzed,num_rows from user_tables;
TABLE_NAME LAST_ANAL NUM_ROWS
------------------------------ --------- ----------
JERRY_TEMP_TEST4 30-JUN-14 86971
JERRY_TEMP_TEST5 30-JUN-14 0
JERRY_TEST 30-JUN-14 14
12 rows selected.
1
2
3
4
5
6
7
8
9
10
11
12
13
SQL>execdbms_stats.gather_schema_stats('SCOTT',gather_temp=>true);
PL/SQLproceduresuccessfullycompleted.
SQL>selecttable_name,last_analyzed,num_rowsfromuser_tables;
TABLE_NAMELAST_ANALNUM_ROWS
------------------------------ --------- ----------
JERRY_TEMP_TEST430-JUN-1486971
JERRY_TEMP_TEST530-JUN-140
JERRY_TEST30-JUN-1414
12rowsselected.
可以看到这2种不同类型的临时表收集统计信息的情况，基于会话的临时表会有正确的统计信息，但基于事务的临时表则没有，中是因为dbms_stats包将提交，这样导致基于事务的临时表就没有数据。所以使用dbms_stats包的时候一定要注意，没有基于事务的临时表，也就是创建临时表的时候，不是指定on commit delete rows参数的临时表，这样会导致收集到不正确的值。
我这里例子中使用的是收集schema的统计信息，当然也可以对单个表收集统计信息，也可以在收集统计信息的时候，手动填充统计信息，不过这种方式还是不适合基于事务的临时表。因为数据会被截断。
转载请注明: 版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!
最后编辑：2014-06-30作者：Jerry
一个积极向上的小青年，热衷于分享--Focus on DB,BI,ETL
扫一扫
2093
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
