- 临时表介绍
Oracle的临时表分为会话级和事务级，创建语句：
--会话级：
create global temporary table tmp_sess_dept on commit preserve rows as select * from dept ;
--事务级
create global temporary table tmp_tran_dept on commit delete rows as select * from dept
会话级的临时表，在一个会话期间内，表的数据都会存在。
事务级的临时表，当事务结束的时候表的数据会自动的删除。。
临时表数据存在于排序段，排序段是会话所专有的，每个会话是隔离的，每个会话只能见到自己的数据。
验证表是否为临时表，以及临时表的生命周期：
select table_name,LOGGING,TEMPORARY,DURATION from user_tables;
TMP_TRAN_DEPT NO Y SYS$TRANSACTION
TMP_SESS_DEPT NO Y SYS$SESSION
多个会话同时使用临时表的时候，会发现有多个排序段在活动。每个会话只是使用临时表在系统表空间中的定义，所以DROP的时候不会去回收站，而是直接从字典中删除。
验证有多少个用户在使用排序段（查看权限SELECT ANY DICTIONARY）：
SELECT TABLESPACE_NAME,CURRENT_USERS FROM V$SORT_SEGMENT;
验证临时表：
insert into tmp_sess_dept select * from dept;
select * from tmp_sess_dept;
commit;
select * from tmp_sess_dept;
insert into tmp_tran_dept select * from dept;
select * from tmp_tran_dept;
commit;
select * from tmp_tran_dept;
- 临时表的统计信息处理
目的：
临时表是会话隔离的，收集临时表的统计信息是没有任何用处，还可能造成错误的执行计划。临时表的统计信息要删除，并且锁定临时表的统计信息，或者关闭数据库的自动收集统计信息任务，改成手动去收集。
数据库优化器模式
查看数据库的优化器模式：
SQL> show parameter mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
remote_dependencies_mode string TIMESTAMP
result_cache_mode string MANUAL
修改数据库的优化器选择模式：
alter system set optimizer_mode='CHOOSE' scope=both;
测试1：不同的session收集统计信息
建立一个session，对临时表插入数据；
新建一个session，收集临时表统计信息：
begin
dbms_stats.gather_table_stats('SCOTT', 'TMP_SESS_DEPT');
end;
begin
dbms_stats.gather_table_stats('SCOTT', 'TMP_TRAN_DEPT');
end;
查看临时表的统计信息：
select * from dba_tab_statistics a
where a.TABLE_NAME in ('TMP_SESS_DEPT','TMP_TRAN_DEPT');
结构显示统计项为0，因为临时表是会话隔离的，所以统计信息为0.
测试2：在一个session内收集统计信息
建立一个session,对临时表插入数据；
收集统计信息
begin
dbms_stats.gather_table_stats('SCOTT', 'TMP_SESS_DEPT');
dbms_stats.gather_table_stats('SCOTT', 'TMP_TRAN_DEPT');
end;
/
查看统计信息：
select *
from dba_tab_statistics a
where a.TABLE_NAME in ('TMP_SESS_DEPT', 'TMP_TRAN_DEPT');
查询结果显示会话级的临时表有相应的统计信息，事务级的临时表统计信息显示的行数为0，这是因为在收集统计信息的时候进行了事务的提交。
测试3：演示错误执行计划
用事务级的临时表进行测试，在3张表的关联，测试语句如下：
select *
from emp e, SALGRADE s, tmp_tran_dept d
where e.sal between s.losal and s.hisal
and e.deptno = d.deptno
查看产生的执行计划：
select a.SQL_TEXT,a.SQL_ID,a.CHILD_NUMBER from v$sql a where a.SQL_TEXT like &stat
select * from table(dbms_xplan.display_cursor(&sqlid,0));
在一个事务里使用事务级的临时表，执行计划显示产生了笛卡尔连接。
正常的执行计划为：
为了避免数据库引用错误的临时表统计信息：
- 建议：
begin
dbms_stats.delete_table_stats('SCOTT', 'TMP_TRAN_DEPT');
end;
/
begin
dbms_stats.lock_table_stats('SCOTT', 'TMP_TRAN_DEPT');
end;
/