히든파라미터 _add_col_optim_enabled, 대용량 테이블 컬럼 추가

1. 개요

대용량 테이블에 새로운 컬럼을 추가하며 DEFAULT 값을 지정할 경우, 오라클은 기본적으로 전체 레코드를 수정하는 물리적 업데이트를 수행합니다. 이 과정에서 발생하는 대량의 I/O 부하와 리소스를 방지하기 위해, 물리적 수정 없이 딕셔너리 정보만 변경하는 최적화 기법이 필요합니다.

---

2. 히든 파라미터

Oracle 11g부터 도입된 Fast Add 기능의 핵심은 Metadata Only Default 방식입니다.

• 동작 원리: 데이터 블록을 직접 수정하지 않고 데이터 딕셔너리에만 기본값을 기록합니다. 이후 사용자가 데이터를 조회하는 시점에 실시간으로 값을 병합하여 결과를 반환합니다.
• 핵심 파라미터: 19c 환경에서는 _add_col_optim_enabled 파라미터가 이 최적화 기능을 제어합니다.

---

3. 해결 방법 및 실전 적용

STEP 1: 히든 파라미터 활성화 확인

먼저 해당 기능이 활성화되어 있는지 확인합니다. (기본값: TRUE)

SELECT n.ksppinm, v.ksppstvl
FROM x$ksppi n, x$ksppcv v
WHERE n.indx = v.indx
  AND n.ksppinm = '_add_col_optim_enabled';

-- 결과 확인
KSPPINM                        KSPPSTVL
------------------------------ ------------------------------
_add_col_optim_enabled         TRUE

STEP 2: 메타데이터 최적화 방식으로 컬럼 추가

DEFAULT 값과 NOT NULL 제약 조건을 포함하여 컬럼을 추가합니다. 최적화가 적용되면 수백만 건의 데이터도 1초 내외로 작업이 완료됩니다.

ALTER TABLE [SCHEMA].[TABLE] ADD ([COLUMN] VARCHAR2(1) DEFAULT 'N' NOT NULL);

STEP 3: Fast Add 적용 여부 시스템 검증

수행 시간 외에도 내부 시스템 테이블 조회를 통해 비트 설정을 확인하여 확신을 얻을 수 있습니다.

SELECT o.name AS table_name,
       c.name AS column_name,
       c.property,
       -- 11g 방식 비트 (0x20000000)
       decode(bitand(c.property, 536870912), 536870912, 'YES', 'NO') as IS_BIT_29,
       -- 12c/19c 방식 비트 (0x40000000)
       decode(bitand(c.property, 1073741824), 1073741824, 'YES', 'NO') as IS_BIT_30
FROM sys.col$ c, sys.obj$ o, sys.user$ u
WHERE o.obj# = c.obj#
  AND o.owner# = u.user#
  AND u.name = '[SCHEMA]' -- 대문자로 계정명 입력
  AND o.name = '[TABLE_NAME]'
  AND c.name = '[COLUMN_NAME]';

-- 기대 결과
TABLE_NAME      COLUMN_NAME    PROPERTY     IS_BIT_29   IS_BIT_30
--------------- -------------- ------------ ----------- -----------
[TABLE_NAME]    [COLUMN_NAME]  1073741824   NO          YES

 

• 해석: PROPERTY 값이 1073741824 (30번째 비트 활성화)라면, 19c 엔진이 물리적 데이터 수정 없이 메타데이터로 컬럼을 관리하고 있음을 의미합니다.

---

4. 데이터 이동 및 복제 시 동작 특성 검증

1) CTAS 테스트

복제된 테이블에서도 최적화 속성이 유지되는지 확인합니다.

-- 테이블 복제 실행
CREATE TABLE [SCHEMA].[TABLE_CTAS] AS SELECT * FROM [SCHEMA].[TABLE];

-- 복제본 비트 확인 (결과: IS_BIT_30이 NO로 변경됨)
SELECT o.name AS table_name,
       c.name AS column_name,
       c.property,
       -- 11g 방식 비트 (0x20000000)
       decode(bitand(c.property, 536870912), 536870912, 'YES', 'NO') as IS_BIT_29,
       -- 12c/19c 방식 비트 (0x40000000)
       decode(bitand(c.property, 1073741824), 1073741824, 'YES', 'NO') as IS_BIT_30
