오라클 컬럼저장 방식 개선 (12c 업그레이드)

컬럼 default not null 변경시 컬럼저장 방식 개선

  vpn_key 오라클 12c이후 부터는 not null 컬럼 추가시 물리적인 저장을 하지 않고 메터정보를 참고 하도록 변경되었다.

- 12c부터는 NOT NULL 여부와 관계없이 DEFAULT 값이 있는 컬럼 추가 시 메타데이터만 저장되며, 실제 데이터가 UPDATE될 때 비로소 물리적으로 저장됩니다.

  playlist_add_check오라클 공식 레퍼런스 내용 참조

1. Oracle 12c에서는 NULL을 허용하는 컬럼의 기본값이 데이터 딕셔너리에 유지되며, DEFAULT 값을 가진 새 컬럼을 추가할 때 더 이상 기존의 모든 레코드에 기본값을 저장할 필요가 없습니다. 이를 통해 스키마 수정이 몇 초 안에 완료되고 기존 데이터 볼륨과 무관하며 공간도 소비하지 않습니다. Oracle Database 12c Release 1 (12.1.0.1) New Features
2. ALTER TABLE 컬럼에 DEFAULT 절을 지정하면 기본값이 메타데이터로 저장되지만 컬럼 자체는 데이터로 채워지지 않습니다. 그러나 새 컬럼을 지정하는 후속 쿼리는 결과 세트에 기본값이 반환되도록 재작성됩니다.
3. Oracle 11g에서는 메타데이터 전용 기본값 개념을 도입했습니다. DEFAULT 절이 있는 NOT NULL 컬럼을 기존 테이블에 추가하는 것이 테이블의 모든 행을 변경하는 대신 메타데이터 변경만 수반했습니다. 새 컬럼에 대한 쿼리는 옵티마이저에 의해 재작성되어 결과가 기본값 정의와 일치하도록 보장했습니다. Oracle 12c는 이를 한 단계 더 발전시켜 필수 컬럼과 선택적 컬럼 모두에 대한 메타데이터 전용 기본값을 허용합니다. 따라서 DEFAULT 절이 있는 새 컬럼을 기존 테이블에 추가하는 것은 해당 컬럼이 NOT NULL로 정의되었는지 여부에 관계없이 메타데이터 전용 변경으로 처리됩니다. 이는 공간 절약과 성능 향상을 모두
4. 내부 동작 방식

   - 11g에서는 Oracle이 내부적으로 NVL 표현식으로 컬럼을 재작성합니다. 12c에서는 NULL을 허용하는 컬럼의 경우 더 복잡한 표현식인 DECODE와 SYS_OP_VECBIT 함수를 사용하여 처리합니다.

5. 제약 사항

   - 인덱스 구성 테이블(iot), 임시 테이블, 클러스터의 일부는 불가하며 , 큐 테이블, 객체 테이블 또는 구체화된 뷰의 컨테이너 테이블도 불가함.

default 컬럼 블럭 확인 및 테스트

• 컬럼의 블럭을 확인 하는 절차는 다음과 같다

1. 테스트 테이블 생성
2. default not null 컬럼 추가 (TEST_YN)
3. 블럭 트레이스 및 덤프 파일에서 결과 확인
4. 신규 컬럼 추가 (ADD1_YN) : 컬럼이 추가 되었때 블럭의 변화를 확인 하기 위함
5. 블럭 트레이스 및 결과 확인 (실제 데이터가 있는지 확인)

테이블 물리적 블럭 내용

-- 1. 테이블 생성 
CREATE TABLE COL_NOTNULL_CHK_TEST(REG_NO VARCHAR2(1 BYTE) NOT NULL);

-- 2. 테스트를 위해 기존 테이블에서 데이터 10개 복사(개별로 추가해도 됨)
CREATE TABLE COL_NOTNULL_CHK_TEST
AS
SELECT REG_NO FROM COL_NOTNULL_CHK -- 더미테이블임(사용자에게 없을수 있음.)
WHERE ROWNUM <= 10
;

