Materialized Expression Columns

Oracle 23ai(23.7)에 Materialized Expression Columns 기능이 추가되었습니다. Materialized Expression Column은 물리적으로 값이 저장되는 Virtual Column입니다. 기능이 추가되면서 Virtual Column을 Expression Column으로 명칭을 변경하고 기존의 Virtual Column을 Virtual Expression Column, 추가된 기능을 Materialized Expression Column으로 명명한 것으로 보입니다.

 

아래는 virtual_column_definition 절의 구문입니다. 칼럼 표현식 뒤에 VIRTUAL 또는 MATERIALIZED 키워드를 사용할 수 있습니다.

column [datatype] [ VISIBLE | INVISIBLE ] [GENERATED ALWAYS] AS (column_expression) [ VIRTUAL | MATERIALIZED ]

 

테스트를 위해 아래와 같이 테이블을 생성하겠습니다. c2 칼럼은 Virtual Expression Column, c3 칼럼은 Materialized Expression Column으로 생성됩니다.

-- 1
DROP TABLE t1 PURGE;

CREATE TABLE t1 (
    c1 NUMBER
  , c2 NUMBER AS (c1 + 1)
  , c3 NUMBER AS (c1 + 2) MATERIALIZED
);

 

아래는 *_TAB_COLS 뷰를 조회한 결과입니다. 현재까지 Materialized Expression Column을 구분할 수 있는 칼럼이 추가되지 않은 것으로 보입니다. data_default_vc IS NOT NULL AND virtual_column = 'NO' 조건으로 Materialized Expression Column을 식별할 수 있습니다.

-- 2
SELECT column_name, column_id, data_default_vc, virtual_column, segment_column_id
  FROM user_tab_cols
 WHERE table_name = 'T1';

COLUMN_NAM COLUMN_ID DATA_DEFAULT_VC VIRTUAL_COLUMN SEGMENT_COLUMN_ID
---------- --------- --------------- -------------- -----------------
C1                 1                 NO                             1
C2                 2 "C1"+1          YES
C3                 3 "C1"+2          NO                             2

3 rows selected.

 

아래와 같이 INSERT 문을 수행하겠습니다. Virtual Expression Column와 Materialized Expression Column 모두 데이터를 삽입할 수 없습니다.

-- 3-1
INSERT INTO t1 (c1) VALUES (1);

1 row created.

INSERT INTO t1 (c2) VALUES (2);

ORA-54013: INSERT operation disallowed on virtual columns

INSERT INTO t1 (c3) VALUES (3);

ORA-54013: INSERT operation disallowed on virtual columns

COMMIT;

-- 3-2
SELECT * FROM t1;

C1 C2 C3
-- -- --
 1  2  3

1 row selected.

 

아래는 블록 덤프 결과입니다. c1, c3 칼럼만 값이 저장된 것을 볼 수 있습니다.

block_row_dump:
tab 0, row 0, @0x1f8f
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [ 2]  c1 04
end_of_block_dump

 

아래와 같이 UPDATE 문을 수행하겠습니다. c2, c3 값이 변경된 것을 볼 수 있습니다.

-- 3-1
UPDATE t1 SET c1 = 2;

-- 3-2
SELECT * FROM t1;

C1 C2 C3
-- -- --
 2  3  4

1 row selected.

 

Virtual Expression Column과 Materialized Expression Column 모두 인덱스를 생성할 수 있습니다.

-- 4-1
CREATE INDEX t1_x2 ON t1 (c2);

Index created.

-- 4-2
CREATE INDEX t1_x3 ON t1 (c3);

Index created.