Add column
EXEM Knowledge Base
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 값으로 대체 하여야 하므로 불 필요하게 블록의 변경을 가 하지 않고 메타 데이터를 사용하게 하였다.