-- 컬럼의 물리적 파일-블럭 구조 조회
SELECT DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) AS FILE_ID -- 파일 번호
     , DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS BLOCK_ID -- 블럭 번호
     , DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)  AS ROW_NUM -- 로우번호 
FROM COL_NOTNULL_CHK_TEST
WHERE REG_NO=1003976357
;
-- [결과] 
--FILE_ID, BLOCK_ID, ROW_NUM
     15   935475      0
;

-- 블럭 덤프 수행 
alter session set timed_statistics = TRUE;
--alter session set max_dump_file_size = 100M;
alter session set tracefile_identifier='CYKUN_DUMP'; -- 덤프파일 식별자 지정

ALTER SESSION SET SQL_TRACE = TRUE;
ALTER SYSTEM DUMP DATAFILE 15 block 935475;
ALTER SESSION SET SQL_TRACE = FALSE;

-- 덤프파일 트레이스 파일 경로
select trace_filename,to_char(modify_time,'YYYY-MM-DD HH24:MI:SS') as modified
       , filesize
  from v$diag_trace_file
 where trace_filename like '%ora_%'
 order by modify_time DESC;

-- .trc 파일 확인 
Dump of buffer cache at level 3 for pdb# tsn=14 rdba=03850b15
BH (0x42f4d8ca8) file# 15 rdba: 0x03cce633 (15/935475) class: 1 ba: 0x42eea8000
....
block_row_dump:
tab 0, row 0, @0x1f72
tl: 14 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [10]  31 30 30 31 39 31 39 36 35 36 30 38 30 38
end_of_block_dump
;

디폴트 not null 컬럼 추가

-- [2] TEST_YN CHAR(1) 컬럼 추가 NOT NULL DEFAULT 'N'
ALTER TABLE COL_NOTNULL_CHK_TEST ADD TEST_YN CHAR(1) DEFAULT 'N' NOT NULL;

-- [1]번째 ROW UPDATE - 데이터가 실제 저장되는지 확인 
UPDATE COL_NOTNULL_CHK_TEST 
   SET TEST_YN='Y'
WHERE REG_NO=1006036461;

SELECT * FROM DBAKM.COL_NOTNULL_CHK_TEST
-- WHERE REG_NO=1006036404
;
-- REG_NO, TEST_YN
--1006036404,N

-- 블럭 덤프
alter session set timed_statistics = TRUE;
--alter session set max_dump_file_size = 100M;
alter session set tracefile_identifier='CYKUN_DUMP2';

ALTER SESSION SET SQL_TRACE = TRUE;
ALTER SYSTEM DUMP DATAFILE 15 block 935475;
ALTER SESSION SET SQL_TRACE = FALSE;

-- 트레이스 파일 경로
select trace_filename,to_char(modify_time,'YYYY-MM-DD HH24:MI:SS') as modified
       , filesize
  from v$diag_trace_file
 where trace_filename like '%cra_%'
 order by modify_time DESC;
;

-- .trc파일에서 FILE_ID = 15 ,BLOCK_ID=935475 , ROW_NUM=1 인 로우를 찾기 (1번째 UPDATE 치고 덤프 조회 )

block_row_dump:
------------------------------------------------------------
tab 0, row 0, @0x1e63        <== 1번째 ROW (REG_NO=1006036404) ,
tl: 17 fb: --H-FL-- lb: 0x2  cc: 3  <== tl 컬럼 행길이 : 17바이트 ,  fb행플그 :  Head-piece,First row piece,Last row piece / lb : lock byte :0x02(로우락
킹) // cc:3  컬럼갯수(column count: 3개 )
col  0: [10]  31 30 30 31 39 31 39 36 35 36 30 38 30 38     <== hex값:  1006036404
col  1: *NULL*                      <== 이건 무슨 컬럼인가??? (아래 참조 설명) 
col  2: [ 1]  59                    <== 아스키코드 0x59 는 'Y'
------------------------------------------------------------

