Oracle 가상 열(Virtual Column)로 조합 컬럼 만들기

화면 여기저기서 가게명(대표자명) 형태의 문자열이 필요했다. 쿼리마다 || 로 이어 붙이는 대신, 테이블에 가상 열(virtual column) 을 하나 정의해 두면 일반 컬럼처럼 조회하고 인덱스도 걸 수 있다. 가상 열은 Oracle 11g 부터 있는 기능이고 19c·23ai 에서도 그대로 쓴다.

1. 테이블

CREATE TABLE SHOP (
    SHOP_CD   VARCHAR2(10)  NOT NULL,
    SHOP_NM   VARCHAR2(500) NOT NULL,
    OWNER_NM  VARCHAR2(500),
    CONSTRAINT SHOP_PK PRIMARY KEY (SHOP_CD)
);

COMMENT ON TABLE  SHOP          IS '가게';
COMMENT ON COLUMN SHOP.SHOP_CD  IS '가게코드';
COMMENT ON COLUMN SHOP.SHOP_NM  IS '가게명';
COMMENT ON COLUMN SHOP.OWNER_NM IS '대표자명';

2. 가상 열 추가

-- 가게명(대표자명). 대표자명이 없으면 가게명만
ALTER TABLE SHOP ADD (
    SHOP_LABEL VARCHAR2(1010)
        GENERATED ALWAYS AS (
            SHOP_NM || CASE WHEN OWNER_NM IS NOT NULL THEN '(' || OWNER_NM || ')' END
        ) VIRTUAL
);

GENERATED ALWAYS 와 VIRTUAL 은 생략할 수 있지만, 읽는 사람을 위해 써 두는 편이 낫다.
자료형도 생략하면 Oracle 이 식에서 정한다. 적는다면 식 결과가 들어갈 만큼만 잡는다(500 + 2 + 500 = 1010 자).
처음에는 SHOP_NM || '(' || OWNER_NM || ')' 로 썼는데, 이러면 대표자명이 NULL 인 행이 가게명() 이 된다. Oracle 에서 || 는 NULL 을 빈 문자열처럼 이어 붙이기 때문이다. 위처럼 CASE 로 괄호까지 함께 빼야 한다.

3. 확인

INSERT INTO SHOP (SHOP_CD, SHOP_NM, OWNER_NM) VALUES ('1', '행복상회', '홍길동');
INSERT INTO SHOP (SHOP_CD, SHOP_NM, OWNER_NM) VALUES ('2', '바른문구', NULL);

SELECT SHOP_CD, SHOP_NM, OWNER_NM, SHOP_LABEL FROM SHOP;

SHOP_CD	SHOP_NM	OWNER_NM	SHOP_LABEL
1	행복상회	홍길동	행복상회(홍길동)
2	바른문구	(null)	바른문구

SHOP_NM 이나 OWNER_NM 을 UPDATE 하면 SHOP_LABEL 도 바로 바뀐 값으로 보인다.

알아 둘 것

값은 저장되지 않는다. 원문에서는 “INSERT·UPDATE 때 자동 반영된다”고 썼는데, 정확히는 테이블에 값을 저장하지 않고 조회할 때마다 식을 계산한다. 그래서 디스크를 차지하지 않고, 원본 컬럼과 어긋날 일도 없다.
가상 열에는 직접 값을 넣을 수 없다. INSERT·UPDATE 대상에 가상 열을 적으면 ORA-54013 오류가 난다. INSERT 는 컬럼 목록을 적어 쓰는 습관을 들이면 헷갈리지 않는다.
식에 쓸 수 있는 것은 같은 테이블의 컬럼, 상수, 결정적(deterministic) 함수다. 다른 테이블이나 다른 가상 열은 참조할 수 없고, SYSDATE 처럼 부를 때마다 값이 바뀌는 것도 안 된다. 사용자 정의 함수를 쓰려면 DETERMINISTIC 으로 선언해야 한다(그 함수를 나중에 바꾸면 가상 열·인덱스 값과 어긋날 수 있으니 조심).

4. 인덱스

가상 열에도 인덱스를 걸 수 있다. 결과적으로 그 식에 대한 함수 기반 인덱스와 같다.

CREATE INDEX SHOP_LABEL_IX ON SHOP (SHOP_LABEL);

SELECT * FROM SHOP WHERE SHOP_LABEL = '행복상회(홍길동)';

인덱스에는 계산된 값이 저장되므로, 원본 컬럼을 바꿀 때 인덱스 갱신 비용이 붙는다. 검색 조건에 실제로 쓰는 가상 열에만 건다.

다시 쓰며 바로잡은 점

“INSERT·UPDATE 때 자동 반영된다”는 설명을 “저장하지 않고 조회 때 계산한다”로 고쳤다.
대표자명이 NULL 이면 가게명() 이 되던 식을 CASE 로 고쳤다.
가상 열에 값을 넣을 수 없다는 점(ORA-54013)과 식의 제약을 덧붙였다.
예제 테이블·데이터는 일반적인 예시로 바꿨다.