태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

INSERT /*+ APPEND */ ... VALUES ...

오라클 2010/06/21 13:43
한건의 로우(Row)를 Insert하는데 APPEND 힌트를 이용한 Direct Path Mode가 필요한지는 잘 모르겠습니다. 어쨌든 오라클은 11g부터 이러한 모드를 지원하기로 했습니다. 즉 INSERT ... VALUES ... 구문에서도 APPEND 힌트가 동작하도록 수정되었습니다. 좀 더 상세하게 정리를 해보면
  • Oracle 10g까지는 INSERT ... VALUES ... 구문에 APPEND 힌트를 부여해도 무시됩니다. 즉, APPEND 힌트는 INSERT ... SELECT 구문에서만 동작합니다.
  • APPEND 힌트로 인해 Direct Path Mode로 동작하면 로우를 Insert할 때 현재 세그먼트의 Free Block을 무시하고(동시에 Buffer Cache를 경유하지 않고) 세그먼트의 High Water Mark 뒤에 Append 시켜버립니다. 따라서 대량의 데이터를 추가할 때 속도는 빠르지만 데이터 파일에 그만큼 불필요한 빈공간이 남아있을 수는 있습니다. (하지만 이 빈공간도 언젠가는 사용될 것입니다)
  • Oracle 11gR1에서는 INSERT ... VALUES ... 구문에서도 APPEND 힌트가 동작합니다. 히든 파라미터 _direct_path_insert_features에 의해 제어됩니다.
  • Oracle 11gR2에서는 INSERT ... VALUES ... 구문에서 APPEND 힌트를 부여해도 무시됩니다(응?). 대신 APPEND_VALUES 라는 힌트가 추가되었습니다. 즉 APPEND_VALUES 힌트를 사용하면 INSERT ... VALUES ... 구문에서도 Direct Path Mode로 Insert가 이루어집니다.
간단한 테스트 결과입니다.
create table t1(c1 number);

insert into t1 values(1);
commit;
insert into t1 values(2);
commit;

-- (2건의 로우를 Insert했고 Conventional Path Mode이므로 두 개의 로우가 같은 Block에 있을 것입니다)
select
	dbms_rowid.rowid_relative_fno(rowid) as file#,
	dbms_rowid.rowid_block_number(rowid) as block#
from t1;

-- 10.2.0.1
     FILE#     BLOCK#
---------- ----------
         4        853
         4        853

-- 11.1.0.6
     FILE#     BLOCK#
---------- ----------
         4        758
         4        758

-- (APPEND 힌트를 부여한 INSERT ... VALUES 구문입니다)
insert /*+ append */ into t1 values(3);
commit;

-- 10.2.0.1에서는 APPEND 힌트가 무시되므로 역시 같은 Block에 Insert 됩니다.
     FILE#     BLOCK#
---------- ----------
         4        853
         4        853
         4        853

-- 하지만 11.1.0.6에서는 APPEND 힌트가 동작했고, 그 결과 High Water Mark의 끝 이후로 Insert 되어 버립니다.
     FILE#     BLOCK#
---------- ----------
         4        758
         4        758
         4        769

-- 11.2.0.1에서는 APPEND_VALUES 힌트가 그 역할을 대신합니다.
iinsert /*+ append_values */ into t1 values(3);
commit;

     FILE#     BLOCK#
---------- ----------
         4        526
         4        526
         4        528
왜 오라클 개발자들이 갑자기 INSERT ... VALUES ... 구문에서도 APPEND 힌트가 동작하도록 바꾼 것일까요? 아무리 생각해도 모르겠습니다.

아마 이전에 일하던 개발자가 그만두고 가면서 인수인계가 잘 안되었나봅니다. 이 모든 것은 사람이 하는 것이기에... :)

저작자 표시
Trackback 0 : Comments 8

Trackback Address :: http://ukja.tistory.com/trackback/317 관련글 쓰기

  1. 왕만두 2010/06/22 09:30 Modify/Delete Reply

    TABLE 이 Compress 로 되어 있을때 Direct Load 로 입력하지 않으면 Compress 가 무의미하므로
    11g 부터 insert ~ values 에도 append 를 추가한것 같네요.
    좋은글 감사합니다.

    • 욱짜 2010/06/22 10:21 Modify/Delete

      Oracle 11gR1에서는 OLTP Compression 기능이 추가되었기때문에 Insert ... Values... 에서도 Compression이 가능합니다. 그러니까 아마 다른 이유가 있을 것 같습니다.

  2. 백면서생 2010/06/22 10:32 Modify/Delete Reply

    insert ~ select 절을 사용할수 없는 상황이면서
    pl/sql이나 java 등에서 array processing할때 성능에 도움이 되지는 않을런지요..^^

    • 욱짜 2010/06/22 10:49 Modify/Delete

      INSERT ... VALUES 구문을 통해 "1건"을 Insert할 때 Direct Path Mode를 사용하면 그 오베허드(블록킹, Physical I/O 발생, Data File의 불필요한 크기 증가 등)가 훨씩 더 큽니다. 배보다 배꼽이 더(너무나 더) 크다고 할 수 있겠습니다.

      적은 수의 데이터를 Insert할 때는 Conventional Path Mode가 훨씬 더 효율적입니다. Logical I/O만으로 원하는 작업을 할 수 있으니까요.

    • 백면서생 2010/06/24 10:36 Modify/Delete

      이런류의 array processing에서 효과는 없을런지요.^^
      CREATE OR REPLACE PROCEDURE a2 (p_array_size IN PLS_INTEGER DEFAULT 100)
      IS

      TYPE ARRAY IS TABLE OF all_objects%ROWTYPE;
      l_data ARRAY;

      CURSOR c IS
      SELECT *
      FROM all_objects;

      BEGIN
      OPEN c;
      LOOP
      FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

      FORALL i IN 1..l_data.COUNT
      INSERT /*+ APPEND_VALUES */ INTO tb2 VALUES l_data(i);
      commit;
      EXIT WHEN c%NOTFOUND;
      END LOOP;
      close c;
      END a2;
      /

  3. 욱짜 2010/06/24 13:35 Modify/Delete Reply

    FORALL ... INSERT ... VALUES 구문은 INSERT ... SELECT 와 거의 동일하게 동작하므로 APPEND 힌트(혹은 APPEND_VALUES 힌트)가 확실히 의미가 있겠습니다.

    좋은 발견입니다.

    • 욱짜 2010/06/24 15:37 Modify/Delete

      좀 더 점검을 해보니 SAVE EXCEPTIONS 구문과는 같이 사용이 안되는군요. 다음과 같은 에러가 발생합니다.

      ORA-38910: BATCH ERROR mode is not supported for this operation

      만일 Oracle 10g에서 FORALL INSERT VALUES 구문에 대해 Direct Path Insert를 사용하려면 다음과 같은 방법을 사용할 수도 있습니다.

      insert /*+ append */ into t1 select * from table(cast(... as ...));

      이 구문을 사용하려면 Collection Type이 create or replace를 통해 명시적으로 선언되어있어야 할 것 같은데요. 제약으로 여길 수 있겠습니다.

      INSERT ... VALUES ... 구문에서 APPEND(11gR1)나 APPEND_VALUES(11gR2) 힌트를 지원하는 목적인 PL/SQL Batch Insert인지 아닌지는 알 수 없지만 잘 이용하면 유용하다는 것만은 확실해보입니다.

  4. ilsantan 2013/02/20 14:32 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment

◀ PREV : [1] : ... [42] : [43] : [44] : [45] : [46] : [47] : [48] : [49] : [50] : ... [354] : NEXT ▶