tab 0, row 1, @0x1f64
tl: 14 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [10]  31 30 30 31 39 31 39 37 39 36 35 39 31 35 37   <=
.....

• 이후 2번째 row의 업데이트를 실시 <== REG_NO=1001976357

-- [2번째 ROW UPDATE - 데이터가 실제 저장되는지 확인 ]
UPDATE COL_NOTNULL_CHK_TEST 
   SET TEST_YN='Y'
WHERE REG_NO=1001976357;

block_row_dump:
-- [REG_NO=1006036404]
tab 0, row 0, @0x1e63
tl: 17 fb: --H-FL-- lb: 0x2  cc: 3
col  0: [10]  31 30 30 31 39 31 39 36 35 36 30 38 30 38 39 34
col  1: *NULL*
col  2: [ 1]  59    <== 1번째 ROW UPDATE한 값

-- [REG_NO=1001976357] <== 2번째 업데이트 처리
tab 0, row 1, @0x1e02
tl: 17 fb: --H-FL-- lb: 0x3  cc: 3
col  0: [10]  31 30 30 31 39 31 39 37 39 36 35 39 31 35 37
col  1: *NULL*
col  2: [ 1]  59     <== 2번째 ROW UPDATE한 값

-- [REG_NO=*** ] 3번째 row , 2번째 컬럼값이 기본값이 저장됨.
tab 0, row 2, @0x1f56
tl: 14 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [10]  31 30 30 31 39 31 39 31 31 37 30 36 35 36 30 38
-- [REG_NO=*** ] 3번째 row , 2번째 컬럼값이 기본값이 저장됨.
tab 0, row 3, @0x1f48
tl: 14 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [10]  31 30 30 31 39 36 34 32 35 38 37 39 34
------------------------------------------------------------

• [참고] ===> 여기에서 cc가 컬럼갯수인데 현재 컬럼을 2개 추가했는데 cc:3인 이유?

select column_name,data_type,hidden_column,virtual_column
  from dba_tab_cols
 where table_name='COL_NOTNULL_CHK_TEST';
------------------------------------------------------------
REG_NO        VARCHAR2       NO        NO
SYS_C00002_25092615-44:55 CHAR      YES       NO  <== 기존에 존재하던 컬럼이 삭제하거나 가상컬럼이 저장된것임.
TEST_YN              CHAR           NO        NO
------------------------------------------------------------

테스트 결과

menu_book * 오라클 12C 부터는 not null 컬럼이 추가될 경우 실제 데이터가 저장되지 않고 메타정보만 저장 된다.

• 단, 데이터가 입력되거나 변경시 컬럼이 실제 데이터가 기록된다.

추가 의문 사항

1. 신규로 컬럼의 추가 하면 블럭의 변화가 발생 될까?

   -- (결론) 다른 컬럼이 추가/변경 되더라도 기존 컬럼구조는 변경되지 않는다.

   -- (데이터에 값이 존재하지 않는 상태 그대로 존재함.TEST_YN 은 default 'N' not null 이지만 실제 데이터블럭에는 값이 저장되어 있지 않음.)

   -- 다른 컬럼[ADD1_YN] 추가로 업데이트 되더라도 TEST_YN 컬럼은 변경되지 않는다.

-- [추가 확장 검증 ]
ALTER TABLE COL_NOTNULL_CHK_TEST ADD ADD1_YN CHAR(1) DEFAULT 'N' NOT NULL;
SELECT * FROM COL_NOTNULL_CHK_TEST;
UPDATE COL_NOTNULL_CHK_TEST SET ADD1_YN='C';

-- 블럭 덤프
ALTER SYSTEM DUMP DATAFILE 15 block 935475;

-- 컬럼의 물리적 구조
SELECT DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) AS FILE_ID
     , DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS BLOCK_ID
     , DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)  AS ROW_NUM
  FROM COL_NOTNULL_CHK_TEST
 WHERE REG_NO=1006036404
;
--FILE_ID, BLOCK_ID, ROW_NUM
15       935475     0
;

