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);