数据库表--temporary table

用于保存事物或会话期间的中间结果集，数据只对当前会话可见，所有会话看不到其他会话的数据，即使会话已经提交，因此不存在用户并发问题。创建临时表的动作不涉及存储空间的分配，不会为此分配初始区段，当一个会话第一次在临时表中放入数据时，才会为该会话创建一个临时段。每个用户应该有自己的临时表空间，并在自己的临时表空间中分配临时段。

基于会话的临时表（会话断开之前，数据一直存在，只有当前会话能看到这些数据，与提交无关）
JEL@JEL >create global temporary table temp_table_session
  2  on commit preserve rows
  3  as
  4  select * from all_objects where 0=1;

Table created.


基于事物的临时表（会话一提交，数据就被清除了，在临时表的自动清除过程中不存在开销）
JEL@JEL >create global temporary table temp_table_transaction
  2  on commit delete rows
  3  as
  4  select * from all_objects where 0=1;

Table created.


提交前：
JEL@JEL >insert into temp_table_session select * from all_objects;

9303 rows created.

JEL@JEL >insert into temp_table_transaction select * from all_objects;

9303 rows created.

JEL@JEL >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
----------- ---------------
       9303            9303

提交后：
JEL@JEL >commit;

Commit complete.

JEL@JEL >select session_cnt,transaction_cnt from (select count(*) session_cnt from temp_table_session),(select count(*) transaction_cnt from temp_table_transaction);

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

开始新会话后：
JEL@JEL >conn / as sysdba
Connected.
SYS@JEL >conn jel/jel
Connected.
JEL@JEL >select session_cnt,transaction_cnt from (select count(*) session_cnt from temp_table_session),(select count(*) transaction_cnt from temp_table_transaction);

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


注意：将所有全局临时表只创建一次，作为应用安装的一部分，就像创建永久表一样。
归根到底，目标是，临时表应该在应用安装期间创建，绝对不要在运行时创建。
临时表可以有触发器、检查约束、索引等，但不支持永久表的某些特性，如：
1、不能有引用完整性约束，不能作为外键的目标，也不能再临时表中定义外键
2、不能有nested table类型的列
3、不能是IOT
4、不能再任何类型的聚簇中
5、不能分区
6、不能通过analyze表命令生成统计信息

临时表的缺点之一就是优化器不能正常的得到临时表的真实统计。在许多情况下，正确的解决方案并不是使用临时表，而是使用一个inline view。如果应用中需要临时存储一个行集由其他表处理，临时表就很有用。


向优化器提供关于全局临时表的统计信息：
方法1：动态采样
oracle10g中，默认会发生自动采样
JEL@JEL >select * from temp_table_session;

Execution Plan
----------------------------------------------------------
Plan hash value: 3237906844

--------------------------------------------------------------------------------
--------

| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Ti
me     |

--------------------------------------------------------------------------------
--------

|   0 | SELECT STATEMENT  |                    |  9943 |  1242K|    34   (0)| 00
:00:01 |

|   1 |  TABLE ACCESS FULL| TEMP_TABLE_SESSION |  9943 |  1242K|    34   (0)| 00
:00:01 |

--------------------------------------------------------------------------------
--------


Note
-----
   - dynamic sampling used for this statement ---此次提示应用了dynamic sampling

方法2：dbms_stats用户分析（gather_schema_stats、gather_table_stats）,在on commit delete rows表上不可行
SYS@JEL >conn jel/jel
Connected.
JEL@JEL >insert into temp_table_session select * from all_objects;

9303 rows created.

JEL@JEL >insert into temp_table_transaction select * from all_objects;

9303 rows created.

JEL@JEL >exec dbms_stats.gather_schema_stats('JEL');

PL/SQL procedure successfully completed.

JEL@JEL >select table_name,last_analyzed,num_rows from user_tables;

TABLE_NAME                     LAST_ANAL   NUM_ROWS
------------------------------ --------- ----------
DEPT_AND_EMP                   11-DEC-13          2
TEMP_TABLE_TRANSACTION
TEMP_TABLE_SESSION

以上统计两个全局临时表被忽略。
JEL@JEL >exec dbms_stats.gather_schema_stats('JEL',gather_temp=>true);

PL/SQL procedure successfully completed.

JEL@JEL >select table_name,last_analyzed,num_rows from user_tables;

TABLE_NAME                     LAST_ANAL   NUM_ROWS
------------------------------ --------- ----------
DEPT_AND_EMP                   11-DEC-13          2
TEMP_TABLE_TRANSACTION         11-DEC-13          0
TEMP_TABLE_SESSION             11-DEC-13       9303

以上统计TEMP_TABLE_SESSION表有正确的统计结果，但TEMP_TABLE_TRANSACTION表没有，这是因为dbms_stats将提交，而擦除表中的所有信息。

注意：第一，要保证在收集统计信息的会话中用代表性数据填充全局临时表；第二，如果有on commit delete rows全局临时表，就不应该用此方法

方法3：通过一个手动过程用临时表的代表性统计信息填充数据字典

例如：如果平均临时表中行数是500，行的平均大小是100字节，块数为7
JEL@JEL >begin
  2  dbms_stats.set_table_stats(ownname=>'JEL',tabname=>'T',numrows=>500,numblks=>7,avgrlen=>100);
  3  end;
  4  /

PL/SQL procedure successfully completed.

JEL@JEL >select table_name,num_rows,blocks,avg_row_len from user_tables where table_name='T';

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

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

临时表的使用场景
1、循环SQL，最初优化前，系统中有很多类似的sql循环执行，效率很低。比如通常会有通过接口传入数千的参数，然后根据这些参数循环执行sql。优化时，可先把这些参数insert到临时表，然后关联该临时表一次执行以达到优化的效果。
2、多表关联，利用临时表简化有太多表关联的复杂SQL
3、如果某个数据集会重复多次使用的情况下建议使用临时表
4、临时表在逻辑复杂的大数据量更新的时候很有用，查询部分with就可以了
5、临时表作为复杂查询条件的中间结果用于主查询

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29337971/viewspace-1063025/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29337971/viewspace-1063025/