FROM sys.col$ c, sys.obj$ o, sys.user$ u
WHERE o.obj# = c.obj#
  AND o.owner# = u.user#
  AND u.name = '[SCHEMA]' -- 대문자로 계정명 입력
  AND o.name = '[TABLE_NAME]'
  AND c.name = '[COLUMN_NAME]';
  
TABLE_NAME      COLUMN_NAME    PROPERTY     IS_BIT_29   IS_BIT_30
--------------- -------------- ------------ ----------- -----------
[TABLE_NAME]    [COLUMN_NAME]  0            NO          NO

-- 결과 데이터 확인
SELECT DISTINCT [COLUMN] FROM [SCHEMA].[TABLE_CTAS]; -- 결과: 'N'

• 결과: PROPERTY가 0으로 조회됩니다. 이는 복제 시 메타데이터 속성이 전이되지 않고, 모든 행에 값이 물리적으로 반영되었음을 뜻합니다.

2) 데이터베이스 링크 테스트

원격 서버(19c)에서 조회 시 값이 정상 수신되는지 확인합니다.

SELECT DISTINCT [COLUMN] FROM [SCHEMA].[TABLE]@[DBLINK];
-- 결과: 'N' 정상 수신

3) 데이터 펌프 테스트

백업 및 복구 시 최적화 상태 보존 여부를 세부 단계별로 확인합니다.

• STEP 1: 내보내기(Export) 실행

  nohup expdp system/[PASSWORD] \
    DIRECTORY=[DATA_PUMP_DIR] \
    DUMPFILE=[FILE_NAME]_%U.dmp \
    LOGFILE=[LOG_NAME].log \
    PARALLEL=[DEGREE] \
    TABLES=[SCHEMA].[TABLE] \
    CONTENT=ALL \
  &
  
  # 로그 확인
  Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
  Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
  Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
  Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
  Processing object type TABLE_EXPORT/TABLE/TABLE
  Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
  Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
  Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
  . . exported "[SCHEMA]"."[TABLE]"             1.404 GB 6500819 rows

• STEP 2: 타 DB로 가져오기(Import) 실행

  nohup impdp system/[PASSWORD] \
    DIRECTORY=[DATA_PUMP_DIR] \
    DUMPFILE=[FILE_NAME]_%U.dmp \
    LOGFILE=[LOG_NAME]_IMP.log \
    PARALLEL=4 \
    TABLES=[SCHEMA].[TABLE] \
    TABLE_EXISTS_ACTION=REPLACE \
  &
  
  # 로그 확인
  Processing object type TABLE_EXPORT/TABLE/TABLE
  Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
  . . imported "[SCHEMA]"."[TABLE]"             1.404 GB 6500819 rows
  Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
  Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
  Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
  Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
  Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

• STEP 3: 가져온 테이블의 비트 검증

  -- Import된 테이블 속성 조회
  SELECT o.name AS table_name,
         c.name AS column_name,
         c.property,
         -- 11g 방식 비트 (0x20000000)
         decode(bitand(c.property, 536870912), 536870912, 'YES', 'NO') as IS_BIT_29,
         -- 12c/19c 방식 비트 (0x40000000)
         decode(bitand(c.property, 1073741824), 1073741824, 'YES', 'NO') as IS_BIT_30
  FROM sys.col$ c, sys.obj$ o, sys.user$ u
  WHERE o.obj# = c.obj#
    AND o.owner# = u.user#
    AND u.name = '[SCHEMA]' -- 대문자로 계정명 입력
    AND o.name = '[TABLE_NAME]'
    AND c.name = '[COLUMN_NAME]';
    
   
  TABLE_NAME      COLUMN_NAME    PROPERTY     IS_BIT_29   IS_BIT_30
  --------------- -------------- ------------ ----------- -----------
  [TABLE_NAME]    [COLUMN_NAME]  0            NO          NO

• 결론: 데이터 펌프 작업 후 IS_BIT_30은 NO가 되며, 이는 데이터가 물리적으로 꽉 채워진 일반 컬럼 상태로 생성되었음을 입증합니다.

5. 요약 및 시사점

1. **_add_col_optim_enabled**는 대용량 운영 테이블의 스키마 변경 시 가용성을 극대화하는 핵심 파라미터입니다.
2. 하지만 CTAS나 Data Pump를 이용한 데이터 이동 시 최적화 속성(비트 30)은 풀리게 되며, 물리적 저장 공간을 점유하게 됩니다.