About Oracle Virtual Column
앞으로 3개의 아티클을 통하여 Oracle ORDBMS(이하 오라클)에서 제공하는 특수한 컬럼들을 살펴보겠습니다. 오라클 11g에서 소개된 Virtual Column과 최신 버전인 오라클 12c에서 소개된 Identity Column과 Invisible Column이 주인공입니다. 이 시간에는 첫 주제로 Virtual Column에 대해 알아보겠습니다.
Virtual Column
Virtual Column은 Oracle 11g에서 새롭게 도입된 기능입니다(오라클 11g가 2007년에 출시되었으니 꽤 오래 전에 소개된 기능입니다). Virtual Column은 테이블에서 정의한 하나 이상의 컬럼값을 활용하는 계산식의 결과 또는 함수의 리턴값으로 활용할 수 있습니다. 실제 활용하는 사례는 많지 않은 것 같습니다만, 적합한 곳에 사용하면 매우 유용하게 활용할 수 있는 기능입니다.
Syntax
Virtual Column은 명칭에서 알 수 있듯이 컬럼과 유사한 특성을 지니며, DDL2과 DML 절에서 활용할 수 있습니다. Virtual Column의 기본적인 구문은 다음과 같습니다.
column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]
column_name - 반드시 입력해야 하며, 컬럼 이름을 의미합니다. 테이블 내에서 유일한 이름이어야 합니다.
datatype - 구문에서 볼 수 있듯이 생략이 가능하지만 명시적으로 지정하는 것이 좋습니다. 데이터 형식을 의미하며, 생략하면 표현식(expression)의 결과를 기반으로 결정됩니다.
expression - Virtual Column의 값을 결정하는 표현식을 기술합니다. 자세한 내용은 다음 링크에서 확인할 수 있습니다.
GENERATED ALWAYS & VIRTUAL 구문은 생략해도 가능하지만, 명시적으로 지정하는 것이 좋습니다. 사용자에게 Virtual Column을 명시적으로 알려주는 역할을 할 수 있기 때문입니다.
Virtual Column Features
이 절에서는 Virtual Column의 특징에 대해 알아보겠습니다. Virtual Column은 일반 컬럼과 유사하지만 다음과 같은 고유한 특징 및 제약사항을 지닙니다.
Virtual Column은 마치 View와 같이 데이터베이스 내에서 메타 데이터로 저장되므로 물리적인 공간을 차지하지 않습니다. 컬럼을 생성할 때 표현식(expression)은 일반 연산뿐만 아니라 함수 등을 활용할 수 있으며, 조회하는 시점에 결괏값이 결정됩니다. 이때, 표현식을 수행한 후 반환되는 값은 단일 값(Scalar)이어야 합니다. 단, 함수를 이용하는 경우에는 함수의 특성으로 말미암은 추가적인 제약사항이 있습니다. 자세한 내용은 다음 링크를 참조하시기 바랍니다.
Virtual Column은 인덱스(Index)를 생성할 수 있습니다. 단, 일반적인 B*Tree 구조가 아니며, 함수 기반 인덱스(Function based Index: FBI)로 생성됩니다. 이뿐만 아니라, VIrtual Column의 통계 정보를 수집할 수도 있습니다.
Virtual Column은 다른 테이블의 컬럼을 참조할 수 없으며, 오직 같은 테이블에 정의된 컬럼만 참조할 수 있습니다. Virtual Column은 WHERE 절에서 활용할 수 있습니다만, 당연하게도 Virtual Column의 값을 직접 조작할 수는 없습니다.
Virtual Column의 특징에 대한 보다 상세한 내용은 다음 링크를 참조하세요.
Example
이 절에서는 Virtual Column을 생성하고, 활용하는 간단한 예제를 살펴보겠습니다.
우리는 영화관을 운영합니다. 영화관을 방문해주는 고객 서비스를 제공하기 위해 포인트 제도를 운영하고 있으며, 이전 년도의 누적 포인트를 활용하여 고객 등급을 구분합니다. 누적 포인트가 15,000점 미만이면 일반 등급이고, 15,000점 이상이면 VIP 등급이며, 20,000점 이상이면 VVIP 등급입니다.
이 예제에서 요구하는 고객 등급을 표현하는 방법은 많습니다만, Virtual Column을 활용하여 이 요구사항을 해결해보겠습니다.
Sample Data
제공되는 샘플 데이터는 다음과 같습니다.
NAME POINT
------------- --------
홍길동 12000
강감찬 8000
김홍도 22000
황진이 25000
이순신 15000
최종 결과는 다음과 같습니다.
NAME POINT POSITION
------------- ---------- -------------
홍길동 12000 NORMAL
강감찬 8000 NORMAL
김홍도 22000 VVIP
황진이 25000 VVIP
이순신 15000 VIP
Source Code
CREATE TABLE SAMPLE_THEATER (
NAME NVARCHAR2(3)
, POINT NUMBER(6)
);
INSERT INTO SAMPLE_THEATER
SELECT '홍길동', 12000 FROM DUAL UNION ALL
SELECT '강감찬', 8000 FROM DUAL UNION ALL
SELECT '김홍도', 22000 FROM DUAL UNION ALL
SELECT '황진이', 25000 FROM DUAL UNION ALL
SELECT '이순신', 15000 FROM DUAL;
COMMIT;
Adding Virtual Column
이전 Syntax 절에서 살펴본 구문을 활용하여 POSITION 컬럼을 Virtual Column으로 추가하겠습니다. 이때, 등급별로 다른 값을 가져야 하므로 조건식이 필요합니다. 이 예제에서는 오라클에서 주로 활용하는 DECODE 구문이 아닌 CASE 구문으로 설명하겠습니다.
ALTER TABLE SAMPLE_THEATER ADD (
POSITION NVARCHAR2(6) GENERATED ALWAYS AS (
CASE WHEN POINT < 15000 THEN 'NORMAL'
WHEN POINT < 20000 THEN 'VIP'
ELSE 'VVIP'
END
) VIRTUAL
);
Virtual Column을 이용하여 간단하게 예제의 요구사항을 해결할 수 있습니다. 이뿐만 아니라, 새로운 데이터를 삽입하거나 기존 데이터를 갱신해도 조회 시점의 값에 따라 POSITION 정보가 알맞게 반영됩니다.
Quiz
간단한 퀴즈를 통해 Virtual Column의 사용법을 익혀보겠습니다. 새로 추가된 요구사항은 다음과 같습니다.
고객에게 우수회원으로 승급하기 위해 필요한 포인트를 제공하고 싶습니다. 예를 들어, 현재 누적 포인트가 12,000점이라면 VIP로 승급하기 위한 포인트인 3,000 포인트가 필요하다고 알려주고 싶습니다. 또한, 현재 누적 포인트가 17,000점이라면 VVIP로 승급하기 위한 포인트인 3,000 포인트가 필요하다고 알려줘야 합니다.
다음 모범 답안과 같이 아주 간단하게 추가 요구사항을 해결할 수 있습니다.
ALTER TABLE SAMPLE_THEATER ADD (
NEEDPOINT NUMBER(5) GENERATED ALWAYS AS (
CASE WHEN POINT < 15000 THEN 15000-POINT
WHEN POINT < 20000 THEN 20000-POINT
ELSE 0
END
) VIRTUA L
);
Data Dictionary
Data Dictionary를 조회하면 특정 테이블의 컬럼 중 어떤 컬럼이 Virtual Column인지 확인할 수 있습니다.
Data Dictionary를 조회하는 방법은 다음과 같습니다.
COLUMN COLUMN_NAME FORMAT A20
COLUMN DATA_TYPE FORMAT A20
COLUMN VIRTUAL_COLUMN FORMAT A20
SELECT COLUMN_NAME, DATA_TYPE, VIRTUAL_COLUMN
FROM USER_TAB_COLS
WHERE TABLE_NAME = 'SAMPLE_THEATER';
Conclusion
지금까지 오라클에서 제공하는 특수한 컬럼인 Virtual Column에 대해 간단히 알아봤습니다. 일반적으로 많이 활용하는 기능은 아니지만, 특수한 상황에서 유용하게 활용할 수 있는 매력적인 기능입니다. Virtual Column을 정확하게 이해하고 사용하려면 이 글에서 소개한 내용 이외에 더 많은 학습이 필요합니다. 관련 내용은 Oracle Document를 참조하시면 됩니다.
다음 시간에는 오라클 12c에서 새롭게 소개된 Identity Column에 대해 살펴보겠습니다.
References
1. Oracle, Oracle Database Online Document 11g Release 1
2. Oracle-Base, Virtual Columns in Oracle Database 11g Release 1 [관련링크]
