当前位置:操作系统 > Unix/Linux >>

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

创建基于事务和基于会话的临时表及临时表建索引的实验
 
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                                                               &nbs
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,