-- 블럭 덤프
alter session set timed_statistics = TRUE;
--alter session set max_dump_file_size = 100M;
alter session set tracefile_identifier='CYKUN_DUMP2';

ALTER SESSION SET SQL_TRACE = TRUE;
ALTER SYSTEM DUMP DATAFILE 15 block 935475;
ALTER SESSION SET SQL_TRACE = FALSE;

block_row_dump:
tab 0, row 0, @0x1e0f
tl: 18 fb: --H-FL-- lb: 0x1  cc: 4
col  0: [10]  31 30 30 31 39 31 39 36 35 36 30 38 30 39 34
col  2: [ 1]  59   <== 1번째 
col  3: [ 1]  43
tab 0, row 1, @0x1eac
tl: 18 fb: --H-FL-- lb: 0x1  cc: 4
col  0: [10]  31 30 30 31 39 31 39 37 39 36 35 39 31 35 37
col  1: *NULL*
col  2: [ 1]  59   <== 2번째 
col  3: [ 1]  43
tab 0, row 2, @0x1e9a
tl: 18 fb: --H-FL-- lb: 0x1  cc: 4
col  0: [10]  31 30 30 31 39 31 39 31 31 37 30 36 35 33 39 30
col  1: *NULL*
col  2: *NULL*       -- <== 데이터가 저장되어 있지않음( 결국 , 조회시 메타정보를 이용한다는 의미임)
col  3: [ 1]  43
tab 0, row 3, @0x1e88
tl: 18 fb: --H-FL-- lb: 0x1  cc: 4
col  0: [10]  31 30 30 31 39 36 34 32 35 38 37 39 34 37 36
col  1: *NULL*
col  2: *NULL*       --  <== 데이터가 저장되어 있지않음( 결국 , 조회시 메타정보를 이용한다는 의미임)
col  3: [ 1]  43
tab 0, row 4, @0x1e76
tl: 18 fb: --H-FL-- lb: 0x1  cc: 4
col  0: [10]  31 30 30 31 39 30 31 31 39 37 36 35 33 39 36
col  1: *NULL*
col  2: *NULL*
col  3: [ 1]  43
tab 0, row 5, @0x1e64
tl: 18 fb: --H-FL-- lb: 0x1  cc: 4
col  0: [10]  31 30 30 31 39 30 30 33 39 36 35 39 39 36
col  1: *NULL*
col  2: *NULL*       --  <== 데이터가 저장되어 있지않음( 결국 , 조회시 메타정보를 이용한다는 의미임)
col  3: [ 1]  43
tab 0, row 6, @0x1e52
tl: 18 fb: --H-FL-- lb: 0x1  cc: 4
col  0: [10]  31 30 30 31 39 30 31 37 39 36 37 39 37 34 30
col  1: *NULL*
col  2: *NULL*
col  3: [ 1]  43
tab 0, row 7, @0x1e40
tl: 18 fb: --H-FL-- lb: 0x1  cc: 4
col  0: [10]  31 30 30 31 39 30 31 35 33 37 39 32 30
col  1: *NULL*
col  2: *NULL*
col  3: [ 1]  43
tab 0, row 8, @0x1e2e
tl: 18 fb: --H-FL-- lb: 0x1  cc: 4
col  0: [10]  31 30 30 31 39 32 34 36 31 31 37 39
col  1: *NULL*
col  2: *NULL*
col  3: [ 1]  43
tab 0, row 9, @0x1e1c
tl: 18 fb: --H-FL-- lb: 0x1  cc: 4
col  0: [10]  31 30 30 31 39 32 30 39 37 31 35 32
col  1: *NULL*
col  2: *NULL*
col  3: [ 1]  43
end_of_block_dump

---

menu_book 테스트한 결과 오라클 12c부터는 NOT NULL DEFAULT 컬럼 추가 시 실제 데이터를 저장하지 않고 메타데이터만 저장하며, 데이터가 UPDATE될 때 비로소 물리적으로 저장된다는 점입니다.