JSON Relational Duality #1 - 단순 Duality 뷰
JSON Relational Duality #2 - 1:M 조인 Duality 뷰
JSON Relational Duality #3 - M:1 조인 Duality 뷰
Oracle 23ai에 JSON Relational Duality 기능이 추가되었습니다. 이 기능을 사용하면 JSON-Relational Duality 뷰(이후 Duality 뷰)를 통해 RDB 모델을 JSON 문서로 조회하거나 변경할 수 있습니다. 이 글에서는 조인이 없는 단순 뷰의 동작에 대해 살펴보겠습니다. JSON Relational Duality에 대한 상세한 내용은 JSON-Relational Duality Developer's Guide를 참고하세요.
준비
테스트 버전은 아래와 같습니다.
-- 1 SELECT version_full FROM product_component_version; VERSION_FULL ------------ 23.4.0.24.05 1 row selected.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다.
-- 2 DROP TABLE t1 PURGE; CREATE TABLE t1 (c1 NUMBER, c2 VARCHAR2(1), CONSTRAINT t1_pk PRIMARY KEY (c1)); INSERT INTO t1 VALUES (1, 'A'); COMMIT;
아래 구문으로 Duality 뷰를 생성할 수 있습니다. Duality 뷰의 베이스 테이블은 반드시 PK가 존재해야 하며, 루트 테이블의 PK는 _id 필드로 선언되어야 합니다. 구문에 대한 상세한 내용은 CREATE JSON RELATIONAL DUALITY VIEW 문을 참고하세요.
-- 3 CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW dv_t1 AS SELECT JSON {'_id':c1, 'c2':c2} FROM t1 WITH INSERT UPDATE DELETE;
JSON Relational Duality 기능과 관련하여 아래와 같이 *_JSON_DUALITY_VIEWS, *_JSON_DUALITY_VIEW_TABS, *_JSON_DUALITY_VIEW_TAB_COLS, *_JSON_DUALITY_VIEW_LINKS 뷰가 추가되었습니다.
-- 4-1 SELECT json_column_name, root_table_name, root_table_owner, allow_insert, allow_update, allow_delete, read_only FROM USER_JSON_DUALITY_VIEWS WHERE view_name = 'DV_T1'; JSON_COLUMN_NAME ROOT_TABLE_NAME ROOT_TABLE_OWNER ALLOW_INSERT ALLOW_UPDATE ALLOW_DELETE READ_ONLY ---------------- --------------- ---------------- ------------ ------------ ------------ --------- DATA T1 TUNA 1 1 1 0 1 row selected. -- 4-2 SELECT json_schema FROM USER_JSON_DUALITY_VIEWS WHERE view_name = 'DV_T1'; JSON_SCHEMA ------------------------------------------------------------------------------------------------------------------------------------ {"title":"DV_T1","dbObject":"TUNA.DV_T1","dbObjectType":"dualityView","dbObjectProperties":["insert","update","delete","check"], ... 1 row selected. -- 4-3 SELECT table_owner, table_name, where_clause, allow_insert, allow_update, allow_delete, read_only FROM USER_JSON_DUALITY_VIEW_TABS WHERE view_name = 'DV_T1'; TABLE_OWNER TABLE_NAME WHERE_CLAUSE ALLOW_INSERT ALLOW_UPDATE ALLOW_DELETE READ_ONLY ----------- ---------- ------------ ------------ ------------ ------------ --------- TUNA T1 1 1 1 0 1 row selected. -- 4-4 SELECT table_owner, table_name, column_name, data_type, json_key_name FROM USER_JSON_DUALITY_VIEW_TAB_COLS WHERE view_name = 'DV_T1'; TABLE_OWNER TABLE_NAME COLUMN_NAME DATA_TYPE JSON_KEY_NAME ----------- ---------- ----------- --------- ------------- TUNA T1 C1 NUMBER _id TUNA T1 C2 VARCHAR2 c2 2 rows selected. -- 4-5 SELECT * FROM USER_JSON_DUALITY_VIEW_LINKS WHERE view_name = 'DV_T1'; no rows selected
SELECT 문
아래는 SELECT 문으로 dv_t1 뷰를 조회한 결과와 실행 계획입니다. 결과를 JSON_SERIALIZE 함수로 포맷팅했습니다.
-- 5 SELECT JSON_SERIALIZE (data PRETTY) AS data FROM dv_t1; DATA ------------------------------------------------ { "_id" : 1, "_metadata" : { "etag" : "B012058CE02FCFF3BC3A92BCCCC672D9", "asof" : "0000000001041F31" }, "c2" : "A" } 1 row selected. -------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | Buffers | -------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1 | 7 | | 1 | TABLE ACCESS FULL| T1 | 1 | 1 | 7 | -------------------------------------------------------------- Outline Data ------------- FORCE_XML_QUERY_REWRITE FORCE_JSON_TABLE_TRANSFORM XML_DML_RWT_STMT XMLINDEX_REWRITE XMLINDEX_REWRITE_IN_SELECT NO_COST_XML_QUERY_REWRITE
변환 후의 쿼리를 확인하기 위해 아래와 같이 함수를 생성하겠습니다. 원인을 알 수 없지만 Duality 뷰를 조회하는 SELECT 문은 10053 트레이스가 동작하지 않는 것으로 보입니다.
-- 6 CREATE OR REPLACE FUNCTION fnc_expand_sql_text ( i_input_sql_text IN CLOB ) RETURN CLOB IS v_outout_sql_text CLOB; BEGIN DBMS_UTILITY.EXPAND_SQL_TEXT (input_sql_text => i_input_sql_text, output_sql_text => v_outout_sql_text); RETURN v_outout_sql_text; END; /
아래는 fnc_expand_sql_text 함수로 변환 후의 5번 쿼리를 조회한 결과입니다. resid 값은 루트 테이블의 PK에 대한 고유 값, etag 값은 data 칼럼의 현재 로우에 대한 고유 값입니다. (SYS_ROW_ETAG 함수 글 참고)
-- 7-1 SELECT fnc_expand_sql_text ('SELECT * FROM DV_T1') AS sql_text FROM DUAL; -- 7-2 SELECT "A1"."DATA" "DATA" FROM (SELECT JSON_OBJECT ( '_id' VALUE "A2"."C1" , 'c2' VALUE "A2"."C2" NULL ON NULL EMPTY ON NO ROWS RETURNING JSON FORMAT JSON /*+ QJSNMD_NOPD3_IN_BOV */ ETAG ("C2" , "C1") TOPLEVEL) "DATA" , SYS_MAKE_OID_FROM_PK ("A2"."C1") "RESID" , SYS_MK_FULL_ETAG (SYS_OBJ_ETAG (SYS_ROW_ETAG ("A2"."C1", "A2"."C2")), 0) "ETAG" FROM "TUNA"."T1" "A2") "A1";
아래와 같이 Dot 노테이션을 사용할 수도 있습니다.
-- 8 SELECT a.data.c2 AS c2 FROM dv_t1 a WHERE a.data."_id" = 1; C2 --- "A" 1 row selected. ------------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | Buffers | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1 | 2 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 2 | |* 2 | INDEX UNIQUE SCAN | T1_PK | 1 | 1 | 1 | ------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"=1)
아래는 fnc_expand_sql_text 함수로 변환 후의 8번 쿼리를 조회한 결과입니다.
-- 9-1 SELECT fnc_expand_sql_text ('SELECT A.DATA.C2 AS C2 FROM DV_T1 A WHERE A.DATA."_id" = 1') AS sql_text FROM DUAL; -- 9-2 SELECT JSON_QUERY ("A1"."DATA" FORMAT OSON, '$.c2' RETURNING JSON ASIS WITHOUTARRAY WRAPPER NULL ON ERROR TYPE (LAX)) "C2" FROM (SELECT JSON_OBJECT ( '_id' VALUE "A2"."C1" , 'c2' VALUE "A2"."C2" NULL ON NULL EMPTY ON NO ROWS RETURNING JSON FORMAT JSON /*+ QJSNMD_NOPD3_IN_BOV */ ETAG ("C2" , "C1") TOPLEVEL) "DATA" , SYS_MAKE_OID_FROM_PK ("A2"."C1") "RESID" , SYS_MK_FULL_ETAG (SYS_OBJ_ETAG (SYS_ROW_ETAG ("A2"."C1", "A2"."C2")), 0) "ETAG" FROM "TUNA"."T1" "A2") "A1" WHERE JSON_QUERY ("A1"."DATA" FORMAT OSON, '$."_id"' RETURNING JSON ASIS WITHOUT ARRAY WRAPPER NULL ON ERROR TYPE (LAX)) = 1
INSERT 문
아래는 dv_t1 뷰에 대한 INSERT 문의 실행 계획입니다. 테스트를 반복하기 위해 DML 문을 수행한 후 롤백을 수행해야 합니다.
-- 10 INSERT INTO dv_t1 VALUES ('{"_id":2,"c2":"B"}'); 1 row created. --------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | Buffers | --------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 1 | 0 | 7 | | 1 | LOAD TABLE CONVENTIONAL | T1 | 1 | 0 | 7 | ---------------------------------------------------------------------
10046 트레이스를 살펴보면 아래의 Recursive SQL이 수행되는 것을 확인할 수 있습니다. Optimistic Locking을 위해 RETURNING 절로 SYS_ROW_ETAG 함수의 수행 결과를 반환하는 것으로 보입니다.
-- 11 INSERT INTO "TUNA"."T1" ("C1", "C2") VALUES (:1, :2) RETURNING SYS_ROW_ETAG ("C1", "C2") INTO :3;
UPDATE 문
아래는 dv_t1 뷰에 대한 UPDATE 문의 실행 계획입니다. 이유를 알 수 없지만 실행 계획 2번에 INDEX UNIQUE SCAN 오퍼레이션이 표시됩니다.
-- 12 UPDATE dv_t1 a SET a.data = '{"_id":1,"c2":"B"}' WHERE a.data."_id" = 1; 1 row updated. -------------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | Buffers | -------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 1 | 0 | 8 | | 1 | UPDATE | T1 | 1 | 0 | 8 | |* 2 | INDEX UNIQUE SCAN | T1_PK | 1 | 1 | 1 | --> ? | 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 2 | |* 4 | INDEX UNIQUE SCAN | T1_PK | 1 | 1 | 1 | -------------------------------------------------------------------------- Outline Data ------------- FORCE_XML_QUERY_REWRITE FORCE_JSON_TABLE_TRANSFORM XML_DML_RWT_STMT XMLINDEX_REWRITE XMLINDEX_REWRITE_IN_SELECT NO_COST_XML_QUERY_REWRITE JSON_QRYOVERGEN_REWRITE(@"SEL$55B0D60B") Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"=1) 4 - access("T1"."C1"=:B1)
10046 트레이스를 살펴보면 아래의 Recursive SQL이 수행되는 것을 확인할 수 있습니다.
-- 13 UPDATE "TUNA"."T1" SET "C2" = :1 WHERE "C1" = :2 AND SYS_ROW_ETAG ("C1", "C2") = RAWTOHEX (:3) RETURNING SYS_ROW_ETAG ("C1", "C2") INTO :4;
아래 UPDATE 문은 PK 값을 다른 값이나 NULL로 변경하므로 에러가 발생합니다.
-- 14-1 UPDATE dv_t1 a SET a.data = '{"_id":2,"c2":"B"}' WHERE a.data."_id" = 1; ORA-42603: Cannot update JSON Relational Duality View 'DV_T1' : The Primary Key column(s) of the root table 'T1' cannot be updated, omitted, or set to NULL. -- 14-2 UPDATE dv_t1 a SET a.data = '{"c2":"B"}' WHERE a.data."_id" = 1; ORA-42603: Cannot update JSON Relational Duality View 'DV_T1' : The Primary Key column(s) of the root table 'T1' cannot be updated, omitted, or set to NULL.
DELETE 문
아래는 dv_t1 뷰에 대한 DELETE 문의 실행 계획입니다.
-- 15 DELETE FROM dv_t1 a WHERE a.data."_id" = 1; 1 row deleted. ---------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | Buffers | ---------------------------------------------------------------- | 0 | DELETE STATEMENT | | 1 | 0 | 11 | | 1 | DELETE | T1 | 1 | 0 | 11 | |* 2 | INDEX UNIQUE SCAN| T1_PK | 1 | 1 | 1 | ---------------------------------------------------------------- Outline Data ------------- FORCE_XML_QUERY_REWRITE FORCE_JSON_TABLE_TRANSFORM XML_DML_RWT_STMT XMLINDEX_REWRITE XMLINDEX_REWRITE_IN_SELECT NO_COST_XML_QUERY_REWRITE JSON_QRYOVERGEN_REWRITE(@"SEL$F032A239") Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"=1)
10046 트레이스를 살펴보면 아래의 Recursive SQL이 수행되는 것을 확인할 수 있습니다.
-- 16 DELETE FROM "TUNA"."T1" WHERE "C1" = :1 AND SYS_ROW_ETAG ("C1", "C2") = RAWTOHEX (:2);