Direct Joins for UPDATE and DELETE Statements(직접 조인을 사용한 업데이트, 딜리트)
기존에 조인을 통해 UPDATE와 DELETE를 할 경우
SELECT와 같지 않아 번거롭게 작성을 해야하는 불편함이 있었다.
이번 23c 버전에서 이런 부분을 개선한 방식을 소개하고 있다.
Direct Join for UPDATE and DELETE 이다.
내용에 대해서 테스트해 보고 사용방법을 알아보도록 하겠다.
먼저 UPDATE 부분을 테스트 해보자.
사용 방법을 한번 확인해 보자.
|
1
2
3
4
5
6
7
8
|
14:18:31 SQL> UPDATE employees e
14:18:33 2 SET e.salary = j.max_salary
14:18:33 3 FROM jobs j
14:18:33 4 WHERE j.job_id = e.job_id;
107 행이 업데이트되었습니다.
경 과: 00:00:00.01
|
차이점이 확인이 되나요?
SET 절 다음에 FROM 절을 위치시키고 조인을 활용할 수 있게 되었다.
이 부분이 개발자나 사용자가 많이 원하던 부분이였다. 간편해 보이네요.
원래는 아래와 같이 사용했었다.
|
1
2
3
4
5
6
7
8
9
10
11
|
14:24:52 SQL> UPDATE employees e
14:24:52 2 SET e.salary = (SELECT j.max_salary
14:24:52 3 FROM jobs j
14:24:52 4 WHERE j.job_id = e.job_id)
14:24:52 5 WHERE EXISTS (SELECT 1
14:24:52 6 FROM jobs j
14:24:52 7 WHERE j.job_id = e.job_id);
107 행이 업데이트되었습니다.
경 과: 00:00:00.01
|
혹은 MERGE 문장을 사용해서 구현을 했었다. 많이 편해졌다.
그럼 3개 테이블에 대한 조인을 활용할 수 있을까?
|
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
|
14:45:05 SQL> update employees e
14:45:08 2 set hire_date = to_date('20230101','YYYYMMDD')
14:45:08 3 from departments d, jobs j
14:45:08 4 where e.DEPARTMENT_ID = d.DEPARTMENT_ID
14:45:08 5 and e.JOB_ID = j.JOB_ID
14:45:08 6 and d.LOCATION_ID = 1500
14:45:08 7 and j.MAX_SALARY > 8000;
5 행이 업데이트되었습니다.
경 과: 00:00:00.01
14:47:11 SQL> select e.employee_id, e.first_name, e.last_name, e.hire_date
14:47:16 2 from employees e, departments d, jobs j
14:47:16 3 where e.DEPARTMENT_ID = d.DEPARTMENT_ID
14:47:16 4 and e.JOB_ID = j.JOB_ID
14:47:16 5 and d.LOCATION_ID = 1500
14:47:16 6 and j.MAX_SALARY > 8000;
EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DAT
----------- ---------- ---------- --------
120 Matthew Weiss 23/01/01
121 Adam Fripp 23/01/01
122 Payam Kaufling 23/01/01
123 Shanta Vollman 23/01/01
124 Kevin Mourgos 23/01/01
5 행이 선택되었습니다.
경 과: 00:00:00.00
|
위와 같이 사용 가능한 것을 확인하고 UPDATE 결과도 확인할 수 있다.
DELETE 문장은 가볍게 살펴보도록 하겠다.
|
1
2
3
4
5
6
7
8
9
10
|
15:05:10 SQL> delete from employees e
15:06:32 2 from departments d, jobs j
15:06:32 3 where e.DEPARTMENT_ID = d.DEPARTMENT_ID
15:06:32 4 and e.JOB_ID = j.JOB_ID
15:06:32 5 and d.LOCATION_ID = 1500
15:06:32 6 and j.MAX_SALARY > 8000;
5 행이 삭제되었습니다.
경 과: 00:00:00.02
|
위와 같이 사용이 가능한 것을 확인할 수 있다.
하지만 뭔가 좀 어색하다. FROM 절 뒤에 FROM 절... 조금은 어색하다.
아래와 같이 해보자.
|
1
2
3
4
5
6
7
8
9
10
|
15:06:53 SQL> delete employees e
15:07:39 2 from departments d, jobs j
15:07:39 3 where e.DEPARTMENT_ID = d.DEPARTMENT_ID
15:07:39 4 and e.JOB_ID = j.JOB_ID
15:07:39 5 and d.LOCATION_ID = 1500
15:07:39 6 and j.MAX_SALARY > 8000;
5 행이 삭제되었습니다.
경 과: 00:00:00.01
|
이렇게 사용하면 어색하지는 않으면서 제대로 동작을 한다.
참고하기 바란다.
기능과 별도로 문장 수행에 따른 플랜도 한번 확인해 보도록 하겠다.
|
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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
|
14:18:31 SQL> UPDATE employees e
14:18:33 2 SET e.salary = j.max_salary
14:18:33 3 FROM jobs j
14:18:33 4 WHERE j.job_id = e.job_id;
107 행이 업데이트되었습니다.
경 과: 00:00:00.01
Plan hash value: 2403913691
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 107 | 2675 | 6 (17)| 00:00:01 |
| 1 | UPDATE | EMPLOYEES | | | | |
| 2 | MERGE JOIN | | 107 | 2675 | 6 (17)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 228 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 107 | 1391 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1391 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("J"."JOB_ID"="E"."JOB_ID")
filter("J"."JOB_ID"="E"."JOB_ID")
14:24:52 SQL> UPDATE employees e
14:24:52 2 SET e.salary = (SELECT j.max_salary
14:24:52 3 FROM jobs j
14:24:52 4 WHERE j.job_id = e.job_id)
14:24:52 5 WHERE EXISTS (SELECT 1
14:24:52 6 FROM jobs j
14:24:52 7 WHERE j.job_id = e.job_id);
107 행이 업데이트되었습니다.
경 과: 00:00:00.01
Plan hash value: 3806721702
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 107 | 3531 | 6 (0)| 00:00:01 |
| 1 | UPDATE | EMPLOYEES | | | | |
|* 2 | HASH JOIN OUTER | | 107 | 3531 | 6 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 107 | 2247 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1391 | 3 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN| JOB_ID_PK | 1 | 8 | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | JOBS | 19 | 228 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("J"."JOB_ID"(+)="E"."JOB_ID")
5 - access("J"."JOB_ID"="E"."JOB_ID")
SQL> explain plan for
2 UPDATE /*+ USE_NL(e j) */ hr.employees e
3 SET e.salary = j.max_salary
4 FROM hr.jobs j
5 WHERE j.job_id = e.job_id;
해석되었습니다.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2286293482
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 107 | 2675 | 22 (0)| 00:00:01 |
| 1 | UPDATE | EMPLOYEES | | | | |
| 2 | NESTED LOOPS | | 107 | 2675 | 22 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 114 | 2675 | 22 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | JOBS | 19 | 228 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_JOB_IX | 6 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 6 | 78 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("J"."JOB_ID"="E"."JOB_ID")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
4 - UPD$1 / "J"@"UPD$1"
U - USE_NL(e j)
25 행이 선택되었습니다.
|
direct join을 사용한 update의 플랜을 살펴 봤으며 기능이 나오기 전 문장에 대한 플랜도 확인해 봤다.
그리고 direct join을 사용할 때 힌트를 사용한 부분을 확인해 봤다.
힌트로 플랜 변경이 가능한 것도 확인된다.
Hint Report 부분에 보면 Unused 로 U 마크가 있지만 해당 힌트가 사용된 것으로 확인된다.
여기에서 이야기 하는 Unused는 어떤 부분인지 추후에 확인할 기회가 있으면 확인해 보겠다.
댓글 0
| 번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
|---|---|---|---|---|
| 11 | (23c 신기능) GROUP BY 절에 컬럼 별칭이나 포지션 사용 | 명품관 | 2023.04.26 | 965 |
| 10 | (23c 신기능) UPDATE 문장을 위한 Default 값 NULL 설정 가능 | 명품관 | 2023.04.26 | 613 |
| 9 | (23c 신기능) PL/SQL 에서 SQL로 Transpiler 자동변환(Automatic PL/SQL to SQL Transpiler) | 명품관 | 2023.04.25 | 266 |
| 8 | (23c 신기능) INTERVAL 데이터타입에 집계함수와 분석함수 사용 가능 | 명품관 | 2023.04.20 | 363 |
| 7 | (23c 신기능) 23c에서 개발자를 위해 새로 추가된 DB_DEVELOPER_ROLE role | 명품관 | 2023.04.19 | 259 |
| 6 | (23c 신기능) IF EXISTS와 IF NOT EXISTS 사용 | 명품관 | 2023.04.18 | 1467 |
| 5 | (23c 신기능) Annotation 사용하기(comment와 유사한) | 명품관 | 2023.04.18 | 220 |
| » | (23c 신기능) Direct Joins for UPDATE and DELETE Statements(직접 조인을 사용한 UPDATE, DELETE) | 명품관 | 2023.04.12 | 212 |
| 3 | JSON-Relational Duality View 튜토리얼 | 명품관 | 2023.04.10 | 173 |
| 2 | 오라클 프로세스 prefix가 ora_ 에서 db_로 변경 | 명품관 | 2023.04.06 | 206 |
| 1 | Oracle Database 23c Free Install with Oracle Linux 8.2(리눅스 8.2 버전에서 오라클 23c 설치) [1] | 명품관 | 2023.04.05 | 1599 |