[SQLP필기풀이]4장 조인튜닝(3)-스칼라서브쿼리

✍️ 38번 : 스칼라 서브쿼리를 일반 조인문으로 변환

스칼라 서브쿼리를 사용한 아래 SQL을 일반 조인문으로 변환할 때, 정확한 결과집합을 보장하면서 성능에도 유리한 것을 고르시오.

SELECT 주문번호, 주문일자, 고객번호, 주문금액, 배송지 ,
(SELECT 고객명 FROM 고객 WHERE 고객번호 = 0.고객번호) 고객명 ,
(SELECT 결제구분명 FROM 결제구분 WHERE 결제구분코드 = 0.결제구분코드) 결제구분명
FROM 주문 O
WHERE 주문일자 BETWEEN :ORD_DT1 AND :ORD_DT2 ;

1. **** 👉 ❌

① SELECT 0. 주문번호, 0.주문일자, 0.고객번호, 0.주문금액, 0.배송지
, C.고객명, S.결제구분명
FROM 주문 0, 고객 C. 결제구분 S
WHERE 0. 주문일자 BETWEEN :ORD_DTI AND:ORD_DT2
AND C.고객번호 = 0.고객번호
AND S. 결제구분코드 = 0. 결제구분코드

2. **** 👉 ❌

SELECT 0. 주문번호, 0. 주문일자, 0.고객번호, 0.주문금액, 0. 배송지
, C.고객명, S. 결제구분명
FROM 주문 0, 고객 C, 결제구분 S
WHERE 0. 주문일자 BETWEEN :ORD_DT1 AND ORD_DT2
AND C. 고객번호(+) = 0.고객번호
AND S. 결제구분코드(+) = 0. 결제구분코드

3. **** 👉 ⭕️

SELECT 0. 주문번호, 0. 주문일자, 0.고객번호, 0. 주문금액, 0. 배송지
C.고객명, S.결제구분명
FROM 주문 0, 고객 C. 결제구분 S
WHERE 0. 주문일자 BETWEEN :ORD_DT1 AND :ORD_DT2
AND C.고객번호 = 0.고객번호
AND S. 결제구분코드(+) = 0. 결제구분코드

4. **** 👉 ❌

④ SELECT 0. 주문번호, 0, 주문일자, 0, 고객번호, 0. 주문금액, 0, 배송지
C. 고객명, 5. 결제구분
FROM 주문 0, 고객 C. 경제구분 S
WHERE 0. 주문일자 BETWEEN :ORD_DTI AND:ORD_DT2
AND C. 고객번호(+) 0. 고객번호
AND S. 결제구분코드 = 0. 결제구분코드

🍋 기출 포인트

1. ERD에서 주문과 결제구분 간 관계선을 보면, 주문 레코드를 입력할 때 결제구분코드는 입력하지 않을 수 있다고 정의하고 있다.따라서 결제구분과 조인하는 스칼라 서브쿼리를 일반
   조인문으로 변환할 때 반드시 Outer 조인을 사용해야 결과집합을 보장할 수 있다.
2. ⭐️ 주문 레코드를 입력할 때 고객번호는 반드시 입력해야 한다고 정의하였다. 따라서 고객과 조인하는 스칼라 서브쿼리를 일반 조인문으로 변환할 때는 Inner 조인과 Outer 조인 중 어느 것을 사용해도 결과집합에는 영향이 없다. ⭐️

🍒 문제 해설

1. 성능을 위해서 가급적 Outer 조인보다 Inner 조인을 사용하는 것이 좋다.

✍️ 39번 : 일반 조인문을 스칼라 서브쿼리로 변환

아래 조인문을 스칼라 서브쿼리로 변환할 때, 불필요한 요소가 전혀 없으면서 정확한 결과를 보장하는 SOL을 고르시오.

SELECT 0. 주문번호, MIN(0.고객번호) AS 고객번호, MIN(C. 고객명) AS 고객명
MIN(D. 주문금액) AS 총주문금액
MAX(P. 주문금액) AS 최대주문금액
FROM 고객 C, 주문 0, 주문상품 P
WHERE D.주문일자 BETWEEN :ORD_DT1 AND :ORD_DT2
AND C.고객번호 = 0.고객번호
AND P.주문번호 = 0.주문번호
GROUP BY O.주문번호 ;

1. **** 👉 ❌

SELECT 0.주문번호, 0.고객번호 ,
(SELECT 고객명 FROM 고객 WHERE 고객번호 = 0.고객번호) AS 고객명
, 0.주문금액 AS 총주문금액 
, (SELECT 주문금액 FROM 주문상품 WHERE 주문번호 = 0. 주문번호) AS 최대주문금액
FROM 주문 0
WHERE 주문일자 BETWEEN :ORD_DT1 AND ORD_DT2 ;

2. **** 👉 ❌

SELECT O. 주문번호, 0. 고객번호
,(SELECT 고객명 FROM 고객 WHERE 고객번호 = 0. 고객번호 AND ROW <= 1) AS 고객명
,O.주문금액 AS 총주문금액
(SELECT 주문금액 FROM 주문상품
WHERE 주문번호 = 0. 주문번호
AND ROWNUM <=1) AS 최대주문금액
FROM 주문 0
WHERE 주문일자 BETWEEN :ORD_DT1 AND ORD_DT2; 

3. **** 👉 ❌

SELECT 0. 주문번호, 0.고객번호
,(SELECT MAX(고객명) FROM 고객 WHERE 고객번호 = 0.고객번호) AS 고객명
,0. 주문금액 AS 총주문금액
,(SELECT MAX(주문금액) FROM 주문상품 WHERE 주문번호 = 0, 주문번호) AS 최대주문금액
FROM 주문 O
WHERE 주문일자 BETWEEN ORD_DT1 AND ORD_DT2 ; 

