컬럼 추가 시에 디폴트 값 사용하기 (oracle 11g)

DBA가 추가하려는 TAX_CODE 컬럼은 NOT NULL 제약 조건이 적용되어 있어야 합니다.

이미 데이터가 입력된 테이블에 NOT NULL 컬럼을 추가하기 위해 디폴트 값 ‘XX’를 적용해야 합니다.

DBA는 아래와 같은 SQL 구문을 작성 할 것입니다.

하지만 문제가 완전히 해결된 것은 아닙니다.

SALES 테이블은 약 4 억 개의 로우를 포함하는 거대한 테이블입니다.

이 명령을 실행한다면 컬럼이 추가되기는 하겠지만, 모든 ‘XX’ 로우에 값이 업데이트되기 전까지는 테이블에 대한 컨트롤을 회복할 수 없을 것입니다. 4 억 개의 로우를 업데이트하는 작업은 매우 오랜 시간이 걸릴 뿐 아니라, 언두 세그먼트를 가득 채우고, 대량의 리두 데이터가 쌓이고, 상당한 수준의 성능 오버헤드가 발생하게 될 것입니다. 결국 쿼리는 시스템을 중단시킬 수 있을 때까지 작업을 유보하기로 합니다.

Oracle Database 11g 라면 ?

대안이 있습니다.

11g 에서는 위 명령을 실행하더라도 테이블의 모든 레코드에 대한 업데이트가 실행되지 않습니다.

새로 추가되는 레코드의 경우 컬럼의 디폴트 값이 자동으로 입력되므로 문제될 것이 없습니다.

하지만 사용자가 기존의 레코드에서 이 컬럼을 조회하면 NULL이 반환되지 않을까요?

그렇지 않습니다. 사용자가 기존 레코드의 컬럼을 선택하면, 오라클은 데이터 딕셔너리에서 디폴트 값을 조회한 후 이 값을 사용자에게 반환합니다.

디폴트 값을 갖는 NOT NULL 컬럼을 새로 추가하고도 리두/언두 데이터 생성으로 인한 성능 문제를 겪지 않으니 일거양득인 셈입니다.

따라서 실제로 not null 를 갖는 컬럼을 추가한 이후의 data는 default 값 ‘XX’ 가 그대로 적용되어 입력 되겠지만, 이전의 로우에는 실제로 default 값 ‘XX’ 가 update 되는것이 아니라 딕셔너리를 통해서 보여 주게 되는 것 입니다.

Oracle 11g 부터 컬럼 추가 기능 향상

테이블에 column 추가시(Default Value가 지정되고, NOT NULL 제한사항이 있는 경우) Default Value가 Table에 직접 insert되지 않고 dictionary에 metadata로만 가지고 있는 기능이다.

이전 버전 에서는 Row수가 많은 테이블에 Not Null이면서 Default Value가 있는 Column을 추가하면, Default Value가 모든 row 에 대하여 insert되었기 때문에 상당한 시간이 걸렸었다.

그리고 Add Column 이 수행되는 동안 해당 Dependent SQL 이 “library cache pin”, “library cache lock”등의 waiting현상이 발생되었다.

오라클 데이터베이스 11g에서는 테이블에 Column이 추가될 경우 Default 값이 지정되면 Dictionary에만 Default값이 저장되어 Add Column작업이 1초 이내에 끝난다.

Default 값은 해당 row가 insert/update되는 순간에 입력이 된다. 따라서 오랜 DDL수행으로 인한 “library cache pin”, “library cache lock” 현상이 해소될 것으로 기대된다.

컬럼 추가 기능 향상의 장점

• Add Column 작업이 매우 짧은 시간 내에 끝난다.
• 실시간 Default Value의 Insert로 인한 부하(insert작업과 Redo 발생)이 없어졌다.
• Add Column 수행 중의 SQL Waiting 현상이 해소된다.