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;