개요
Oracle 23c에 Table Value Constructor(이후 TVC) 기능이 추가되었습니다.
The database's SQL engine now supports a VALUES clause for many types of statements. This new clause allows for materializing rows of data on the fly by specifying them using the new syntax without relying on existing tables. Oracle supports the VALUES clause for the SELECT, INSERT, and MERGE statements. The introduction of the new VALUES clause allows developers to write less code for ad-hoc SQL commands, leading to better readability with less effort.
SELECT 문
아래 쿼리는 FROM 절에 TVC를 사용합니다. 실행 계획 2번에 VALUES SCAN 오퍼레이션이 표시된 것을 확인할 수 있습니다. 아울러 실행 계획에 Table Value Constructor 항목이 추가로 표시됩니다.
-- 1-1
SELECT * FROM (VALUES (1, 'A'), (2, 'B')) a (c1, c2);
C1 C2
-- --
1 A
2 B
2 행이 선택되었습니다.
------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 4 (0)|
| 1 | VIEW | | 2 | 4 (0)|
| 2 | VALUES SCAN | | 2 | 4 (0)|
------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1 / "A"@"SEL$3"
2 - SET$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "A"."C1"[NUMBER,2], "A"."C2"[CHARACTER,1]
2 - STRDEF[2], STRDEF[1]
Table Value Constructor
------------------------------------------------------
2 - #tuples:2, #elems:2 values:(1, 'A'), (2, 'B')
Query Block Registry:
---------------------
SEL$1 (PARSER) [FINAL]
SEL$2 (PARSER) [FINAL]
SEL$3 (PARSER) [FINAL]
SET$1 (PARSER) [FINAL]
10053 트레이스의 변환된 쿼리에서 TVC가 SELECT DUAL 문을 UNION ALL 연산자로 연결한 형태로 구현된 것을 확인할 수 있습니다. 참고로 가독성을 위해 쿼리를 포맷팅했습니다. UNION ALL 연산자로 연결된 쿼리 블록은 각각 최적화되므로 과도한 TVC 사용으로 인한 파싱 경합에 주의할 필요가 있습니다.
-- 2-1
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "A"."C1" "C1"
, "A"."C2" "C2"
FROM ((SELECT 1 "C1", 'A' "C2" FROM "SYS"."DUAL" "DUAL")
UNION ALL
(SELECT 2 , 'B' FROM "SYS"."DUAL" "DUAL")) "A"
아래 쿼리는 WITH 절에 TVC를 사용합니다. 3-2번 쿼리처럼 FROM 절에 TVC를 사용해도 결과가 동일합니다.
-- 3-1
WITH w1 (c1, c2) AS (VALUES (1, 'A'), (2, 'B'))
SELECT * FROM w1;
C1 C2
-- --
1 A
2 B
2 행이 선택되었습니다.
-- 3-2
WITH w1 AS (SELECT * FROM (VALUES (1, 'A'), (2, 'B')) a (c1, c2))
SELECT * FROM w1;
DBMS_UTILITY.EXPAND_SQL_TEXT 프로시저로도 변환된 쿼리를 확인할 수 있습니다. 참고로 해당 프로시저는 SELECT 문만 지원합니다.
-- 4
SET SERVEROUT ON
DECLARE
v_input_sql_text VARCHAR2(32767);
v_output_sql_text VARCHAR2(32767);
BEGIN
v_input_sql_text := q'?WITH w1 (c1, c2) AS (VALUES (1, 'A'), (2, 'B')) SELECT * FROM w1?';
DBMS_UTILITY.EXPAND_SQL_TEXT (v_input_sql_text, v_output_sql_text);
DBMS_OUTPUT.PUT_LINE (v_output_sql_text);
END;
/
SELECT "A1"."C1" "C1"
, "A1"."C2" "C2"
FROM ((SELECT 1 "C1",'A' "C2" FROM "SYS"."DUAL" "A4")
UNION ALL
(SELECT 2 "C1",'B' "C2" FROM "SYS"."DUAL" "A3")) "A1"
INSERT 문
아래 INSERT 문은 VALUES 절에 TVC를 사용합니다.
-- 5-1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 NUMBER, c2 VARCHAR2(1));
INSERT INTO t1 VALUES (1, 'A'), (2, 'B'), (3, 'C');
2 행이 생성되었습니다.
--------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------
| 0 | INSERT STATEMENT | | 3 | 6 (0)|
| 1 | LOAD TABLE CONVENTIONAL | T1 | | |
| 2 | VALUES SCAN | | 3 | 6 (0)|
--------------------------------------------------------------
-- 5-2
INSERT INTO t1 SELECT * FROM (VALUES (1, 'D'), (2, 'E'), (3, 'C')) a (c1, c2);
VALUES 절에 TVC를 사용한 INSERT 문은 10053 트레이스에서 잘못된 변환 쿼리가 표시됩니다.
-- 6
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT 0 FROM "TUNA"."T1" "T1"
MERGE 문
아래 MERGE 문은 USING 절에 TVC를 사용합니다.
-- 7-1
MERGE
INTO t1 t
USING (VALUES (1, 'D'), (2, 'E'), (3, 'F'), (4, 'G')) s (c1, c2)
ON (t.c1 = s.c1)
WHEN MATCHED THEN
UPDATE SET t.c2 = s.c2
WHEN NOT MATCHED THEN
INSERT (t.c1, t.c2)
VALUES (s.c1, s.c2);
3 행이 병합되었습니다.
----------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------
| 0 | MERGE STATEMENT | | 4 | 10 (0)|
| 1 | MERGE | T1 | | |
| 2 | VIEW | | | |
|* 3 | HASH JOIN OUTER | | 4 | 10 (0)|
| 4 | VIEW | | 4 | 8 (0)|
| 5 | VALUES SCAN | | 4 | 8 (0)|
| 6 | TABLE ACCESS FULL| T1 | 82 | 2 (0)|
----------------------------------------------------------
-- 7-2
MERGE
INTO t1 t
USING (SELECT * FROM (VALUES (1, 'D'), (2, 'E'), (3, 'F'), (4, 'G')) a (c1, c2)) s
ON (t.c1 = s.c1)
WHEN MATCHED THEN
UPDATE SET t.c2 = s.c2
WHEN NOT MATCHED THEN
INSERT (t.c1, t.c2)
VALUES (s.c1, s.c2);
USING 절에 TVC를 사용한 MERGE 문 역시 10053 트레이스에서 잘못된 변환 쿼리가 표시됩니다. INSERT 문과 MERGE 문에 사용한 TVC는 향후 추가 테스트가 필요할 것으로 보입니다.
-- 8
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT 0
FROM (SELECT /*+ NO_MERGE */
"T".ROWID
, "T"."C1" "C1"
, "T"."C2" "C2"
, "S"."C1" "C1"
, "S"."C2" "C2"
FROM ((SELECT 1 "C1",'D' "C2" FROM "SYS"."DUAL" "DUAL")
UNION ALL
(SELECT 2 ,'E' FROM "SYS"."DUAL" "DUAL")) "S","TUNA"."T1" "T"
WHERE "T"."C1"(+)="S"."C1") "from$_subquery$_018"
, ((SELECT 1 "C1",'D' "C2" FROM "SYS"."DUAL" "DUAL")
UNION ALL
(SELECT 2 ,'E' FROM "SYS"."DUAL" "DUAL")) "S","TUNA"."T1" "T"