JSON Relational Duality #1 - 단순 Duality 뷰
JSON Relational Duality #2 - 1:M 조인 Duality 뷰
JSON Relational Duality #3 - M:1 조인 Duality 뷰
지난 글에 이어 이번 글에서는 M:1 조인 Duality 뷰의 동작에 대해 살펴보겠습니다.
준비
테스트 버전은 아래와 같습니다.
-- 1
SELECT version_full FROM product_component_version;
VERSION_FULL
------------
23.4.0.24.05
1 row selected.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다.
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, 'B');
INSERT INTO t2 VALUES (1, 2, 'C');
COMMIT;
아래 구문으로 M:1 조인 Duality 뷰를 생성할 수 있습니다.
-- 3
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW dv_t2_t1 AS
SELECT JSON {'_id':{'t2_c1':a.c1, 't2_c2':a.c2}
, 't2_c3':a.c3
, UNNEST (SELECT JSON {'t1_c1':x.c1, 't1_c2':x.c2}
FROM t1 x WITH INSERT UPDATE DELETE
WHERE x.c1 = a.c1)}
FROM t2 a WITH INSERT UPDATE DELETE;
아래는 JSON Relational Duality 관련 뷰를 조회한 결과입니다. t1 테이블의 relationship이 singleton join_type이 reverse로 표시됩니다.
-- 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_T2_T1';
JSON_COLUMN_NAME ROOT_TABLE_NAME ROOT_TABLE_OWNER ALLOW_INSERT ALLOW_UPDATE ALLOW_DELETE READ_ONLY
---------------- --------------- ---------------- ------------ ------------ ------------ ---------
DATA T2 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_T2_T1';
TABLE_OWNER TABLE_NAME ROOT_TABLE TABLE_NUMBER PARENT_TABLE_NUMBER RELATIONSHIP
----------- ---------- ---------- ------------ ------------------- ------------
TUNA T2 1 0
TUNA T1 0 1 0 singleton
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_T2_T1';
TABLE_OWNER TABLE_NAME COLUMN_NAME DATA_TYPE JSON_KEY_NAME PRIMARY_KEY_POS
----------- ---------- ----------- --------- ------------- ---------------
TUNA T2 C1 NUMBER t2_c1 1
TUNA T2 C2 NUMBER t2_c2 2
TUNA T2 C3 VARCHAR2 t2_c3
TUNA T1 C1 NUMBER t1_c1 1
TUNA T1 C2 VARCHAR2 t1_c2
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_T2_T1';
PARENT_TABLE_OWNER PARENT_TABLE_NAME CHILD_TABLE_OWNER CHILD_TABLE_NAME FROM_COLUMN TO_COLUMN JOIN_TYPE KEY_NAME
------------------ ----------------- ----------------- ---------------- ----------- --------- --------- --------
TUNA T2 TUNA T1 C1 C1 reverse
1 row selected.
SELECT 문
아래는 SELECT 문으로 dv_t2_t1 뷰를 조회한 결과와 실행 계획입니다. 결과를 JSON_SERIALIZE 함수로 포맷팅했습니다.
-- 5
SELECT JSON_SERIALIZE (data PRETTY) AS data
FROM dv_t2_t1;
DATA
------------------------------------------------
{
"_id" :
{
"t2_c1" : 1,
"t2_c2" : 1
},
"_metadata" :
{
"etag" : "1FD5F07710A9E22212095308D5E32CFB",
"asof" : "0000000001041232"
},
"t2_c3" : "B",
"t1_c1" : 1,
"t1_c2" : "A"
}
{
"_id" :
{
"t2_c1" : 1,
"t2_c2" : 2
},
"_metadata" :
{
"etag" : "D1070E02E50E21767E2994034D7F87B1",
"asof" : "0000000001041232"
},
"t2_c3" : "C",
"t1_c1" : 1,
"t1_c2" : "A"
}
2 rows selected.
-------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 8 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 2 | 4 |
|* 2 | INDEX UNIQUE SCAN | T1_PK | 2 | 2 | 2 |
| 3 | TABLE ACCESS FULL | T2 | 1 | 2 | 8 |
-------------------------------------------------------------------------
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):
---------------------------------------------------
2 - access("X"."C1"=:B1)
아래는 fnc_expand_sql_text 함수로 변환 후의 5번 쿼리를 조회한 결과입니다.
-- 6-1
SELECT fnc_expand_sql_text ('SELECT * FROM DV_T2_T1') AS sql_text
FROM DUAL;
-- 6-2
SELECT "A1"."DATA" "DATA"
FROM (SELECT JSON_OBJECT(/*+ NO_GEN_WITH_ROWSET */
'_id' VALUE JSON_OBJECT ('t2_c1' VALUE "A2"."C1"
, 't2_c2' VALUE "A2"."C2"
NULL ON NULL EMPTY ON NO ROWS RETURNING JSON FORMAT JSON
/*+ QJSNMD_NOPD3_NEST */
/*+ QJSNMD_NOPD3_IN_BOV */)
, 't2_c3' VALUE "A2"."C3"
, UNNEST (SELECT JSON_OBJECT ('t1_c1' VALUE "A3"."C1"
, 't1_c2' VALUE "A3"."C2"
NULL ON NULL EMPTY ON NO ROWS RETURNING JSON FORMAT JSON
/*+ QJSNMD_NOPD3_IN_BOV */
ETAG ("C2", "C1")) "JSON{'T1_C1':X.C1,'T1_C2':X.C2}"
FROM "TUNA"."T1" "A3"
WHERE "A3"."C1"="A2"."C1")
NULL ON NULL EMPTY ON NO ROWS RETURNING JSON FORMAT JSON
/*+ QJSNMD_NOPD3_IN_BOV */
ETAG ("C2", "C1", "C3") TOPLEVEL) "DATA"
, SYS_MAKE_OID_FROM_PK ("A2"."C1", "A2"."C2") "RESID"
, SYS_MK_FULL_ETAG (SYS_OBJ_ETAG (SYS_ROW_ETAG ("A2"."C1", "A2"."C2", "A2"."C3")
, '_sys_unnest5'
, (SELECT SYS_OBJ_ETAG (SYS_ROW_ETAG ("A4"."C1", "A4"."C2")) "JSON{'T1_C1':X.C1,'T1_C2':X.C2}"
FROM "TUNA"."T1" "A4"
WHERE "A4"."C1"="A2"."C1")), 0) "ETAG"
FROM "TUNA"."T2" "A2") "A1";
INSERT 문
아래는 dv_t2_t1 뷰에 대한 INSERT 문의 실행 계획입니다. 이유는 모르겠지만 실행 계획 2번에서 7번까지 T2 테이블을 집계하는 오퍼레이션이 표시됩니다. 테스트를 반복하기 위해 DML 문을 수행한 후 롤백을 수행해야 합니다.
-- 7
INSERT
INTO dv_t2_t1
VALUES ('{"_id":{"t2_c1":2,"t2_c2":1},"t2_c3":"A","t1_c1":2,"t1_c2":"B"}');
1 row created.
--------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 0 | 13 |
| 1 | LOAD TABLE CONVENTIONAL | T2 | 1 | 0 | 13 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 0 | 0 | 0 | --> ?
|* 3 | INDEX UNIQUE SCAN | T1_PK | 0 | 0 | 0 | --> ?
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 0 | 0 | 0 | --> ?
|* 5 | INDEX UNIQUE SCAN | T1_PK | 0 | 0 | 0 | --> ?
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("X"."C1"=:B1)
5 - access("X"."C1"=:B1)
10046 트레이스를 살펴보면 아래의 Recursive SQL이 수행되는 것을 확인할 수 있습니다. 8-2번 SELECT 문은 t2 테이블에 대한 Optimistic Locking을 위해 수행되는 것으로 보입니다.
-- 8-1
INSERT
INTO "TUNA"."T2" ("C1", "C2", "C3")
VALUES (:1, :2, :3)
RETURNING SYS_ROW_ETAG ("C1", "C2", "C3") INTO :4;
-- 8-2
SELECT /*+DUALV363A540486EB8928A*/
SYS_ROW_ETAG ("C1", "C2")
FROM "TUNA"."T1"
WHERE "C1" = :1;
-- 8-3
INSERT
INTO "TUNA"."T1" ("C1", "C2")
VALUES (:1, :2)
RETURNING SYS_ROW_ETAG ("C1", "C2") INTO :3;
아래 INSERT 문은 t2 테이블에만 로우를 삽입합니다.
-- 9-1
INSERT
INTO dv_t2_t1
VALUES ('{"_id":{"t2_c1":1,"t2_c2":3},"t2_c3":"C","t1_c1":1,"t1_c2":"A"}');
1 row created.
-- 9-2
INSERT
INTO dv_t2_t1
VALUES ('{"_id":{"t2_c1":1,"t2_c2":3},"t2_c3":"C"}');
1 row created.
아래 INSERT 문은 t2 테이블의 c2 칼럼에 NULL을 삽입하므로 ORA-01400 에러가 발생합니다.
-- 10
INSERT
INTO dv_t2_t1
VALUES ('{"t1_c1":1,"t1_c2":"A"}');
ORA-42692: Cannot insert into JSON Relational Duality View 'DV_T2_T1': Error while inserting into table 'T2'
ORA-01400: cannot insert NULL into ("TUNA"."T2"."C2")
UPDATE 문
아래는 dv_t2_t1 뷰에 대한 UPDATE 문의 실행 계획입니다. 이유는 모르겠지만 실행 계획 2번에 INDEX UNIQUE SCAN 오퍼레이션이 표시되고, 실행 계획 5번에서 6번까지 T1 테이블을 조회하는 오퍼레이션이 표시됩니다.
-- 11
UPDATE dv_t2_t1 a
SET a.data = '{"_id":{"t2_c1":1,"t2_c2":1},"t2_c3":"B","t1_c1":1,"t1_c2":"B"}'
WHERE a.data."_id"."t2_c1" = 1
AND a.data."_id"."t2_c2" = 1;
--------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 0 | 21 |
| 1 | UPDATE | T2 | 1 | 0 | 21 |
|* 2 | INDEX UNIQUE SCAN | T2_PK | 1 | 1 | 1 | --> ?
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 1 | 2 |
|* 4 | INDEX UNIQUE SCAN | T2_PK | 1 | 1 | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 2 | --> ?
|* 6 | 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$EC6BC154")
JSON_QRYOVERGEN_REWRITE(@"SEL$55B0D60B")
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."C1"=1 AND "A"."C2"=1)
4 - access("A"."C1"=:B1 AND "A"."C2"=:B2)
6 - access("X"."C1"=:B1)
10046 트레이스를 살펴보면 아래의 Recursive SQL이 수행되는 것을 확인할 수 있습니다.
-- 12-1
UPDATE "TUNA"."T2"
SET "C3" = :1
WHERE "C1" = :2
AND "C2" = :3
AND SYS_ROW_ETAG ("C1", "C2", "C3") = RAWTOHEX (:4)
RETURNING SYS_ROW_ETAG ("C1", "C2", "C3") INTO :5;
-- 12-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;
-- 12-3
SELECT /*+DRDUALV363A540486EB8928A*/
v.DATA."_metadata".ETAG.BINARY ()
FROM "DV_T2_T1" v
WHERE JSON_VALUE (data, '$."_id"."t2_c1"' RETURNING NUMBER) = :1
AND JSON_VALUE (data, '$."_id"."t2_c2"' RETURNING NUMBER) = :2;
아래 UPDATE 문은 t2 테이블의 로우만 갱신했지만 t1 테이블의 c2 값이 NULL로 갱신되어 ORA-40896 에러가 발생하는 것으로 보입니다.
-- 13
UPDATE dv_t2_t1 a
SET a.data = '{"_id":{"t2_c1":1,"t2_c2":1},"t2_c3":"B"}'
WHERE a.data."_id"."t2_c1" = 1
AND a.data."_id"."t2_c2" = 1;
ORA-40896: Cannot update JSON Relational Duality View 'DV_T2_T1'
: Concurrent modification detected to document with ID 'FB03C1020003C10200'.
DELETE 문
아래는 dv_t2_t1 뷰에 대한 DELETE 문의 실행 계획입니다.
-- 14
DELETE dv_t2_t1 a
WHERE a.data."_id"."t2_c1" = 1
AND a.data."_id"."t2_c2" = 1;
1 row deleted.
----------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
----------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 0 | 26 |
| 1 | DELETE | T2 | 1 | 0 | 26 |
|* 2 | INDEX UNIQUE SCAN| T2_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$1CF2246C")
JSON_QRYOVERGEN_REWRITE(@"SEL$F032A239")
10046 트레이스를 살펴보면 아래의 Recursive SQL이 수행되는 것을 확인할 수 있습니다.
-- 15-1
SELECT /*+DUALV363A540486EB8928A*/
v.data."_metadata".ETAG.BINARY ()
FROM "DV_T2_T1" v
WHERE JSON_VALUE (data, '$."_id"."t2_c1"' RETURNING NUMBER) = :1
AND JSON_VALUE (data, '$."_id"."t2_c2"' RETURNING NUMBER) = :2
FOR UPDATE;
-- 15-2
DELETE
FROM "TUNA"."T2"
WHERE "C1"=:1
AND "C2"=:2;