Add column

EXEM Knowledge Base

Jump to: navigation, 찾기

Oracle 11g 에서는 테이블에 컬럼을 추가 하면서 Default Value를 설정 해 줄 수 있다.


이럴 경우 흔히 생각하기에 많은 데이터를 가진 테이블에 컬럼을 추가 하면서 Default value까지 지정을 해 주면 컬럼을 추가 하는 overhead 및 default value를 넣어주면서 발생하는 update에 대한 overhead를 생각할 수 있다.

그러나 11g에서는 기존 데이터에 대한 업데이트 대신 메타데이터에 저장되어 있는 default value값을 읽어 가게끔 해 놓았다.


테스트를 통하여 해당 기능에 대해서 알아 보자

- 테스트 환경

  노트북에 Asianux 2.0 (2.6.9-42.7AXsmp) + Oracle 11g 11.1.0.6

- 테스트 시나리오

  1.Table 생성 
  2. 데이터 insert
  3. Block dump
  4. Add Column  with default value
  5. Block dump
  6. SELECT statements
  7. Insert New Data   

-- 테스트에 사용된 스크립트 --

- 테이블 생성

  CREATE TABLE ADD_COL_TEST
  ( id number ,
    name varchar2(1000) ,
    sal  number ) ;

- 데이터 인서트

  BEGIN
    FOR i IN 1..100000 LOOP
       INSERT INTO ADD_COL_TEST VALUES( i , 'add_test', 100) ;
       COMMIT ;
    END LOOP ;
  END ;
  /

- 컬럼 추가

  SQL>alter table ADD_COL_TEST add add_col varchar2(10) default 'ADD COLUMN' NOT NULL ;


- 테스트 시작


