배송 완료를 주문에 반영하는 조인 뷰 UPDATE가 ORA-01779로 막힌다

모범 답안 SQL ​

-- ① 설계서의 1:1을 믿지 않고 먼저 센다 (0건이어야 다음으로 간다)
SELECT order_id, COUNT(*)
  FROM shipping
 GROUP BY order_id
HAVING COUNT(*) > 1;

-- ② 0건이면 유일성을 선언한다 (조인 키에 유일 인덱스)
CREATE UNIQUE INDEX ux_shipping_order_id ON shipping (order_id);

-- ③ 이제 orders가 키 보존 테이블이다. 두 테이블을 한 번씩 읽고 해시 조인한다
UPDATE (SELECT o.status AS o_status, s.status AS s_status
          FROM orders o, shipping s
         WHERE s.order_id = o.order_id
           AND o.status = 'SHIPPED'
           AND s.status IN ('DELIVERED', 'RETURNED'))
   SET o_status = s_status;

개선 후 실행계획 학습용 예시 ​

----------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT     |          |      1 |        |      0 |00:00:00.04 |    6008 |
|   1 |  UPDATE              | orders   |      1 |        |      0 |00:00:00.04 |    6008 |
|*  2 |   HASH JOIN          |          |      1 |   2918 |   2816 |00:00:00.02 |     376 |
|*  3 |    TABLE ACCESS FULL | orders   |      1 |   5818 |   3410 |00:00:00.01 |     193 |
|*  4 |    TABLE ACCESS FULL | shipping |      1 |  29870 |  29870 |00:00:00.01 |     183 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("S"."ORDER_ID"="O"."ORDER_ID")
   3 - filter("O"."STATUS"='SHIPPED')
   4 - filter(("S"."STATUS"='DELIVERED' OR "S"."STATUS"='RETURNED'))

  중복 검사 0건 · 유일 인덱스 생성 0.1초 · 갱신 2,816행

모범 답안 서술 ​

원본은 EXISTS를 해시 세미 조인으로 풀어 대상 2,816건을 고르는 데까지는 376블록이면 된다(2번 줄). 비싼 것은 SET 절의 서브쿼리다. shipping.order_id에 인덱스가 없어서, 고치는 주문 한 건마다 shipping 전체 183블록을 읽는다. 5번 줄 Starts 2,816 × 183블록 = 515K가 Buffers의 거의 전부다. 조인 뷰 UPDATE가 ORA-01779로 막힌 것은 오라클이 키 보존 여부를 데이터가 아니라 제약과 인덱스로 판단하기 때문이다. orders 칸을 고치려면 조인 결과의 한 행이 orders의 한 행에만 대응해야 하고, 그러려면 조인 상대인 shipping.order_id가 유일하다는 것이 PK, UNIQUE 제약, 유일 인덱스 가운데 하나로 선언돼 있어야 한다. 지금은 shipping의 PK가 shipping_id이고 order_id에는 아무것도 없으니, 실제 데이터가 1:1이어도 오라클은 그것을 알 수 없다. 그래서 먼저 GROUP BY order_id HAVING COUNT(*) > 1로 중복을 센다. 이커머스는 FK가 꺼져 있어 설계서와 데이터가 다를 수 있다. 0건이면 shipping(order_id)에 유일 인덱스를 만든다. 그러면 orders가 키 보존 테이블이 되어 조인 뷰 UPDATE가 동작하고, 두 테이블을 한 번씩 읽어 해시 조인한다. Buffers는 521K → 약 6,000으로 준다. 대가도 있다. 유일 인덱스가 생기면 shipping INSERT마다 인덱스를 하나 더 고치고, 같은 주문을 두 번 배송하는(재배송) 업무가 생기면 그 INSERT가 ORA-00001로 막힌다. 재배송이 업무상 있을 수 있다면 인덱스 대신 MERGE를 쓰고, 원천에서 주문마다 한 행을 고르는 규칙(마지막 배송)을 정해야 한다.

해설 ​

• MERGE는 키 보존을 따지지 않는다. MERGE INTO orders o USING shipping s ON (o.order_id = s.order_id) WHEN MATCHED THEN UPDATE SET o.status = s.status WHERE o.status = 'SHIPPED' AND s.status IN (…)은 인덱스 없이도 돈다. 대신 실행 중에 같은 주문에 배송 두 행이 맞물리면 ORA-30926으로 멈춘다. 검사가 정의 시점이 아니라 실행 시점에 있다는 차이다.
• BYPASS_UJVC 힌트로 ORA-01779를 피하던 시절이 있었다. 문서화되지 않은 힌트이고 최신 버전에서는 동작하지 않는다. 중복이 있으면 어느 배송 행의 값이 남을지 정해지지 않으므로 쓰지 않는다.
• 이 유일 인덱스는 나중에 shipping에서 orders로 가는 FK를 켤 때 자식 쪽 조인 키 인덱스 역할도 한다. 인덱스 없는 FK는 부모를 지우거나 고칠 때 자식 테이블 잠금을 부른다(LOCK 단원).
• 원본도 shipping.order_id 인덱스만 있으면 5번 줄이 행마다 3블록 안팎으로 줄어든다(약 8K). 그래도 대상 행마다 찾아가는 구조는 남는다.

관련 교재 ​

• 18화 · 튜너가 되는 날
• 14화 · 야간 배치가 아침을 넘긴다