파티션 high_value_clob, high_value_json 칼럼 추가

Oracle 23c에 파티션의 상위 값을 조회할 수 있는 CLOB 타입의 high_value_clob 칼럼과 JSON 타입의 high_value_json 칼럼이 추가되었습니다. 이제 불편한 LONG 타입의 high_value 칼럼을 사용하지 않아도 됩니다.

All data dictionary views holding partitioning-related metadata, e.g. [ALL_TAB_PARTITIONS], have two additional columns representing the high value (boundary) information of partitions and subpartitions in JSON and CLOB format. Providing the high value (boundary) partitioning information in JSON and as CLOB enables you to use this information programmatically. This enables simple and automated processing of this information for schema retrieval or lifecycle management operations.

 

아래 쿼리로 추가된 칼럼을 확인할 수 있습니다.

-- 1
SELECT table_name, column_id, column_name, data_type
  FROM dba_tab_columns
 WHERE owner = 'SYS'
   AND table_name LIKE 'DBA%PART%'
   AND column_name IN ('HIGH_VALUE_CLOB', 'HIGH_VALUE_JSON');

TABLE_NAME            COLUMN_ID COLUMN_NAME     DATA_TYPE
--------------------- --------- --------------- ---------
DBA_IND_PARTITIONS           44 HIGH_VALUE_CLOB CLOB
DBA_IND_PARTITIONS           45 HIGH_VALUE_JSON JSON
DBA_IND_SUBPARTITIONS        42 HIGH_VALUE_CLOB CLOB
DBA_IND_SUBPARTITIONS        43 HIGH_VALUE_JSON JSON
DBA_TAB_PARTITIONS           54 HIGH_VALUE_CLOB CLOB
DBA_TAB_PARTITIONS           55 HIGH_VALUE_JSON JSON
DBA_TAB_SUBPARTITIONS        52 HIGH_VALUE_CLOB CLOB
DBA_TAB_SUBPARTITIONS        53 HIGH_VALUE_JSON JSON

8 행이 선택되었습니다.

 

테스트를 위해 아래와 같이 테이블을 생성하겠습니다.

-- 2
DROP TABLE t1 PURGE;

CREATE TABLE t1 (c1 NUMBER)
PARTITION BY RANGE (c1) (
    PARTITION p1 VALUES LESS THAN (11)
  , PARTITION p2 VALUES LESS THAN (21)
  , PARTITION pm VALUES LESS THAN (MAXVALUE)
);

 

아래와 같이 high_value_clob, high_value_json 칼럼을 조회할 수 있습니다.

-- 3
SELECT partition_name, high_value, high_value_clob, JSON_SERIALIZE (high_value_json) AS high_value_json
  FROM user_tab_partitions
 WHERE table_name = 'T1';

PARTITION_NAME HIGH_VALUE HIGH_VALUE_CLOB HIGH_VALUE_JSON
-------------- ---------- --------------- --------------------
P1             11         11              {"high_value":11}
P2             21         21              {"high_value":21}
PM             MAXVALUE   MAXVALUE        {"is_maxvalue":true}

3 행이 선택되었습니다.