컬럼 default not null 변경시 컬럼저장 방식 개선
vpn_key 오라클 12c이후 부터는 not null 컬럼 추가시 물리적인 저장을 하지 않지 메터정보를 참고하도록 변경되었다.
- - 12c부터는 NOT NULL 여부와 관계없이 DEFAULT 값이 있는 컬럼 추가 시 메타데이터만 저장되며, 실제 데이터가 UPDATE될 때 비로소 물리적으로 저장됩니다.
playlist_add_check오라클 공식 레퍼런스 내용 참조
- Oracle 12c에서는 NULL을 허용하는 컬럼의 기본값이 데이터 딕셔너리에 유지되며, DEFAULT 값을 가진 새 컬럼을 추가할 때 더 이상 기존의 모든 레코드에 기본값을 저장할 필요가 없습니다. 이를 통해 스키마 수정이 몇 초 안에 완료되고 기존 데이터 볼륨과 무관하며 공간도 소비하지 않습니다. Oracle Database 12c Release 1 (12.1.0.1) New Features
- ALTER TABLE 컬럼에 DEFAULT 절을 지정하면 기본값이 메타데이터로 저장되지만 컬럼 자체는 데이터로 채워지지 않습니다. 그러나 새 컬럼을 지정하는 후속 쿼리는 결과 세트에 기본값이 반환되도록 재작성됩니다.
- Oracle 11g에서는 메타데이터 전용 기본값 개념을 도입했습니다. DEFAULT 절이 있는 NOT NULL 컬럼을 기존 테이블에 추가하는 것이 테이블의 모든 행을 변경하는 대신 메타데이터 변경만 수반했습니다. 새 컬럼에 대한 쿼리는 옵티마이저에 의해 재작성되어 결과가 기본값 정의와 일치하도록 보장했습니다. Oracle 12c는 이를 한 단계 더 발전시켜 필수 컬럼과 선택적 컬럼 모두에 대한 메타데이터 전용 기본값을 허용합니다. 따라서 DEFAULT 절이 있는 새 컬럼을 기존 테이블에 추가하는 것은 해당 컬럼이 NOT NULL로 정의되었는지 여부에 관계없이 메타데이터 전용 변경으로 처리됩니다. 이는 공간 절약과 성능 향상을 모두
- 내부 동작 방식
- - 11g에서는 Oracle이 내부적으로 NVL 표현식으로 컬럼을 재작성합니다. 12c에서는 NULL을 허용하는 컬럼의 경우 더 복잡한 표현식인 DECODE와 SYS_OP_VECBIT 함수를 사용하여 처리합니다.
- 제약 사항
- - 인덱스 구성 테이블(not), 임시 테이블, 클러스터의 일부는 불가하며 , 큐 테이블, 객체 테이블 또는 구체화된 뷰의 컨테이너 테이블도 불가함.
```sql
-- 1. 컬럼제약조건
CREATE TABLE COL_NOTNULL_CHK_TEST(AGRNEN_REG_NO VARCHAR2(1 BYTE) NOT NULL);
-- 2.TEST_YN CHAR(1) DEFAULT 'N' NOT NULL; 컬럼 추가 ALTER TABLE COL_NOTNULL_CHK_TEST ADD TEST_YN CHAR(1) DEFAULT 'N' NOT NULL;
-- 3.ADD1_YN CHAR(1) DEFAULT 'N' NOT NULL; 컬럼 추가 ALTER TABLE DBAKM.COL_NOTNULL_CHK_TEST ADD ADD1_YN CHAR(1) DEFAULT 'N' NOT NULL;
CREATE TABLE DBAKM.COL_NOTNULL_CHK_TEST AS SELECT AGRNEN_REG_NO FROM DBAKM.COL_NOTNULL_CHK_TEST 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 DBAKM.COL_NOTNULL_CHK_TEST WHERE AGRNEN_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 '%cra_%' 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
-- [2] TEST_YN CHAR(1) 컬럼 추가 NOT NULL DEFAULT 'N' ALTER TABLE DBAKM.COL_NOTNULL_CHK_TEST ADD TEST_YN CHAR(1) DEFAULT 'N' NOT NULL;
-- [1]번째 ROW UPDATE - 데이터가 실제 저장되는지 확인 ] UPDATE DBAKM.COL_NOTNULL_CHK_TEST SET TEST_YN='Y' WHERE AGRNEN_REG_NO=1006036461;
SELECT * FROM DBAKM.COL_NOTNULL_CHK_TEST -- WHERE AGRNEN_REG_NO=1006036404
-- AGRNEN_REG_NO,WTOT_UTEZN_AGRE_YN --1006036404,N
-- 블럭 덤프 -- 블럭 덤프 ```
- 페이지 2:**
```sql 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 (AGRNEN_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의 업데이트를 실시 <== AGRNEN_REG_NO=1001976357
-- [2번째 ROW UPDATE - 데이터가 실제 저장되는지 확인 ] UPDATE DBAKM.COL_NOTNULL_CHK_TEST SET TEST_YN='Y' WHERE AGRNEN_REG_NO=1001976357;
block_row_dump: -- [AGRNEN_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
-- [AGRNEN_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
-- [AGRNEN_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 -- [AGRNEN_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';
AGRNEN_REG_NO VARCHAR2 NO NO SYS_C00002_25092615-44:55 CHAR YES NO <== 기존에 세개 되있지만 안쓰지 계속 되어 있는 것은 , 가상컬럼이 저장된 것으. TEST_YN CHAR NO NO
```
- 페이지 3:**
``` -- [공통] →1.오라클 12C 부터는 not null 컬럼이 추가될 경우 실제 데이터가 저장되지 않고 메타정보만 저장 된다. -- 단, 데이터가 입력되거나 변경시 컬럼이 실제 데이터가 기록된다.
-- [추가 의문] -- 다른 컬럼의 추가 되면서 안된다. -- (공통) 다른 컬럼이 추가 되면 내부 딕셔너리 기준으로 컬럼을 변경되지 않는다.
(데이터가 없어 존재하지 안은데라 그대로 존재 TEST_YN 은 default 'N' not null 이지만 실제로 데이터변경이 갈이 저장되어 있지 않음.)
-- 다른 컬럼[ADD1_YN] 추가후 업데이트 되면으로 TEST_YN 컬럼은 업데이트 안된다.
-- [추가 확장 검증 ] ALTER TABLE DBAKM.COL_NOTNULL_CHK_TEST ADD ADD1_YN CHAR(1) DEFAULT 'N' NOT NULL; SELECT * FROM DBAKM.COL_NOTNULL_CHK_TEST; UPDATE DBAKM.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 DBAKM.COL_NOTNULL_CHK_TEST WHERE AGRNEN_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 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 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 ```
- 페이지 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 ```
---
문서는 Oracle 12c 이상에서 NOT NULL DEFAULT 제약조건이 있는 컬럼 추가 시 물리적 저장 방식을 블록 덤프로 분석한 내용입니다. 주요 발견사항은 12c부터는 NOT NULL DEFAULT 컬럼 추가 시 실제 데이터를 저장하지 않고 메타데이터만 저장하며, 데이터가 UPDATE될 때 비로소 물리적으로 저장된다는 점입니다.