分享
 
 
 

临时表更适合做插入和查询操作

王朝other·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

Oracle数据库除了可以保存永久表外,还可以建立临时表temporary tables。这些临时表用来保存一个会话SESSION的数据,或者保存在一个事务中需要的数据。

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

1简介

ORACLE数据库除了可以保存永久表外,还可以建立临时表temporary tables。这些临时表用来保存一个会话SESSION的数据,或者保存在一个事务中需要的数据。当会话退出或者用户提交commit和回滚rollback事务的时候,临时表的数据自动清空(truncate),但是临时表的结构以及元数据还存储在用户的数据字典中。

In addition to permanent tables, Oracle can create temporary tables to hold session-private data that exists only for the duration of a transaction or session.

Temporary tables are supported by Oracle9i and Oracle8i.

2具体介绍

Oracle临时表分为 会话级临时表 和 事务级临时表。

会话级临时表是指临时表中的数据只在会话生命周期之中存在,当用户退出会话结束的时候,Oracle自动清除临时表中数据。

事务级临时表是指临时表中的数据只在事务生命周期中存在。当一个事务结束(commit or rollback),Oracle自动清除临时表中数据。

临时表中的数据只对当前Session有效,每个Session都有自己的临时数据,并且不能访问其它Session的临时表中的数据。因此,临时表不需要DML锁。

The CREATE GLOBAL TEMPORARY TABLE statement creates a temporary table that can be transaction-specific or session-specific. For transaction-specific temporary tables, data exists for the duration of the transaction. For session-specific temporary tables, data exists for the duration of the session. Data in a temporary table is private to the session. Each session can only see and modify its own data. DML locks are not acquired on the data of the temporary tables. The LOCK statement has no effect on a temporary table, because each session has its own private data.

DML操作的临时表不产生redo log重作日志,但会产生回滚日志Undo log;Undo的产生(rollback segment)会产生Redo log。

DML statements on temporary tables do not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs are generated.

当一个会话结束(用户正常退出 用户不正常退出 ORACLE实例崩溃)或者一个事务结束的时候,Oracle对这个会话的表执行 TRUNCATE 语句清空临时表数据.但不会清空其它会话临时表中的数据.

A TRUNCATE statement issued on a session-specific temporary table truncates data in its own session. It does not truncate the data of other sessions that are using the same table.

DML statements on temporary tables do not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs are generated. Data from the temporary table is automatically dropped in the case of session termination, either when the user logs off or when the session terminates abnormally sUCh as during a session or instance failure.

你可以索引临时表和在临时表基础上建立视图.同样,建立在临时表上的索引也是临时的,也是只对当前会话或者事务有效. 临时表可以拥有触发器.

You can create indexes for temporary tables using the CREATE INDEX statement. Indexes created on temporary tables are also temporary, and the data in the index has the same session or transaction scope as the data in the temporary table.

You can create views that Access both temporary and permanent tables. You can also create triggers on temporary tables.

空间分配Segment Allocation(v$sort_usage)

Temporary tables use temporary segments. Unlike permanent tables, temporary tables and their indexes do not automatically allocate a segment when they are created. Instead, segments are allocated when the first INSERT (or CREATE TABLE AS SELECT) is performed. This means that if a SELECT, UPDATE, or DELETE is performed before the first INSERT, then the table appears to be empty.

Temporary segments are deallocated at the end of the transaction for transaction-specific temporary tables and at the end of the session for session-specific temporary tables.

临时表在一些版本中存在BUG可能产生过多的REDO LOG。

eygle 的站点http://www.eygle.com/faq/temp_table.htm

3建立临时表

临时表的定义对所有会话SESSION都是可见的,但是表中的数据只对当前的会话或者事务有效.

建立方法:

1) ON COMMIT DELETE ROWS 定义了建立事务级临时表的方法.

CREATE GLOBAL TEMPORARY TABLE admin_work_area

(startdate DATE,

enddate DATE,

class CHAR(20))

ON COMMIT DELETE ROWS;

EXAMPLE:

SQL CREATE GLOBAL TEMPORARY TABLE admin_work_area

2(startdate DATE,

3 enddate DATE,

4 class CHAR(20))

5ON COMMIT DELETE ROWS;

SQL create table permernate( a number);

SQL insert into admin_work_area values(sysdate,sysdate,'temperary table');

SQL insert into permernate values(1);

SQL commit;

SQL select * from admin_work_area;

SQL select* from permernate;

A

1

2)ON COMMIT PRESERVE ROWS 定义了创建会话级临时表的方法.

CREATE GLOBAL TEMPORARY TABLE admin_work_area

(startdate DATE,

enddate DATE,

class CHAR(20))

ON COMMIT PRESERVE ROWS;

EXAMPLE:

会话1:

SQL drop table admin_work_area;

SQL CREATE GLOBAL TEMPORARY TABLE admin_work_area

2(startdate DATE,

3 enddate DATE,

4 class CHAR(20))

5 ON COMMIT PRESERVE ROWS;

SQL insert into permernate values(2);

SQL insert into admin_work_area values(sysdate,sysdate,'session temperary');

SQL commit;

SQL select * from permernate;

A

1

2

SQL select * from admin_work_area;

STARTDATEENDDATECLASS

17-1?? -03 17-1?? -03 session temperary

会话2:

SQL select * from permernate;

A

1

2

SQL select * from admin_work_area;

未选择行.

会话2看不见会话1中临时表的数据.

temporary tables -- but they have to support

a) read consistency

b) rollback

c) rollback to savepointhttp://asktom.oracle.com/pls/ask/f?p=4950:8:8284619847966507619::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:30774214640787

they definitely -- totally definitely -- generate UNDO.

consider:

ops$tkyte@ORA9IUTF create global temporary table t ( x int ) on commit preserve

rows;

Table created.

ops$tkyte@ORA9IUTF insert into t values ( 1 );

1 row created.

ops$tkyte@ORA9IUTF variable x refcursor

ops$tkyte@ORA9IUTF exec open :x for select * from t;

PL/SQL procedure successfully completed.

that is (as always) a read consitent result set, it is pre-ordained, we

haven't fetched a single row of data yet -- NO IO HAS BEEN performed, the result

set is not 'copied' somewhere -- just like any other query

ops$tkyte@ORA9IUTF savepoint foo;

Savepoint created.

ops$tkyte@ORA9IUTF insert into t values ( 2 );

1 row created.

ops$tkyte@ORA9IUTF select * from t;

X

----------

1

2

that shows we can see two rows -- but

ops$tkyte@ORA9IUTF print x

X

----------

1

that query used UNDO to rol

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有