[SQLP필기풀이]6장 고급SQL튜닝(6) 고급 SQL 활용

p.2권 121 ~ 123

✍️ 63,64번 : With 구문

🍒 문제 해설

✅ with 절을 처리하는 DBMS 내부 실행방식 2가지

• Materialize 방식
  • 내부적으로 임시 테이블을 만든다.
  • 만들어진 임시 테이블은 반복 재사용이 가능하다.
  • 실행계획에 'TEMP TABLE TRANSFORMATION' 라고 뜬다.
  • 'materialize' 힌트를 사용한다.
• Inline 방식
  • 참조된 횟수만큼 런타임시 반복 수행된다.
  • 임시테이블이 생성되지 않는다.
  • 실행계획에 'TEMP TABLE TRANSFORMATION' 라고 뜨지 않는다.
  • 'Inline' 힌트를 사용한다.

✍️ 65번 : With절 성능

With절 성능에 대한 설명으로 가장 부적절한 것

1. With절에서 참조하는 원본 집합과 결과집합의 크기 차이가 없을 때 Materialize 방식으로 수행하면 성능에 도움이 되지 않을 뿐만 아니라 오히려 성능이 나빠질 수 있다. 👉 ⭕️
2. WITH 절에서 처리하는 원본 집합이 작고 동시 수행 빈도가 높을 때 Materialize 방식으로 수행하면 성능에 좋다. 👉 ❌
3. 매우 많은 데이터를 읽어 GROUP BY , 조인 등을 통해 집합 크기를 많이 줄일 수 있을 때 WITH절을 Materialize 방식으로 수행하면 성능에 좋다. 👉 ⭕️
4. 절자척 프로그램 패턴(Loop 돌면서 한 건씩 처리하는 패턴)을 집합적인 처리 패턴으로 전환하고자할 때 WITH절을 사용하면 성능 향상에 큰 도움이 된다. 👉 ⭕️

🍋 기출 포인트

1. Materialize 방식은 중간 집합을 만드는 과정에 원본 집합을 읽어 Temp 공간에 기록하는 과정을 수반하면서 DISK I/O가 발생한다.따라서 원본집합이 작고 동시 수행 빈도가 높을 때에 사용하면 오히려 성능이 저하된다.

🍒 문제 해설

✅ Materialize 방식

• 중간 집합을 만드는 과정에 원본 집합을 읽어 Temp 공간에 기록하는 과정을 수반하면서 DISK I/O가 발생한다.
  • 따라서 원본집합이 작고 동시 수행 빈도가 높을 때에 사용하면 오히려 성능이 저하된다.
  • 매우 많은 데이터를 읽어 group by , 조인 등을 통해 집합 크기를 많이 줄일 수 있을 때 수행하면 성능 개선에 도움이 된다.
• 해당 뷰를 읽을 때에도 버퍼 캐시를 경유하여 읽는다.