OS 환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 23.4.0.24.05 ai Free
방법 : 오라클 23ai 신기능 Table Value Constructor(TVC)
오라클 23ai 부터 Table Value Constructor(TVC) 를 사용할수 있음
이 방식은 insert, select, merge 시 여러건의 데이터를 한번에 처리할 수 있음
예를들어 insert into values 절 사용시 1건이 아닌 여러건을 넣을수 있고
select 절에서 union all로 데이터를 합치던 방식을 union all 없이 values 절로 간결하게 작성할수 있어 가독성도 높아짐
그리고 with 절이나 merge 문에도 효과적으로 사용할 수 있음
참고로 한번의 values 절에서 최대 65535건까지 넣을수 있음
본문에서는 insert values 절, select values 절, with 문 사용시, merge 문 사용시 각각 이전 방식과 새로추가된 방식을 사용하는법을 설명하고 추가로 실행계획도 확인해봄
테스트
1. insert into values 절
2. select values 절
3. select values 절 + with절
4. merge 문
5. TVC 실행계획(autotrace + xplan + 10046 + 10053)
1. insert into values 절
한번의 insert 구문 실행시 여러건 insert 가능
샘플 테이블 생성
|
1
2
3
|
SQL>
drop table t1 purge;
create table t1 (col1 number, col2 varchar(10));
|
샘플 데이터 3건 삽입(기존 방식)
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> insert into t1 values (1, 'test1');
1 row created.
SQL> insert into t1 values (2, 'test2');
1 row created.
SQL> insert into t1 values (3, 'test3');
1 row created.
|
샘플 데이터 3건 삽입(새로 추가된 방식)
|
1
2
3
|
SQL> insert into t1 values (4, 'test4'), (5, 'test5'), (6, 'test6');
3 rows created.
|
입력된 데이터 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL> select * from t1;
COL1 COL2
---------- ----------
1 test1
2 test2
3 test3
4 test4
5 test5
6 test6
6 rows selected.
|
두 방식 모두 정상적으로 삽입됨
참고용
insert values 절 최대 갯수만큼 삽입
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
DECLARE
v_sql clob;
BEGIN
v_sql := 'INSERT INTO t1 VALUES ';
FOR i IN 1..65534 LOOP
IF i > 1 THEN
v_sql := v_sql || ',';
END IF;
v_sql := v_sql || '(1)';
END LOOP;
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.PUT_LINE('Insertion complete');
END;
/
|
최대 갯수 초과 입력시 ORA-63805 에러 발생함
참고 : ORA-63805: Maximum number of tuples in Table Value Constructor exceeded ( https://positivemh.tistory.com/1119 )
2. select values 절
샘플 데이터 생성시 사용가능
샘플 데이터 생성(기존 방식)
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
select 1 as id, 'max' as name, '2024-01-01' as date1 from dual
union all
select 2 as id, 'bob' as name, '2024-02-01' as date1 from dual
union all
select 3 as id, 'tim' as name, '2024-03-01' as date1 from dual;
ID NAM DATE1
---------- --- ----------
1 max 2024-01-01
2 bob 2024-02-01
3 tim 2024-03-01
|
샘플 데이터 생성(새로 추가된 방식)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
select * from (
values
(1, 'max', '2024-01-01'),
(2, 'bob', '2024-02-01'),
(3, 'tim', '2024-03-01')
) as temp_tbl (id, name, date1);
ID NAM DATE1
---------- --- ----------
1 max 2024-01-01
2 bob 2024-02-01
3 tim 2024-03-01
|
union all 없이 select 절에 정상적으로 values 절 사용이 가능함
3. select values 절 + with절
샘플 데이터 생성시 사용가능
샘플 데이터 생성(기존 방식)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
with temp_tbl (id, name, date1) as (
select 1 as id, 'max' as name, '2024-01-01' as date1 from dual
union all
select 2 as id, 'bob' as name, '2024-02-01' as date1 from dual
union all
select 3 as id, 'tim' as name, '2024-03-01' as date1 from dual
)
select * from temp_tbl;
ID NAM DATE1
---------- --- ----------
1 max 2024-01-01
2 bob 2024-02-01
3 tim 2024-03-01
|
샘플 데이터 생성(새로 추가된 방식)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
with temp_tbl (id, name, date1) as (
values
(1, 'max', '2024-01-01'),
(2, 'bob', '2024-02-01'),
(3, 'tim', '2024-03-01')
)
select * from temp_tbl;
ID NAM DATE1
---------- --- ----------
1 max 2024-01-01
2 bob 2024-02-01
3 tim 2024-03-01
|
union all 없이 with 절에 정상적으로 values 절 사용이 가능함
4. merge 문
merge 문 사용시 values 절 사용가능
emp2 테이블 생성
|
1
2
3
|
SQL> create table emp2 as select empno, ename, hiredate, job from emp;
Table created.
|
기존 emp2 테이블 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL> select * from emp2;
EMPNO ENAME HIREDATE JOB
---------- ---------- --------- ---------
7839 KING 17-NOV-81 PRESIDENT
7698 BLAKE 01-MAY-81 MANAGER
7782 CLARK 09-JUN-81 MANAGER
7566 JONES 02-APR-81 MANAGER
7788 SCOTT 13-JUL-87 ANALYST
7902 FORD 03-DEC-81 ANALYST
7369 SMITH 17-DEC-80 CLERK
7499 ALLEN 20-FEB-81 SALESMAN
7521 WARD 22-FEB-81 SALESMAN
7654 MARTIN 28-SEP-81 SALESMAN
7844 TURNER 08-SEP-81 SALESMAN
7876 ADAMS 13-JUL-87 CLERK
7900 JAMES 03-DEC-81 CLERK
7934 MILLER 23-JAN-82 CLERK
14 rows selected.
|
14건이 존재함
merge 문 실행
(empno를 기준으로 매칭한뒤, empno가 일치하는 경우 해당 행을 업데이트하고, 일치하지 않는 경우 새로운 행을 삽입)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
merge into emp2
using (
values
(1, 'MAX', to_date('2024-01-01', 'yyyy-mm-dd'), 'SALESMAN'),
(2, 'BOB', to_date('2024-02-01', 'yyyy-mm-dd'), 'CLERK'),
(3, 'TIM', to_date('2024-03-01', 'yyyy-mm-dd'), 'ANALYST')
) src (empno, ename, hiredate, job)
on (emp2.empno = src.empno)
when matched then
update set
emp2.ename = src.ename,
emp2.hiredate = src.hiredate,
emp2.job = src.job
when not matched then
insert (empno, ename, hiredate, job)
values (src.empno, src.ename, src.hiredate, src.job);
3 rows merged.
|
merge 구문 설명
merge into emp2 : emp2 테이블을 대상으로 merge 작업을 실행
using (values ...) src (empno, ename ...) : values 절을 사용해 소스 데이터를 정의하고 src라는 별칭 부여
on (emp2.empno = src.empno) : 대상 테이블(emp2)과 소스 데이터(src) 간의 매칭 조건을 정의
when matched then ... : 매칭 조건이 충족될 때 update 실행
when not matched then ... : 매칭 조건이 충족되지 않을 때 insert 실행
merge 후 emp2 테이블 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL> select * from emp2;
EMPNO ENAME HIREDATE JOB
---------- ---------- --------- ---------
7839 KING 17-NOV-81 PRESIDENT
7698 BLAKE 01-MAY-81 MANAGER
7782 CLARK 09-JUN-81 MANAGER
7566 JONES 02-APR-81 MANAGER
7788 SCOTT 13-JUL-87 ANALYST
7902 FORD 03-DEC-81 ANALYST
7369 SMITH 17-DEC-80 CLERK
7499 ALLEN 20-FEB-81 SALESMAN
7521 WARD 22-FEB-81 SALESMAN
7654 MARTIN 28-SEP-81 SALESMAN
7844 TURNER 08-SEP-81 SALESMAN
7876 ADAMS 13-JUL-87 CLERK
7900 JAMES 03-DEC-81 CLERK
7934 MILLER 23-JAN-82 CLERK
1 MAX 01-JAN-24 SALESMAN
2 BOB 01-FEB-24 CLERK
3 TIM 01-MAR-24 ANALYST
17 rows selected.
|
기존에 empno가 1,2,3인 row가 없었기 때문에 신규로 empno가 1,2,3 인 row가 추가됨
5. TVC 실행계획(autotrace + xplan + 10046 + 10053)
실행계획은 insert 구문만 확인해봄
5_1. autotrace
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
SQL>
set autotrace on
insert into t1 values (4, 'test4'), (5, 'test5'), (6, 'test6');
3 rows created.
Execution Plan
----------------------------------------------------------
Plan hash value: 1
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 3 | 6 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | T1 | | | |
| 2 | VALUES SCAN | | 3 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------
Table Value Constructor
------------------------------------------------------
2 - #tuples:3, #elems:2 values:(4, 'test4'), (5, 'test5'), (6, 'test6')
Statistics
----------------------------------------------------------
0 recursive calls
3 db block gets
1 consistent gets
0 physical reads
0 redo size
204 bytes sent via SQL*Net to client
83 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
|
처음보는 VALUES SCAN 라는 실행계획이 표시되고
Table Value Constructor 라는 탭도 같이 표시됨
2 - #tuples:3, #elems:2 values:(4, 'test4'), (5, 'test5'), (6, 'test6')
row 3개(tuples:3) 라고 나오고 값까지 각각 표시됨(values:(4, 'test4'), (5, 'test5'), (6, 'test6'))
5_2. xplan display_cursor
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
SQL>
alter session set statistics_level = all;
insert into t1 values (4, 'test4'), (5, 'test5'), (6, 'test6');
SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 31uu8vmn4twsr, child number 1
-------------------------------------
insert into t1 values (4, 'test4'), (5, 'test5'), (6, 'test6')
Plan hash value: 1
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | 6 (100)| | 0 |00:00:00.01 | 4 |
| 1 | LOAD TABLE CONVENTIONAL | T1 | 1 | | | | 0 |00:00:00.01 | 4 |
| 2 | VALUES SCAN | | 1 | 3 | 6 (0)| 00:00:01 | 3 |00:00:00.01 | 0 |
-------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('23.1.0')
DB_VERSION('23.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"INS$1")
LOAD_METHOD(@"INS$1" "T1"@"INS$1" CONVENTIONAL)
LOAD_TYPE(@"INS$1" "T1"@"INS$1" SERIAL)
FULL(@"INS$1" "T1"@"INS$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - STRDEF[22], STRDEF[10]
Query Block Registry:
---------------------
INS$1 (PARSER) [FINAL]
SEL$1 (PARSER) [FINAL]
SEL$2 (PARSER) [FINAL]
SEL$3 (PARSER) [FINAL]
SET$1 (PARSER) [FINAL]
54 rows selected.
|
autotrace 처럼 처음보는 VALUES SCAN 라는 실행계획이 표시되고
Column Projection Information에 STRDEF라고 표시됨
Query Block Name 에는 쿼리블록이 SET$1만 표시되는데
Query Block Registry에는 총 5개가 표시됨
(INS$1 (PARSER) [FINAL], SEL$1 (PARSER) [FINAL], SEL$2 (PARSER) [FINAL], SEL$3 (PARSER) [FINAL], SET$1 (PARSER) [FINAL])
insert 1건(INS$1 (PARSER)), select 3건(SEL$1~3 (PARSER) [FINAL]), 집합 연산자 사용 1건(SET$1 (PARSER) [FINAL])
이렇게 표시됨 내부적으로는 union all을 한것으로 보임
5_3. 10046 trace
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
|
SQL>
alter session set events '10046 trace name context forever, level 12';
insert into t1 values (4, 'test4'), (5, 'test5'), (6, 'test6');
alter session set events '10046 trace name context off';
$ cd /opt/oracle/diag/rdbms/free/FREE/trace
$ tkprof FREE_ora_79831.trc FREE_ora_79831.txt sys=no
$ cat FREE_ora_79831.txt
********************************************************************************
SQL ID: 31uu8vmn4twsr Plan Hash: 1
insert into t1
values
(4, 'test4'), (5, 'test5'), (6, 'test6')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 1 15 0 0
Execute 1 0.00 0.00 0 30 49 3
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 1 45 49 3
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 139
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL T1 (cr=5 pr=0 pw=0 time=163 us starts=1 direct read=0 direct write=0)
3 3 3 VALUES SCAN (cr=0 pr=0 pw=0 time=4 us starts=1 direct read=0 direct write=0 cost=6 size=0 card=3)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
log file sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
|
10046 트레이스에서는 특별한 내용은 보이지 않음
다만 10046 트레이스에 Row Source Operation 세부항목에 direct read와 direct write 항목도 새로 추가된듯함
5_4. 10053 trace
|
1
2
3
4
5
6
7
8
|
SQL>
alter session set events '10053 trace name context forever, level 12';
insert into t1 values (4, 'test4'), (5, 'test5'), (6, 'test6');
alter session set events '10053 trace name context off';
$ cd /opt/oracle/diag/rdbms/free/FREE/trace
$ vi FREE_ora_79377.trc
Final query after transformations: qb INS$1 (#0):******* UNPARSED QUERY IS *******
SELECT 0 FROM "SYS"."T1" "T1"
|
Final query를 검색해서 보면 insert 가 아닌 select 구문이 존재함
트레이스 전체를 다시 확인하여 UNION ALL 구문을 찾아냄
|
1
2
3
|
$ vi FREE_ora_79377.trc
qb SET$1 (#0):******* UNPARSED QUERY IS *******
(SELECT 4 "4",'test4' "'test4'" FROM "SYS"."DUAL" "DUAL") UNION ALL (SELECT 5,'test5' FROM "SYS"."DUAL" "DUAL") UNION ALL (SELECT 6,'test6' FROM "SYS"."DUAL" "DUAL")
|
데이터 삽입시 insert values (다중 values) 를 사용했지만
내부적으로는 아래와 같이 union all 을 사용한 방식으로 insert가 동작하는듯함
|
1
2
3
4
5
6
7
8
9
|
SQL>
insert into t1
(select 4, 'test4' from dual)
union all
(select 5, 'test5' from dual)
union all
(select 6, 'test6' from dual);
3 rows created.
|
결론 :
오라클 23ai의 Table Value Constructor 기능 덕분에 insert, select, merge 작업을 더욱 간편하고 효율적으로 처리할 수 있게됨
이 기능을 잘 활용하면 SQL을 좀더 직관적으로 작성할수 있기 때문에 코드 생산성과 간결성, 가독성이 크게 올라갈것으로 보임
트레이스 확인결과 내부적으로는 기존 방식처럼 select from dual 구문을 union all 방식을 사용해 묶어서 데이터를 처리하는 방식을 이용하는듯함
그렇기 때문에 한번에 많은값을 넣을 경우 다중 union all로 인한 파싱 부하를 고려해서 사용해야 할듯함
참조 :
https://apex.oracle.com/pls/apex/features/r/dbfeatures/features?feature_id=1711
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/IN-Condition.html#SQLRF-GUID-C7961CB3-8F60-47E0-96EB-BDCF5DB1317C
https://www.youtube.com/watch?v=LIwm_UDAHBE
https://medium.com/@vbalebai/oracle-23c-sql-syntax-for-efficient-data-manipulation-value-constructor-8a776e49f4d
https://oracle-base.com/articles/23/table-values-constructor-23
https://positivemh.tistory.com/1108
https://positivemh.tistory.com/1109
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 23ai 신기능 테이블 엑세스 기록 확인 뷰(dba_table_access_stats) (0) | 2024.06.22 |
|---|---|
| 오라클 23ai 10046 트레이스 이전 버전과 차이점 비교 (0) | 2024.06.20 |
| 오라클 19c 인덱스 리빌드 전후 용량 차이 확인 테스트 (0) | 2024.06.17 |
| 오라클 23ai 신기능 UMM(Unified Memory Management) 통합메모리 관리 (0) | 2024.06.14 |
| 오라클 19c 테이블 initrans 값에 따른 lock 테스트 (0) | 2024.06.13 |