建立基於事務和基於會話的臨時表及臨時表建索引的實驗

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 |