ITPub博客

首页 > Linux操作系统 > Linux操作系统 > temporary table

temporary table

原创 Linux操作系统 作者:alvin2012_zhang 时间:2013-06-27 14:01:33 0 删除 编辑

SQL> create global temporary table temp01(id number) on commit delete rows;

 

Table created.

 

SQL> insert into temp01 values (1) ;

 

1 row created.

 

SQL> select * from temp01;

 

        ID

----------

         1

 

SQL> commit;

 

Commit complete.

 

SQL> select * from temp01;

 

no rows selected

 

SQL> select table_name ,temporary from user_tables;

 

TABLE_NAME                     T

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

TEST01                         N

TEMP01                         Y

 

SQL> create global temporary table temp02 (id number) on commit preserve rows;

 

Table created.

 

SQL> insert into temp02 values(1);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from temp02;

 

        ID

----------

         1

 

SQL> conn / as sysdba

Connected.

SQL> conn test/test

Connected.

SQL> select * from temp02;

 

no rows selected

 

SQL> alter table temp01 add constraint pk_temp01 primary key(id);

 

Table altered.

 

SQL> alter table temp02 add constraint pk_temp02 primary key(id);

 

Table altered.

 

SQL> desc temp01

 Name                                                              Null?    Type

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

 ID                                                                NOT NULL NUMBER

 

SQL> alter table temp02 drop constraint pk_temp02;

 

Table altered.

 

SQL> desc temp02

 Name                                                              Null?    Type

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

 ID                                                                         NUMBER

 

SQL> insert into temp02 values(1);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from temp02;

 

        ID

----------

         1

 

SQL> alter table temp02 add constraint pk_temp02 primary key(id);

alter table temp02 add constraint pk_temp02 primary key(id)

*

ERROR at line 1:

ORA-14450: attempt to access a transactional temp table already in use

 

SQL> select username from v$sort_usage;

 

USERNAME

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

TEST

 

SQL> select * from temp02;

 

        ID

----------

         1

 

SQL> insert into temp01 values(1);

 

1 row created.

 

SQL> select username,SQLADDR,SEGTYPE ,BLOCKS, SEGBLK#  from v$sort_usage;

 

USERNAME                       SQLADDR  SEGTYPE       BLOCKS    SEGBLK#

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

TEST                           29ABA460 DATA             128       1801

TEST                           29ABA460 DATA             128       1673

TEST                           29ABA460 INDEX            128       1545

 

 

 

临时表的不足之处:

1.不支持lob对象,这也许是设计者基于运行效率的考虑,但实际应用中确实需要此功能时就无法使用临时表了。

2.不支持主外键关系

特性和性能(与普通表和视图的比较) 

1.临时表只在当前连接内有效 

2.临时表不建立索引,所以如果数据量比较大或进行多次查询时,不推荐使用 

3.数据处理比较复杂的时候时表快,反之视图快点 

4.在仅仅查询数据的时候建议用游标: open cursor for 'sql clause'; 

 

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

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2013-03-22

  • 博文量
    5
  • 访问量
    8414