Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2885567
  • 博文数量: 599
  • 博客积分: 16398
  • 博客等级: 上将
  • 技术积分: 6875
  • 用 户 组: 普通用户
  • 注册时间: 2009-11-30 12:04
个人简介

WINDOWS下的程序员出身,偶尔也写一些linux平台下小程序, 后转行数据库行业,专注于ORACLE和DB2的运维和优化。 同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。 数据库技术交流群:58308065,23618606

文章分类

全部博文(599)

文章存档

2014年(12)

2013年(56)

2012年(199)

2011年(105)

2010年(128)

2009年(99)

分类:

2012-12-06 14:08:15

During direct-path INSERT, the database obtains exclusive locks on the table (or on all partitions of a partitioned table). As a result, users cannot perform any concurrent insert, update, or delete operations on the table, and concurrent index creation and build operations are not permitted. Concurrent queries, however, are supported, but the query will return only the information before the insert operation.

The behaviour of /*+ APPEND */ hint has changed in the different versions of Oracle:
  • In Oracle 10g and previous releases the APPEND hint is ignored when specifying INSERT..VALUES. Thus even though stated, the insert will be done in conventional mode and not use direct-path insert into the buffer cache. When using INSERT..SELECT the hint will be honoured.

  • On 11g Release 1, the APPEND hint will be honoured for an INSERT with VALUES and the row will be inserted directly into datafiles, bypassing the buffer cache.

  • In 11g release 2, a new hint has been added, /*+ APPEND_VALUES */ that will load the data directly into the datafiles.
To summarize, there are now 2 different hints for direct-path load:
  • APPEND: Instructs Oracle to use direct-path INSERT with the subquery syntax of the INSERT statement.
  • APPEND_VALUES: Instructs Oracle to use direct-path INSERT with the VALUES clause.

INSERT .. VALUES and APPEND HINT

Version: 10.2.0.4 

--SID=159:
SQL> create table test_insert (id number, name varchar2(25));
SQL> insert /*+ APPEND */ into test_insert values (1,'Test1');

1 row created.

select sid,type,id1,id2,lmode from v$lock where sid=159;

SQL>
SID TY ID1 ID2 LMODE
---------- -- ---------- ---------- ----------
159 TM 61245 0 3
159 TX 262153 3571 6

APPEND hint ignored and lock mode=3 Row Exclusive 


Version: 11.1.0.7

--SID=131:
SQL> create table test_insert (id number, name varchar2(25));
SQL> insert /*+ APPEND */ into test_insert values (1,'Test1');

1 row created.

select sid,type,id1,id2,lmode from v$lock where sid=131;

SQL>
SID TY ID1 ID2 LMODE
---------- -- ---------- ---------- ----------
131 TM 84239 0 6
131 TX 65548 4981 6

APPEND hint honoured and lock mode=6 Exclusive 

Version: 11.2.0.3

--SID=59
SQL> create table test_insert (id number, name varchar2(25));
SQL> insert /*+ APPEND */ into test_insert values (1,'Test1');

1 row created.

select sid,type,id1,id2,lmode from v$lock where sid=59;

SID TY ID1 ID2 LMODE
---------- -- ---------- ---------- ----------
69 TM 96843 0 3
69 TX 196640 3301 6



APPEND hint ignored and lock mode=3 Row Exclusive 


INSERT .. VALUES and APPEND_VALUES HINT

Version: 11.2.0.3
--SID=134
SQL> create table test_insert (id number, name varchar2(25));
SQL> insert /*+ APPEND_VALUES */ into test_insert values (1,'Test1');

1 row created.

SQL> select sid,type,id1,id2,lmode from v$lock where sid=134;

SID TY ID1 ID2 LMODE
---------- -- ---------- ---------- ----------
134 TM 96843 0 6
134 TX 65565 7849 6

New APPEND_VALUES  hint honoured and lock mode=6 Exclusive 


INSERT .. SELECT and APPEND HINT 

All Versions

--SID=77
SQL> insert /*+ APPEND */ into test_insert select object_id,object_name from user_objects;

59 rows created.

SQL> select sid,type,id1,id2,lmode from v$lock where sid=77;

SID TY ID1 ID2 LMODE
---------- -- ---------- ---------- ----------
77 TM 96843 0 6
77 TX 589827 8029 6

APPEND hint honoured and lock mode=6 Exclusive 

阅读(1011) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~