4. **** 👉 ⭕️

SELECT 0. 주문번호, 0.고객번호
,(SELECT 고객명 FROM 고객 WHERE 고객번호 = 0. 고객번호) AS 고객명
,O.주문금액 AS 총주문금액
(SELECT MAX(주문금액) FROM 주문상품 WHERE 주문번호 = 0. 주문번호) AS 최대주문금액
FROM 주문 O
WHERE 주문일자 BETWEEN ORD_DT1 AND ORD_DT2 ;

🍋 기출 포인트

1. 스칼라 서브쿼리는 메인 집합의 레코드당 하나의 레코드만 반환해야 하므로 w쪽 집합을
   스칼라 서브쿼리로 변환할 때는 집계(Aggregate) 함수를 사용하거나 ROWNUM = 1 조건을 사용해야 한다.

2. ---

3. ---

🍒 문제 해설

1. 1번 : 주문과 주문상품 간 조인 카디널리티는 1:M이다. 주문 하나당 두 개 이상의 주문상품을 반환할 수 있으므로 주문상품과의 조인을 스칼라 서브쿼리로 변환할 때 ORA-91427 에러를 피하려면 집계 함수를 사용하거나 ROWNUM = 1 조건을 사용해야 한다.
2. 3번 : 고객과 주문 간 조인 카디널리티는 1:M이다. 고객과의 조인을 스칼라 서브쿼리로 변환하면 항상 단일 행을 반환하므로 집계 함수는 불필요하다.

✍️ 40번 : 스칼라 서브쿼리

스칼라 서브쿼리에 대한 설명으로 가장 부적절한 것을 고르시오.

1. ① 메인 쿼리의 한 레코드당 하나의 값만 반환할 수 있다. 👉 ⭕️
2. ② 주로 코드명, 상품명 등을 조회할 때 사용한다. 👉 ⭕️
3. ③ (Unnesting 쿼리 변환이 작동하지 않는다면) NL 조인과 같은 방식으로 조인한다. 👉 ⭕️
4. ④ 스칼라 서브쿼리는 성능에 좋지 않으므로 가급적 일반 조인문을 사용하는 것이 좋다. 👉 ❌

🍋 기출 포인트

✅ 스칼라서브쿼리 캐싱 효과

• 스칼라 서브쿼리로 조인하면 오라클은 조인 횟수를 최소화하려고 입력 값과 출력 값을 내부
  캐시(Query Execution Cache)에 저장해 둔다.
• 조인할 때마다 일단 캐시에서 '입력 값'을 찾아보고, 찾으면 저장된 출력 값을 반환한다.
  캐시에서 찾지 못할 때만 조인을 수행하며, 결과는 버리지 않고 캐시에 저장해 둔다.
• 메인쿼리 집합이 아무리 커도 조인할 데이터를 대부분 캐시에서 찾는다면, 조인 수행횟수를 최소화할 수 있다.

✍️ 41번 : 스칼라 서브쿼리의 캐싱 효과

캐싱 효과 측면에서 스칼라 서브쿼리를 가장 적절히 사용한 것을 고르시오.

SELECT 0. 주문번호, 0. 주문일자, 0.고객번호, 0. 주문금액, 0. 배송지
,(SELECT 고객명 FROM 고객 WHERE 고객번호 = 0.고객번호) 고객명
,(SELECT 상품명 FROM 상품 WHERE 상품코드 = 0. 상품코드) 상품명
,(SELECT 결제구분명 FROM 결제구분 WHERE 결제구분코드 = 0. 결제구분코드) 결제구분명
,(SELECT /*+ INDEX_DESC(H (고객번호, 변경일시)) */ 휴대폰번호
FROM 고객이력 0
WHERE 고객번호 = 0.고객번호
AND ROWNUM = 1) 휴대폰번호
FROM 주문 0, 주문상세 미
WHERE 0. 주문일자 BETWEEN ORD_DT1 AND ORD_DT2
AND D. 주문번호 = 0.주문번호 ; 

1. ① 고객명 조회 👉 ❌
2. ② 상품명 조회 👉 ❌
3. ③ 결제구분명 조회 👉 ⭕️
4. ④ 휴대폰번호 조회 👉 ❌

🍋 기출 포인트

1. 스칼라 서브쿼리 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 작을 때 효과가 있다.

🍒 문제 해설

1. 스칼라 서브쿼리에 사용하는 캐시는 매우 작은 메모리 공간이다.
2. 입력 값의 종류가 매우 많은 경우라면 캐시를 매번 확인하는 비용 때문에 오히려 성능이 나빠지고 CPU 사용률만 높게 만든다. 메모리도 더 사용한다.

✍️ 42번 : 스칼라 서브쿼리 실행계획 모양

아래 SQL 실행계획의 괄호 안에 들어갈 오퍼레이션을 가, 나, 다, 라, 마 순으로 올바르게 나열한 것을 고르시오.

select c.고객번호, c. 고객명
, (select round(avg(거래금액), 2) 평균거래금액
	from 거래
	where 거래일시 >= trunc(sysdate, 'mm')
	and 고객번호 = c.고객번호)
from 고객 c
where c. 가입일시 >= trunc(add_months(sysdate, -1), 'mm') ;		

1. E - D - B - C - A 👉 ⭕️

[순서] [액세스 대상]
5		거래 소트
4 			거래 테이블
3				거래 인덱스
2 					고객 테이블
1						고객 인덱스

🍋 기출 포인트

1. 스칼라 서브쿼리 실행계획 모양은 메인 쿼리 실행부분이 아래쪽에 있고, 스칼라 서브쿼리 실행부분이 위쪽에 있다.