Precheckable Constraints using JSON SCHEMA

Oracle 23c에 Precheckable Constraints using JSON SCHEMA 기능(이후 Precheck 제약 조건)이 추가되었습니다. Precheck 제약 조건의 JSON 값을 사용하면 애플리케이션에서 값을 사전 검증할 수 있습니다.

Supported check constraints can be checked outside the database. For this, you mark the check constraint as PRECHECK. For example, you can create a JSON Schema document from a table or column with precheckable check constraint. This means that data could be checked for validity outside of the database using an external JSON Schema validator.

 

테스트를 위해 아래와 같이 테이블을 생성하겠습니다. t1_c1 제약 조건을 DISABLE PRECHECK로 생성합니다.

-- 1
DROP TABLE t1 PURGE;

CREATE TABLE t1 (
    c1 NUMBER
  , c2 VARCHAR2(1)
  , CONSTRAINT t1_c1 CHECK (C2 IN ('A', 'B')) DISABLE PRECHECK
);

 

아래는 *_CONSTRAINTS 뷰를 조회한 결과입니다. *_CONSTRAINTS 뷰에 precheck 칼럼이 추가되었습니다.

-- 2
SELECT constraint_name, search_condition_vc, status, deferrable, deferred, validated, precheck
  FROM user_constraints
 WHERE constraint_name = 'T1_C1';

CONSTRAINT_NAME SEARCH_CONDITION_VC STATUS   DEFERRABLE     DEFERRED  VALIDATED     PRECHECK
--------------- ------------------- -------- -------------- --------- ------------- --------
T1_C1           C2 IN ('A', 'B')    DISABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED PRECHECK

1개의 행이 선택되었습니다.

 

DBMS_JSON_SCHEMA.DESCRIBE 함수로 애플리케이션에서 사용할 수 있는 JSON 값을 조회할 수 있습니다. 참고로 DBMS_JSON_SCHEMA 패키지는 23c Free까지 문서화되어 있지 않습니다.

FUNCTION describe (
    object_name IN VARCHAR2
  , owner_name  IN VARCHAR2 DEFAULT NULL
  , column_name IN VARCHAR2 DEFAULT NULL
)
    RETURN JSON;
    PRAGMA SUPPLEMENTAL_LOG_DATA (describe, READ_ONLY);

 

아래 쿼리는 t1 테이블의 c2 칼럼에 대한 Precheck 제약 조건의 JSON 값을 조회합니다.

-- 3
SELECT JSON_SERIALIZE (DBMS_JSON_SCHEMA.DESCRIBE ('T1', 'TUNA', 'C2') PRETTY) AS json_schema
  FROM DUAL;

JSON_SCHEMA
--------------------------
{
  "dbColumn" : "C2",
  "extendedType" :
  [
    "null",
    "string"
  ],
  "maxLength" : 1,
  "allOf" :
  [
    {
      "enum" :
      [
        "A",
        "B"
      ]
    }
  ]
}

1개의 행이 선택되었습니다.

 

Precheck 제약 조건은 옵티마이저의 동작에 영향을 미치지 않는 것으로 보입니다.

-- 4-1
SELECT * FROM t1 WHERE c2 = 'C';

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| T1   |
----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C2"='C')

-- 4-2
ALTER TABLE t1 MODIFY CONSTRAINT t1_c1 NOPRECHECK;

SELECT * FROM t1 WHERE c2 = 'C';

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| T1   |
----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C2"='C')

-- 4-3
ALTER TABLE t1 MODIFY CONSTRAINT t1_c1 ENABLE;

SELECT * FROM t1 WHERE c2 = 'C';

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  FILTER            |      |
|*  2 |   TABLE ACCESS FULL| T1   |
-----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL)
   2 - filter("C2"='C')