JSON Relational Duality #1 - 단순 Duality 뷰
JSON Relational Duality #2 - 1:M 조인 Duality 뷰
JSON Relational Duality #3 - M:1 조인 Duality 뷰
지난 글에 이어 이번 글에서는 1:M 조인 Duality 뷰의 동작에 대해 살펴보겠습니다.
준비
테스트 버전은 아래와 같습니다.
-- 1
SELECT version_full FROM product_component_version;
VERSION_FULL
------------
23.4.0.24.05
1 row selected.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다.
-- 2
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE TABLE t1 (c1 NUMBER, c2 VARCHAR2(1), CONSTRAINT t1_pk PRIMARY KEY (c1));
CREATE TABLE t2 (c1 NUMBER, c2 NUMBER, c3 VARCHAR2(1), CONSTRAINT t2_pk PRIMARY KEY (c1, c2));
INSERT INTO t1 VALUES (1, 'A');
INSERT INTO t2 VALUES (1, 1, 'A');
INSERT INTO t2 VALUES (1, 2, 'B');
COMMIT;
아래 구문으로 1:M 조인 Duality 뷰를 생성할 수 있습니다.
-- 3
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW dv_t1_t2 AS
SELECT JSON {'_id':{'t1_c1':a.c1}
, 't1_c2':a.c2
, 't2':[SELECT JSON {'t2_c1':x.c1, 't2_c2':x.c2, 't2_c3':x.c3}
FROM t2 x WITH INSERT UPDATE DELETE
WHERE x.c1 = a.c1]}
FROM t1 a WITH INSERT UPDATE DELETE;
아래는 JSON Relational Duality 관련 뷰를 조회한 결과입니다. t2 테이블의 relationship과 join_type이 nested로 표시됩니다.
-- 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_T2';
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 table_owner, table_name, root_table, table_number, parent_table_number, relationship
FROM USER_JSON_DUALITY_VIEW_TABS
WHERE view_name = 'DV_T1_T2';
TABLE_OWNER TABLE_NAME ROOT_TABLE TABLE_NUMBER PARENT_TABLE_NUMBER RELATIONSHIP
----------- ---------- ---------- ------------ ------------------- ------------
TUNA T1 1 0
TUNA T2 0 1 0 nested
2 rows selected.
-- 4-3
SELECT table_owner, table_name, column_name, data_type, json_key_name, primary_key_pos
FROM USER_JSON_DUALITY_VIEW_TAB_COLS
WHERE view_name = 'DV_T1_T2';
TABLE_OWNER TABLE_NAME COLUMN_NAME DATA_TYPE JSON_KEY_NAME PRIMARY_KEY_POS
----------- ---------- ----------- --------- ------------- ---------------
TUNA T1 C1 NUMBER t1_c1 1
TUNA T1 C2 VARCHAR2 t1_c2
TUNA T2 C1 NUMBER t2_c1 1
TUNA T2 C2 NUMBER t2_c2 2
TUNA T2 C3 VARCHAR2 t2_c3
5 rows selected.
-- 4-4
SELECT parent_table_owner, parent_table_name, child_table_owner, child_table_name, from_column, to_column, join_type, key_name
FROM USER_JSON_DUALITY_VIEW_LINKS
WHERE view_name = 'DV_T1_T2';
PARENT_TABLE_OWNER PARENT_TABLE_NAME CHILD_TABLE_OWNER CHILD_TABLE_NAME FROM_COLUMN TO_COLUMN JOIN_TYPE KEY_NAME
------------------ ----------------- ----------------- ---------------- ----------- --------- --------- --------
TUNA T1 TUNA T2 C1 C1 nested t2
1 row selected.
SELECT 문
아래는 SELECT 문으로 dv_t1_t2 뷰를 조회한 결과와 실행 계획입니다. 결과를 JSON_SERIALIZE 함수로 포맷팅했습니다.
-- 5
SELECT JSON_SERIALIZE (data PRETTY) AS data
FROM dv_t1_t2;
DATA
------------------------------------------------
{
"_id" :
{
"t1_c1" : 1
},
"_metadata" :
{
"etag" : "686930B22D522DC2A68440478C4A3D28",
"asof" : "0000000001041222"
},
"t1_c2" : "A",
"t2" :
[
{
"t2_c1" : 1,
"t2_c2" : 1,
"t2_c3" : "A"
},
{
"t2_c1" : 1,
"t2_c2" : 2,
"t2_c3" : "B"
}
]
}
1 row selected.
----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 7 |
| 1 | SORT GROUP BY | | 1 | 1 | 2 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 2 | 2 |
|* 3 | INDEX RANGE SCAN | T2_PK | 1 | 2 | 1 |
| 4 | 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
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("X"."C1"=:B1)
아래는 fnc_expand_sql_text 함수로 변환 후의 5번 쿼리를 조회한 결과입니다.
-- 6-1
SELECT fnc_expand_sql_text ('SELECT * FROM DV_T1_T2') AS sql_text
FROM DUAL;
-- 6-2
SELECT "A1"."DATA" "DATA"
FROM (SELECT JSON_OBJECT(/*+ NO_GEN_WITH_ROWSET */
'_id' VALUE JSON_OBJECT ('t1_c1' VALUE "A2"."C1"
NULL ON NULL EMPTY ON NO ROWS RETURNING JSON FORMAT JSON
/*+ QJSNMD_NOPD3_NEST */
/*+ QJSNMD_NOPD3_IN_BOV */)
, 't1_c2' VALUE "A2"."C2"
, 't2' VALUE (SELECT JSON_ARRAYAGG (
JSON_OBJECT ('t2_c1' VALUE "A3"."C1"
, 't2_c2' VALUE "A3"."C2"
, 't2_c3' VALUE "A3"."C3"
NULL ON NULL EMPTY ON NO ROWS RETURNING JSON FORMAT JSON
/*+ QJSNMD_NOPD3_IN_BOV */
ETAG ( "C3" , "C2" , "C1" ))
ABSENT ON NULL EMPTY ON NO ROWS RETURNING JSON FORMAT JSON ETAG)
"JSON{'T2_C1':X.C1,'T2_C2':X.C2,'T2_C3':X.C3}"
FROM "TUNA"."T2" "A3"
WHERE "A3"."C1"="A2"."C1")
NULL ON NULL EMPTY ON NO ROWS RETURNING JSON FORMAT JSON
/*+ QJSNMD_NOPD3_IN_BOV */ ETAG ( "C1" , "C2" ) TOPLEVEL) "DATA"
, SYS_MAKE_OID_FROM_PK ("A2"."C1") "RESID"
, SYS_MK_FULL_ETAG (SYS_OBJ_ETAG (SYS_ROW_ETAG ("A2"."C1", "A2"."C2")
, 't2'
, (SELECT SYS_AGG_ETAGS (SYS_OBJ_ETAG (SYS_ROW_ETAG ("A4"."C1", "A4"."C2", "A4"."C3")))
"JSON{'T2_C1':X.C1,'T2_C2':X.C2,'T2_C3':X.C3}"
FROM "TUNA"."T2" "A4"
WHERE "A4"."C1"="A2"."C1")), 0) "ETAG"
FROM "TUNA"."T1" "A2") "A1";
INSERT 문
아래는 dv_t1_t2 뷰에 대한 INSERT 문의 실행 계획입니다. 이유는 모르겠지만 실행 계획 2번에서 7번까지 T2 테이블을 집계하는 오퍼레이션이 표시됩니다. 테스트를 반복하기 위해 DML 문을 수행한 후 롤백을 수행해야 합니다.
-- 7
INSERT
INTO dv_t1_t2
VALUES ('{"_id":{"t1_c1":2},"t1_c2":"B","t2":[{"t2_c1":2,"t2_c2":1,"t2_c3":"A"},{"t2_c1":2,"t2_c2":2,"t2_c3":"B"}]}');
1 row created.
-----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-----------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 0 | 25 |
| 1 | LOAD TABLE CONVENTIONAL | T1 | 1 | 0 | 25 |
| 2 | SORT GROUP BY | | 0 | 0 | 0 | --> ?
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 0 | 0 | 0 | --> ?
|* 4 | INDEX RANGE SCAN | T2_PK | 0 | 0 | 0 | --> ?
| 5 | SORT GROUP BY | | 0 | 0 | 0 | --> ?
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 0 | 0 | 0 | --> ?
|* 7 | INDEX RANGE SCAN | T2_PK | 0 | 0 | 0 | --> ?
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("X"."C1"=:B1)
7 - access("X"."C1"=:B1)
10046 트레이스를 살펴보면 아래의 Recursive SQL이 수행되는 것을 확인할 수 있습니다. 8-2번 SELECT 문은 t2 테이블에 대한 Optimistic Locking을 위해 수행되는 것으로 보입니다.
-- 8-1
INSERT
INTO "TUNA"."T1" ("C1", "C2")
VALUES (:1, :2)
RETURNING SYS_ROW_ETAG ("C1", "C2") INTO :3;
-- 8-2
SELECT /*+DUALV363A540486EB8928A*/
SYS_ROW_ETAG ("C1", "C2", "C3")
FROM "TUNA"."T2"
WHERE "C1" = :1
AND "C2" = :2;
-- 8-3
INSERT
INTO "TUNA"."T2" ("C1", "C2", "C3")
VALUES (:1, :2, :3)
RETURNING "C1", "C2", SYS_ROW_ETAG ("C1", "C2", "C3") INTO :4, :5, :6;
아래와 같이 t1, t2 테이블에 각각 데이터를 삽입할 수도 있습니다. 9-3번 INSERT 문은 t1 테이블에 c1이 3인 로우를 삽입합니다.
-- 9-1
INSERT
INTO dv_t1_t2
VALUES ('{"_id":{"t1_c1":2},"t1_c2":"B"}');
1 row created.
-- 9-2
INSERT
INTO dv_t1_t2
VALUES ('{"t2":[{"t2_c1":3,"t2_c2":1,"t2_c3":"A"},{"t2_c1":3,"t2_c2":2,"t2_c3":"B"}]}');
1 row created.
아래 INSERT 문은 t1 테이블에 c1이 1인 로우를 삽입하므로 ORA-00001 에러가 발생합니다.
-- 10-1
INSERT
INTO dv_t1_t2
VALUES ('{"_id":{"t1_c1":1},"t1_c2":"A","t2":[{"t2_c1":1,"t2_c2":3,"t2_c3":"C"},{"t2_c1":1,"t2_c2":4,"t2_c3":"D"}]}');
ORA-42692: Cannot insert into JSON Relational Duality View 'DV_T1_T2'
: Error while inserting into table 'T1'
ORA-00001: unique constraint (TUNA.T1_PK) violated on table TUNA.T1 columns (C1)
ORA-03301: (ORA-00001 details) row with column values (C1:1) already EXISTS
-- 10-2
INSERT
INTO dv_t1_t2
VALUES ('{"t2":[{"t2_c1":1,"t2_c2":3,"t2_c3":"C"},{"t2_c1":1,"t2_c2":4,"t2_c3":"D"}]}');
ORA-42692: Cannot insert into JSON Relational Duality View 'DV_T1_T2'
: Error while inserting into table 'T1'
ORA-00001: unique constraint (TUNA.T1_PK) violated on table TUNA.T1 columns (C1)
ORA-03301: (ORA-00001 details) row with column values (C1:1) already EXISTS
아래 INSERT 문은 t1 테이블의 c1이 2인 로우와 t2 테이블의 c1이 1인 로우를 삽입하므로 ORA-40944 에러가 발생합니다.
-- 11
INSERT
INTO dv_t1_t2
VALUES ('{"_id":{"t1_c1":2},"t1_c2":"B","t2":[{"t2_c1":1,"t2_c2":3,"t2_c3":"C"},{"t2_c1":1,"t2_c2":4,"t2_c3":"D"}]}');
ORA-40944: Cannot insert into JSON Relational Duality View 'DV_T1_T2': The input JSON document is invalid.
JZN-00661: linked rows have conflicting column values for 'T1'.'C1' to 'T2'.'C1'
UPDATE 문
아래는 dv_t1_t2 뷰에 대한 UPDATE 문의 실행 계획입니다. 이유는 모르겠지만 실행 계획 2번에 INDEX UNIQUE SCAN 오퍼레이션이 표시되고, 실행 계획 5번에서 7번까지 T2 테이블을 집계하는 오퍼레이션이 표시됩니다.
-- 12
UPDATE dv_t1_t2 a
SET a.data = '{"_id":{"t1_c1":1},"t1_c2":"B","t2":[{"t2_c1":1,"t2_c2":3,"t2_c3":"C"},{"t2_c1":1,"t2_c2":4,"t2_c3":"D"}]}'
WHERE a.data."_id"."t1_c1" = 1;
1 row updated.
-----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-----------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 0 | 39 |
| 1 | UPDATE | T1 | 1 | 0 | 39 |
|* 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 |
| 5 | SORT GROUP BY | | 1 | 1 | 2 | --> ?
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 2 | 2 | --> ?
|* 7 | INDEX RANGE SCAN | T2_PK | 1 | 2 | 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("A"."C1"=1)
4 - access("A"."C1"=:B1)
7 - access("X"."C1"=:B1)
10046 트레이스를 살펴보면 아래의 Recursive SQL이 수행되는 것을 확인할 수 있습니다.
-- 13-1
DELETE
FROM "TUNA"."T2"
WHERE "C1" = :1
AND "C2" = :2
AND SYS_ROW_ETAG ("C1", "C2", "C3") = RAWTOHEX (:3);
-- 13-2
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;
-- 13-3
SELECT /*+DUALV363A540486EB8928A*/
SYS_ROW_ETAG ("C1", "C2", "C3")
FROM "TUNA"."T2"
WHERE "C1"=:1
AND "C2"=:2;
-- 13-4
INSERT
INTO "TUNA"."T2" ("C1", "C2", "C3")
VALUES (:1, :2, :3)
RETURNING "C1", "C2", SYS_ROW_ETAG ("C1", "C2", "C3") INTO :4, :5, :6;
-- 13-5
SELECT /*+DRDUALV363A540486EB8928A*/
v.data."_metadata".ETAG.BINARY ()
FROM "DV_T1_T2" v
WHERE JSON_VALUE (data, '$."_id"."T1_C1"' RETURNING NUMBER) = :1;
DELETE 문
아래는 dv_t1_t2 뷰에 대한 DELETE 문의 실행 계획입니다.
-- 14
DELETE dv_t1_t2 a
WHERE a.data."_id"."t1_c1" = 1;
1 row deleted.
----------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
----------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 0 | 39 |
| 1 | DELETE | T1 | 1 | 0 | 39 |
|* 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("A"."C1"=1)
10046 트레이스를 살펴보면 아래의 Recursive SQL이 수행되는 것을 확인할 수 있습니다.
-- 15-1
SELECT /*+DUALV363A540486EB8928A*/
v.data."_metadata".ETAG.BINARY ()
FROM "DV_T1_T2" v
WHERE JSON_VALUE (data, '$."_id"."t1_c1"' RETURNING NUMBER) = :1
FOR UPDATE;
-- 15-2
DELETE
FROM "TUNA"."T2"
WHERE "C1" = :1;
-- 15-3
DELETE
FROM "TUNA"."T1"
WHERE "C1" = :1;