21) Join Update & Join Delete

Use Case

• 다른 테이블의 컬럼 값을 참조해서 Update / Delete 하고 싶은 경우
• 한 번에 여러 테이블에 대해 Update / Delete 하고 싶은 경우

 

Join Update

• 첫번째 쿼리
  • 특정 회사의 상품 수수료율이 변경되었을 때 그 회사의 제품들의 가격을 update하는 쿼리
  • 다른 테이블의 컬럼 값ㅇ르 참조해서 실제 작업 대상 테이블의 데이터를 업데이트하는 경우
• 두번째 쿼리
  • product, order table에 상품명을 저장하려고 한다.
  • join후에 한번의 쿼리로 상품명을 update 하였다.
• SET 절에 update 대상 컬럼들을 명시한다.
• 쿼리에서 참조하고 있는 테이블들 중 "전체" 또는 "일부"에 대해 컬럼 값 업데이트가 가능하다
• Left Join등 다른 유형의 join들도 사용이 가능하다.

쿠폰 만료일을 update하는 쿼리 비교문이다. 일일이 update를 날리기보다는

Values라는 table constructor와 Row constructor를 이용하여 업데이트 해야하는 쿠폰id와 만료일자 값을 하나의 세트로 묶어서 테이블 형태의 데이터로 만들 수 있다.

이를 join 하여 한번에 처리하는 예시이다.

 

Join Delete

• 첫번째 쿼리
  • user log 와 user table 을 join해서 6개월 동안 active하지않은 user 로그를 삭제한다.
  • 이처럼 삭제 대상 테이블과 연관된 다른 테이블의 컬럼 값을 참조해서 데이터를 삭제할 수 있다.
• 두번쨰 쿼리
  • category 테이블과 product 테이블에 Fruits과 연관된 row들을 삭제한다.
• Delete ... From 절 사이에 데이터 삭제 대상 테이블 목록을 명시해야한다.
• 쿼리에서 참조하고 있는 테이블들 중 "전체" 또는 "일부" 에 대해 삭제 가능하다.
• Left Join 등 다른 유형의 join들도 사용이 가능하다.

 

Using Optimzier Hint

• 첫번째 쿼리
  • 실행계획을 보면 category table을 name 컬럼에 대한 인덱스를 활용해 먼저 접근하고
  • 그후 product table을 join하는것을 알 수 있다.
• 두번째 쿼리
  • JOIN_FIXED_ORDER() => query의 From 절에 지정된 테이블 순서로 join을 수행하라는 것을 의미한다.
  • Straight Join 힌트와 동일한 역할이다.
  • product table을 먼저 접근한다.

 

주의사항

• 참조하는 테이블들의 데이터에는 읽기 잠금 (S lock)이 발생하므로 잠금 경합이 발생할 수 있다.
  • dml은 X lock이 잠기는것도 동일하다.
• join update의 경우 조인되는 테이블들의 관계가 1:N일 때 , N 테이블의 컬럼 값을 1테이블에 update하는 경우 예상과는 다르게 처리 될수 있다. (N:M 관계도 마찬가지)
  • N테이블의 컬럼값중에 어떤것을 1테이블 값으로 해야할지 알기가 어려움
• Join update & Join Delete 쿼리는 단일 쿼리보다 쿼리 형태가 복잡하므로 ,
  반드시 사전에 쿼리 실행계획 확인 필요하다.