JSON Relational Duality #3 - M:1 조인 Duality 뷰

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;

저작자표시 비영리 변경금지

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

JSON Relational Duality #3 - M:1 조인 Duality 뷰

준비

SELECT 문

INSERT 문

UPDATE 문

DELETE 문

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역

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

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

	-- 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.

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

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

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

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

	-- 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.

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

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

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

	-- 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'.

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

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