테이블을 생성 한 후 데이터를 넣고 블럭 덤프를 수행 함 ( 이후의 블럭의 내용과 비교해 보기 위함) 전체 블록 대신에 id값이 1번인 데이터가 들어있는 블록에 대하여만 덤프를 수행해 보겠슴


  SQL> SELECT dbms_rowid.rowid_relative_fno( ROWID ) ,
                       dbms_rowid.rowid_block_number( ROWID )
           FROM   add_col_test
           WHERE  id = 1 ;


  DBMS_ROWID.ROWID_RELATIVE_FNO( DBMS_ROWID.ROWID_BLOCK_NUMBER(
  ------------------------------ ------------------------------
                                 4                          17743
  1 row 


덤프 수행

  SQL> alter system dump datafile 4 block 17743 ;


다음은 블럭의 덤프 내용이다.

  block_row_dump: 
  tab 0, row 0, @0x1f86
  tl: 18 fb: --H-FL-- lb: 0x0  cc: 3
  col  0: [ 2]  c1 02
  col  1: [ 8]  61 64 64 5f 74 65 73 74
  col  2: [ 2]  c2 02   
  tab 0, row 1, @0x1f74
  tl: 18 fb: --H-FL-- lb: 0x0  cc: 3
  col  0: [ 2]  c1 03
  col  1: [ 8]  61 64 64 5f 74 65 73 74
  col  2: [ 2]  c2 02   
  tab 0, row 2, @0x1f62
  tl: 18 fb: --H-FL-- lb: 0x0  cc: 3
  col  0: [ 2]  c1 04
  col  1: [ 8]  61 64 64 5f 74 65 73 74
  col  2: [ 2]  c2 02
  ........ 생략

여기서 간단히 설명을 하자면 cc라는 값은 컬럼 카운트를 나타낸다. 현재 모든 row가 컬럼 카운트가 3임을 알 수 있다.


테이블에 컬럼을 추가 한 후에 동일 블록에 대한 덤프를 수행해 보면 다음과 같은 결과를 확인 할 수 있다.


  block_row_dump:
  tab 0, row 0, @0x1f86
  tl: 18 fb: --H-FL-- lb: 0x0  cc: 3
  col  0: [ 2]  c1 02
  col  1: [ 8]  61 64 64 5f 74 65 73 74
  col  2: [ 2]  c2 02
  tab 0, row 1, @0x1f74
  tl: 18 fb: --H-FL-- lb: 0x0  cc: 3
  col  0: [ 2]  c1 03
  col  1: [ 8]  61 64 64 5f 74 65 73 74
  col  2: [ 2]  c2 02
  tab 0, row 2, @0x1f62
  tl: 18 fb: --H-FL-- lb: 0x0  cc: 3 
  col  0: [ 2]  c1 04
  col  1: [ 8]  61 64 64 5f 74 65 73 74
  col  2: [ 2]  c2 02
  ....... 생략


컬럼을 추가 하기 전이나 추가한 후나 컬럼 카운트는 변화가 없다.


  SQL>SELECT table_name , column_name , NULLABLE , DATA_DEFAULT   FROM dba_tab_columns
          WHERE owner = 'SCOTT'
           AND   table_name = 'ADD_COL_TEST';
  TABLE_NAME               COLUMN_NAME         NULLABLE         DATA_DEFAULT
  ----------------- ------------------ -------- ----------------------------------------
  ADD_COL_TEST                   ID                          Y
  ADD_COL_TEST                   NAME                     Y
  ADD_COL_TEST                   SAL                       Y
  ADD_COL_TEST                   ADD_COL               N                'ADD COLUMN'
  4 rows


위의 쿼리를 통하여 메타 데이터를 조회해 보면 컬럼이 4개임을 확인 할 수 있다.


  SQL>SELECT *
          FROM   add_col_test
          WHERE  id < 3
                 ID NAME                                                      SAL ADD_COL   
  -------------------- ---------------------------------------- -------------------- ----------
                  1 add_test                                                  100 ADD COLUMN
                  2 add_test                                                  100 ADD COLUMN
  2 rows 

테이블을 조회 하면 정상적으로 데이터가 나온다.


즉, 추가되는 컬럼에 Default 값을 줄 수 있으나 이는 실제 물리적인 공간을 할당 한 후에 데이터를 넣는 방법이 아니라 , Disk I/O에 대한 overhead를 줄이기 위하여 메타 데이터만 변경 한 후에 SELECT 쿼리에 대한 값을 리턴해 주고 있다.


그럼 row를 업데이트 하면 어떻게 되는 지 확인 해 보자


  SQL> update add_col_test set add_col='update' where id = 1 ;


블럭에 대한 덤프를 수행 한 후에 내용을 확인 해 보면 비로서 물리적인 공간에 변화가 있음을 확인 할 수 있다.


  block_row_dump:
  tab 0, row 0, @0x5eb
  tl: 25 fb: --H-FL-- lb: 0x1  cc: 4 
  col  0: [ 2]  c1 02
  col  1: [ 8]  61 64 64 5f 74 65 73 74
  col  2: [ 2]  c2 02
  col  3: [ 6]  75 70 64 61 74 65
  tab 0, row 1, @0x1f74
  tl: 18 fb: --H-FL-- lb: 0x0  cc: 3
  col  0: [ 2]  c1 03
  col  1: [ 8]  61 64 64 5f 74 65 73 74
  col  2: [ 2]  c2 02
  tab 0, row 2, @0x1f62
  tl: 18 fb: --H-FL-- lb: 0x0  cc: 3
  col  0: [ 2]  c1 04
  col  1: [ 8]  61 64 64 5f 74 65 73 74
  col  2: [ 2]  c2 02
  ... 생략


물리적인 공간의 변화가 일어 났다.


새로운 데이터를 insert 하면서 마지막 추가된 컬럼에 Default value를 사용하도록 해 보자


  SQL> insert into add_col_test( id ,name,sal) values ( 999999 , 'insert' , 999  ) ;


  SQL>SELECT dbms_rowid.rowid_relative_fno( ROWID ) ,
                      dbms_rowid.rowid_block_number( ROWID )
          FROM   add_col_test
         WHERE  id = 999999 ;
  DBMS_ROWID.ROWID_RELATIVE_FNO( DBMS_ROWID.ROWID_BLOCK_NUMBER(
  ------------------------------ ------------------------------
                               7                            211
  1 row 


  SQL> alter system dump datafile 7 block 211 ;


  tab 0, row 203, @0xfac
  tl: 18 fb: --H-FL-- lb: 0x2  cc: 3
  col  0: [ 2]  c3 0b
  col  1: [ 8]  61 64 64 5f 74 65 73 74
  col  2: [ 2]  c2 02
  end_of_block_dump
  End dump data blocks tsn: 4 file#: 7 minblk 211 maxblk 211

해당 블록의 마지막 row의 내용을 확인해 보면 역시 컬럼 카운트가 3 임을 확인 할 수 있다. Null이 들어갈 경우 Default 값으로 대체 하여야 하므로 불 필요하게 블록의 변경을 가 하지 않고 메타 데이터를 사용하게 하였다.