중급 (4-1)서브쿼리1, IN, subquery&join, ALTER SESSION, 괄호&FROM&WHERE
---
https://youtube.com/playlist?list=PLyQR2NzLKOCbOYNJHKpCax4ija38yCqBN
이거 보는 중
---
https://finepiz.tistory.com/218 참고
: group by, having, join, sub query, in, sub query&join, union, minus(not in), intersect, exists
->where절만으로 안되는 경우가 있더라. where price = max(price)이런거 쓰려면 sub query로 select max(price) from book; 이렇게 넣어줘야
->join으로 같은 결과를 낼 수 있다
https://velog.io/@zoo4we/subquery 서브쿼리 개념 참고
---
(4/44)
https://youtu.be/oc-ya1MpK5c - 서브쿼리 한방에 정리해 줌
서브쿼리는 쓰이는 위치에 따라서 3가지 종류
(4/44)where절에 사용하는 중첩 subquery - 1탄
(9/44)from절에 사용하는 inline view - 2탄
(8/44)select절에 사용 : scalar subquery - 3탄
오늘은 (4/44)where절에 사용하는 중첩 subquery
서브쿼리는 쿼리 안에 있는 또 다른 쿼리를 의미
바깥 쿼리 메인 쿼리. 안쪽 쿼리 서브 쿼리.
실습 : HR 계정
이 두개의 테이블을 갖고 서브쿼리를 작성해 볼 것
EMPLOYEES
DEPARTMENTS
둘다 department_id 있으니 이걸로 서브쿼리로 줘볼게
WHERE절이 where a.department_id = (department_id를 return하는 서브쿼리) 식이 되는 것.
보면 일단 만들어본 서브쿼리의 결과가
하나야. 이렇게 하나의 행을 리턴하는 서브쿼리를 단일행 서브쿼리. equal조건으로 쓰였을 때 아무 문제 없이 실행됨
EMPLOYEES에 없는 LOCATION_ID 컬럼의 값으로 EMPLOYEES의 레코드를 뽑아냄
두건이 나옴. 이 query의 뜻은
▽
A의 컬럼을 모두 볼건데,
A, B 둘다 DEPARTMENT_ID이 있고,
B에서 LOCATION_ID의 값이 1800인 것의 DEPARTMENT_ID값인 애의 A의 컬럼을 모두다 보자
□
" 'LOCATION_ID'가 1800인 '부서의 아이디'를 SELECT한 다음에 그 부서의 ID "
를 가지는 사원을 최종적으로 뽑아내는 QUERY
---
서브쿼리가 리턴하는 행이 여러건이면?
값을 1700으로 하면
여러건이야. 이때 equal조건으로 들어가면
에러가 난다
single row 서브쿼리가 원래 single row가 리턴이 돼야 하는데 하나 이상의 row가 리턴이 됐다는 뜻
대신 in으로 쓰면 에러가 나지 않아. in으로 쓰면 location_id를 가지는 부서를 갖는 사원들을 모두 출력하는 거니까
▽어떤 지역의 부서의 사원들을 모두 출력하는 거네
---
*개발자들이 잘못 알고 있는게 서브쿼리는 성능의 문제와는 무관하다
왜냐면 서브쿼리로 짜든 join으로 풀어서 짜든 어떤것이 유리한 지를 옵티마이저가 알아서 판단해서 실행계획을 작성해 주기 때문
증명하기 위해 앞서 작성했던 서브쿼리를 조인으로 풀어보고 실행 계획을 비교해보자
같은 결과가 나오게 JOIN문으로 바꿀 수 있다
ALTER SESSION SET STATISTICS_LEVEL=ALL; 쿼리 실행(https://pangsun.co.kr/entry/STATISTICS-LEVEL : 캡쳐 레벨)
> 서브쿼리 실행 > 실행계획 쿼리(▽ALLSTATS LAST니까 마지막에 한걸 실행계획 보겠다는건가?) 순으로 실행
서브쿼리인데 그냥 조인으로 풀렸다
HASH JOIN으로 풀렸고 Buffers가 8개
두번째 join문도 실행계획 보면
똑같이 생김. 옵티마이저가 같은식으로 풀었다
NO_UNNEST라는 힌트를 줘서 풀지말고 강제로 할 수 있다(▽조인을 푼다? UNNEST라는 단어니까 푼다고 표현하나)
해보면
아까 해쉬 조인이었는데 지금은 바뀌었고 밑에 보면 location_id가 1700이라는게 filter로 풀었다
28개의 버퍼를 읽었다
옵티마이저가 판단한 것.
서브쿼리로 하면 이렇게 많이 읽고,
join으로 풀면 더 적게 읽으니까 더 효율이고 그럼 풀어야겠다 생각하고 알아서 풀은 것
결론 : 개발할 때 함부로 힌트를 넣으면 안되겠구나.
---
보통의 경우에는 서브쿼리를 쓰든 조인을 쓰든 성능에는 문제가 없지만
서브쿼리를 '반드시' 쓰면 안되는 경우가 있다
SALARY가 최고인 사람과 최저인 사람을 한번 뽑아볼 것
이렇게, 여기서 문제점은 뭘까?
아까랑은 다르게 서브쿼리에 있는 테이블이 메인쿼리와 같다
하나의 쿼리 안에서 한 테이블을 3번 액세스 했다. 그럼 굉장히 비효율 왜냐면? 잠시 후에 볼 것
그럼 이걸 어떻게 작성해야되냐?
예전에 RANK함수 배웠었죠? 그때 ROW_NUMBER를 배웠을 거예요(7/44)
ROW_NUMBER() 이렇게 써놓고 어떻게 하면 위랑 똑같은 쿼리가 될까 생각하는 것
저번에 한 것처럼 이 테이블의 결과를 감싸고 FROM절에 두고 WHERE로 거르기
위와 아래가 같은 쿼리. 실행 계획으로 성능차이 확인해보자
위
ACCESS 세번 버퍼 18개
ACCESS 한번 버퍼 6개
= 3배의 성능 차이. 이런 경우엔 서브쿼리를 쓰면 안된다~!