메뉴 건너뛰기

Korea Oracle User Group

23c Free Developer Release

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는 어떤 부분인지 추후에 확인할 기회가 있으면 확인해 보겠다.

 

위로