创建基于事务和基于会话的临时表及临时表建索引的实验

Oracle的临时表只存在于某个回话或者事务的生命周期里，此时临时表中的数据只对当前这个会话可见。

当会话退出或者用户提交commit和回滚rollback事务的时候，临时表的数据自动清空，但是临时表的结构以及元数据还存储在用户的数据字典中。

临时表的定义对所有会话SESSION都是可见的,但是表中的数据只对当前的会话或者事务有效.
临时表经常被用于存放一个操作的中间数据（数据处理的中间环节）。

临时表由于不产生redo，能够提高数据操作的性能。

临时表不会产生锁和等待。

实验如下：
1.ON COMMIT DELETE ROWS 基于事务的临时表，临时表中的数据是基于事务的，当事物提交或者回滚时，临时表中的数据将被清空12:35:26 SQL> create global temporary table temp1 on commit delete rows as select * from dba_extents;
Table created
14:06:29 SQL> select count(*) from temp1;
COUNT(*)
----------
0
14:08:16 SQL> insert into temp1 select * from dba_extents;
9221 rows inserted
14:09:02 SQL> select count(*) from temp1;
COUNT(*)
----------
9221
14:09:20 SQL> commit;
Commit complete
14:09:24 SQL> select count(*) from temp1;
COUNT(*)
----------
0
14:09:27 SQL> select * from temp1;
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------

2.PRESERVE ROWS 基于会话的临时表，临时表中的数据基于会话，当会话结束时，临时表中的数据被清空。14:15:16 SQL> create global temporary table temp2 on commit preserve rows as select * from dba_extents;
Table created
14:15:39 SQL> select count(*) from temp2;
COUNT(*)
----------
9221
14:16:19 SQL> insert into temp2 select * from dba_extents;
9222 rows inserted
14:16:58 SQL> select count(*) from temp2;
COUNT(*)
----------
18443
14:17:15 SQL> commit;
Commit complete
14:17:18 SQL> select count(*) from temp2;
COUNT(*)
----------
18443
14:17:19 SQL>

再打开一个会话进行查询：
SQL> select count(*) from temp2;
COUNT(*)
----------
0
SQL> select * from temp2;
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
SQL>

3.临时表上建立索引会话1：环境及错误提示
14:30:11 SQL> create global temporary table temp3 on commit preserve rows as select * from dba_objects;
Table created
14:31:22 SQL> select count(*) from temp3;
COUNT(*)
----------
72009
14:32:25 SQL> create index idx_temp3 on temp3(object_id);
create index idx_temp3 on temp3(object_id)

ORA-14452: attempt to create, alter or drop an index on temporary table already in use

对以上错误的解释：create index idx_temp3 on temp3(object_id);用于会话相关,也就在事务结束后truncate data in the temporary table,但如果在会话未结束时要修改temporary table就会出现错误：

ORA-14452: attempt to create, alter or drop an index on temporary table already in use

此时可以从其它会话中来建索引。

会话2：建索引
SQL> create index idx_temp3 on temp3(object_id);
Index created
SQL> select index_name,index_type,table_owner,table_name from user_indexes where table_name='TEMP3';
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME
------------------------------ --------------------------- ------------------------------ ------------------------------
IDX_TEMP3 NORMAL BYS TEMP3

回到会话1：查询新建索引的相关信息
14:36:21 SQL> select index_name,index_type,table_owner,table_name from user_indexes where table_name='TEMP3';
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME
------------------------------ --------------------------- ------------------------------ ------------------------------
IDX_TEMP3 NORMAL BYS TEMP3
14:44:02 SQL> select count(*) from temp3;
COUNT(*)
----------
72009
14:44:35 SQL> select * from temp3 where object_id=50;
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY NAMESPACE EDITION_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------

14:44:58 SQL> select table_name,tablespace_name from user_tables where table_name='TEMP3';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP3
14:46:19 SQL> select index_name,tablespace_name from user_indexes where table_name='TEMP3';
INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
IDX_TEMP3
14:47:05 SQL> select segment_name,tablespace_name from user_segments where segment_name in('TEMP3','IDX_TEMP3');
SEGMENT_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------

会话3：在新建的会话上，测试查询是否能用到索引。第一次查询未用到索引，因为表内无数据。第二次查询从执行计划中可以看到使用了索引
SQL> set autotrace on explain;
SQL> select * from temp3 where object_id=111;
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 2448592476
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEMP3 | 1 | 207 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL>
SQL> set autotrace off;
SQL> select index_name,index_type,table_owner,table_name from user_indexes where table_name='TEMP3';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
IDX_TEMP3 NORMAL
BYS TEMP3

SQL> select count(*) from temp3;
COUNT(*)
----------
0
SQL> insert into temp3 select * from dba_objects;
已创建72010行。
SQL> commit;
提交完成。
SQL> set autotrace on explain;
SQL> select * from temp3 where object_id=111;
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 3995552559
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEMP3 | 1 | 207 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEMP3 | 1 | | 1 (0)| 00:00:01 |