Oracle 23c부터 UPDATE 문과 DELETE 문에 대한 직접 조인이 가능해졌습니다.
Join the target table in UPDATE and DELETE statements to other tables using the FROM clause. These other tables can limit the rows changed or be the source of new values. Direct joins make it easier to write SQL to change and delete data.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE TABLE t1 (c1, c2) AS SELECT ROWNUM, ROWNUM FROM XMLTABLE ('1 to 1000000');
CREATE TABLE t2 (c1, c2) AS SELECT ROWNUM, ROWNUM FROM XMLTABLE ('1 to 1000000');
CREATE INDEX t2_x1 ON t2 (c1);
아래 UPDATE 문은 FROM 절로 t2 테이블을 직접 조인니다. 조인 방식과 조인 순서와 SELECT 문과 동일한 방법으로 제어할 수 있습니다.
-- 2-1
UPDATE t1 t
SET t.c1 = s.c2
FROM t2 s
WHERE t.c1 <= 100
AND s.c1 = t.c2;
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 100 | 674 (1)|
| 1 | UPDATE | T1 | | |
| 2 | NESTED LOOPS | | 100 | 674 (1)|
| 3 | NESTED LOOPS | | 100 | 674 (1)|
|* 4 | TABLE ACCESS FULL | T1 | 100 | 374 (2)|
|* 5 | INDEX RANGE SCAN | T2_X1 | 1 | 2 (0)|
| 6 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 3 (0)|
--------------------------------------------------------------------
-- 2-2
UPDATE /*+ LEADING(T) USE_HASH(S) */
t1 t
SET t.c1 = s.c2
FROM t2 s
WHERE t.c1 <= 100
AND s.c1 = t.c2;
---------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
---------------------------------------------------------
| 0 | UPDATE STATEMENT | | 100 | 751 (2)|
| 1 | UPDATE | T1 | | |
|* 2 | HASH JOIN | | 100 | 751 (2)|
|* 3 | TABLE ACCESS FULL| T1 | 100 | 374 (2)|
| 4 | TABLE ACCESS FULL| T2 | 1000K| 373 (2)|
---------------------------------------------------------
3-1번 UPDATE 문은 직접 조인에 의해 한 로우가 같은 값으로 2번 갱신되어 에러가 발생하지 않았습니다. 3-2번 UPDATE 문은 한 로우가 다른 값으로 갱신되어 에러가 발생니다. 3-3번 UPDATE 문처럼 갱신 테이블과 조인 테이블의 관계 차수가 1:1인 경우에만 직접 조인을 사용하는 편이 바람직합니다.
-- 3-1
INSERT INTO t2 VALUES (1, 1);
UPDATE t1 t
SET t.c1 = s.c2
FROM t2 s
WHERE t.c1 <= 100
AND s.c1 = t.c2;
101 행이 업데이트되었습니다.
ROLLBACK;
-- 3-2
INSERT INTO t2 VALUES (1, 2);
UPDATE t1 t
SET t.c1 = s.c2
FROM t2 s
WHERE t.c1 <= 100
AND s.c1 = t.c2;
ORA-30926: 동일한 행을 업데이트하려고 시도했습니다.
ROLLBACK;
-- 3-3
INSERT INTO t2 VALUES (1, 2);
UPDATE t1 t
SET t.c1 = s.c2
FROM (SELECT c1, MAX (c2) AS c2 FROM t2 GROUP BY c1) s
WHERE t.c1 <= 100
AND s.c1 = t.c2;
100 행이 업데이트되었습니다.
ROLLBACK;
아래 DELETE 문도 FROM 절로 t2 테이블을 직접 조인합니다. UPDATE 문과 달리 DELETE 문은 조인 대상의 값을 직접 참조하지 않으므로 서브쿼리를 사용하는 것에 비해 장점이 없어 보입니다. 다만 서브쿼리가 Filter 방식으로 수행되는 경우 성능 개선을 위해 직접 조인을 사용할 수 있습니다.
-- 4-1
DELETE t1 t
FROM t2 s
WHERE t.c1 <= 100
AND s.c1 = t.c2;
----------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------
| 0 | DELETE STATEMENT | | 100 | 574 (2)|
| 1 | DELETE | T1 | | |
| 2 | NESTED LOOPS | | 100 | 574 (2)|
|* 3 | TABLE ACCESS FULL| T1 | 100 | 374 (2)|
|* 4 | INDEX RANGE SCAN | T2_X1 | 1 | 2 (0)|
----------------------------------------------------------
-- 4-2
DELETE t1 t
WHERE t.c1 <= 100
AND EXISTS (SELECT 1
FROM t2 s
WHERE s.c1 = t.c2);
----------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------
| 0 | DELETE STATEMENT | | 100 | 574 (2)|
| 1 | DELETE | T1 | | |
| 2 | NESTED LOOPS SEMI | | 100 | 574 (2)|
|* 3 | TABLE ACCESS FULL| T1 | 100 | 374 (2)|
|* 4 | INDEX RANGE SCAN | T2_X1 | 1 | 2 (0)|
----------------------------------------------------------
참고로 DELETE 문은 삭제 테이블과 조인 테이블의 관계 차수가 1:M이어도 에러가 발생하지 않습니다.
-- 5-1
INSERT INTO t2 VALUES (1, 1);
DELETE t1 t
FROM t2 s
WHERE t.c1 <= 100
AND s.c1 = t.c2;
100 행이 삭제되었습니다.
ROLLBACK;
-- 5-2
INSERT INTO t2 VALUES (1, 2);
DELETE t1 t
FROM t2 s
WHERE t.c1 <= 100
AND s.c1 = t.c2;
100 행이 삭제되었습니다.
ROLLBACK;