[SQLP필기풀이]4장 조인튜닝(1)-NL조인

✍️ 1번 : 알맞은 오라클 힌트 기술

FROM 절에 나열한 주문, 고객, 결제방식 순으로 조인하되 고객 테이블과는 NL 조인, 결제방식과는 해시 조인하도록 유도하고자 할 때 빈칸에 들어갈 오라클 힌트를 기술하시오.

select /*+ ? */
ㅇ. 주문번호, o, 고객번호, c.고객명, c. 전화번호, 0. 주문금액, t.결제방식명
from 주문 0, 고객 c, 결제방식 t
where o. 주문일자 >= trunc(sysdate)
and c. 고객번호 = 0. 고객번호
and t. 결제방식코드 = o. 결제방식코드

1. ordered use_nl(c) use_hash(t) 👉 ⭕️
2. leading(o c t) use_nl(c) use_hash(t) 👉 ⭕️

🍋 기출 포인트

1. FROM 절에 테이블을 나열한 순으로 조인하고자 할 때 ordered 힌트를 사용한다.
2. NL 조인으로 유도할 때 use_n1 힌트를 사용하며, 해시 조인으로 유도할 때 use_hash 힌트를 사용한다.

✍️ 2번 : 알맞은 SQL Server 힌트 기술

주문 테이블을 기준으로 고객 테이블과 NL 조인하도록 유도하고자 할 때 빈칸에 들어갈 SQL Server 힌트를 기술하시오.

select o.주문번호, p. 고객번호, c.고객명, c.전화번호, o. 주문금액
from 주문 o, 고객 c
where o. 주문일자 >= trunc(sysdate)
and c. 고객번호 = o.고객번호
option ( ? )

1. force order, loop join 👉 ⭕️

🍋 기출 포인트

1. FROM 절에 테이블을 나열한 순으로 조인하고자 할 때 force order 힌트를 사용한다.
2. NL 조인으로 유도할 때 loop join 힌트를 사용한다.

✍️ 3번 : 알맞은 SQL Server 힌트 기술

FROM 절에 나열한 주문, 고객, 결제방식 순으로 조인하되, 고객 테이블과는 NL 조인, 결제방식과는 해시 조인하도록 아래 쿼리를 재작성하고 SQL Server 힌트를 기술하시오.

select o.주문번호, o.고객번호, c.고객명, c.전화번호, o. 주문금액, t. 결제방식명
from 주문 o, 고객 c, 결제방식 t
where 0. 주문일자 >= trunc(sysdate)
and c. 고객번호 = 0. 고객번호
and t. 결제방식코드 = o. 결제방식코드

1. 👉 ⭕️

select o. 주문번호, 0.고객번호, c. 고객명, c, 전화번호, o, 주문금액, t. 결제방식명
from 주문 o
inner loop join 고객 c on (c.고객번호= o.고객번호)
inner hash join 결제방식 t on (t.결제방식코드 = 0.결제방식코드)
where 0. 주문일자 > trunc(sysdate);

🍋 기출 포인트

1. FROM 절에 테이블을 나열한 순으로 조인하고자 할 때 force order 힌트를 사용한다.
2. NL 조인으로 유도할 때 inner loop join 구문을 사용한다.
3. 해시 조인으로 유도할 때 inner hash join 구문을 사용한다.

✍️ 4번 : SQL을 힌트에 따른 조건절 비교 순서

아래 SQL을 힌트에 지시한 대로 수행할 때, 조건절 비교 순서를 올바르게 나열

[인덱스 구성]
사원_PK : 사원번호
사원X1 : 입사일자
고객_PK : 고객번호
고객_X1 : 관리사원번호

select /*+ ordered use_nl(c) index(e) index(c) */
e.사원번호, e.사원명, e. 입사일자
c.고객번호, c.고객명, c. 전화번호, c.최종주문금액
from 사원 e, 고객 c
where c.관리사원번호 = e.사원번호	--가
and e. 입사일자 > '19960101' --나
and e. 부서코드 = '2123'	--다
and c. 최종주문금액 > 20000	--라

1. 나 -> 다 -> 가 -> 라 👉 ⭕️

🍋 기출 포인트

1. 사원 테이블은 인덱스를 통해서 액세스한다.입사일자와 부서코드에 조건절이 있는데, 부서코드에는 인덱스가 없으므로 입사일자 조건('나'조건)으로 사원X1 인덱스를 스캔해서 얻은 ROWID로 테이블을 액세스한 후 부서코드 조건('다' 조건)을 필터링한다.
2. 고객 테이블도 인덱스를 통해서 액세스한다.관리사원번호와 최종주문금액에 조건절이 있는데, 최종주문금액에는 인덱스가 없으므로 관리사원번호 조인 조건('가' 조건)으로 고객_X1 인덱스를 스캔해서 얻은 ROWID로 테이블을 액세스한후 최종주문금액 조건('라' 조건)을 필터링한다.

🍒 문제 해설

1. 고객 테이블 최종주문금액에 인덱스가 있다면, 나 → 다 → 라 → 가 순으로 진행할 수도 있다.어느 쪽이 유리한지는 관리사원번호 조건과 최종주문금액 조건의 데이터 분포에 따라 결정된다.

✍️ 5번 :

아래 SQL index 힌트에 인덱스명이나 컬럼명을 지정하지 않았으므로 여러 가지 액세스 경로가 가능하다. 보기는 각 액세스 경로에서 사용하게 될 인덱스를 나열한 것인데, 가장 가능성이 작은 것을 고르시오.

[인덱스 구성]
사원_PK : 사원번호
사원_X1 : 입사일자
사원_X2 : 부서코드
고객_PX : 고객번호
고객_X1 : 관리사원번호
고객_X2 : 최종주문금액

select /*+ ordered use_nl(c) index(e) index(c) */
e.사원번호, e.사원명, e. 입사일자, c.고객번호, c.고객명, c. 전화번호, c.최종주문금액
from 사원 e, 고객 c
where c. 관리사원번호 = e.사원번호
and e. 입사일자 >= '19960101'
and e. 부서코드 = '2123'
and c. 최종주문금액 >= 20000

1. ② 사원_X1, 고객_X1 👉 ⭕️
2. ③ 사원_X1, 고객_X2 👉 ⭕️
3. ④ 사원_X2, 고객_X1 👉 ⭕️
4. ① 사원_PK, 고객_X1 👉 ❌

🍒 문제 해설

1. ordered use_nl(c) 힌트를 지정했으므로 사원을 기준으로 고객과 NL 조인한다.
2. 사원 테이블은 Index(e) 힌트를 지정했으므로 인덱스를 통해서 액세스한다.
   입사일자와 부서코드에 조건절이 있는데, 두 컬럼 모두 인덱스가 있으므로 입사일자 조건('나' 조건으로사원_X1 인덱스를 스캔할 수도 있고, 부서코드 조건으로 사원_X2 인덱스를 스캔할 수도 있다.
3. 고객 테이블도 Index(c) 힌트를 지정했으므로 인덱스를 통해서 액세스한다. 관리사원번호와 최종주문금액에 조건절이 있는데, 두 컬럼 모두 인덱스가 있으므로 관리사원번호 조인 조건
   ('가' 조건)으로 고객X1 인덱스를 스캔할 수도 있고, 최종주문금액 조건('라' 조건)으로 고객X2 인덱스를 스캔할 수도 있다.

✍️ 6번 : NL 조인의 특징

NL 조인의 특징

1. ① 랜덤 액세스 위주의 조인 방식이다. 👉 ⭕️
2. ② 조인할 대상 레코드가 아무리 많아도 ArraySize에 해당하는 최초 N건을 빠르게 출력할 수 있다. 👉 ⭕️
3. ③ 인덱스 유무, 인덱스 구성에 의해 성능이 크게 달라진다. 👉 ⭕️
4. ④ 부분범위 처리가 가능한 상황에서만 효과적인 조인 방식이다. 👉 ❌

🍋 기출 포인트

1. NL 조인은 소량 데이터를 주로 처리하거나 부분범위 처리가 가능한 온라인 트랜잭션 처리(OLTP) 시스템에 적합한 조인 방식이다.
2. 부분범위 처리가 불가능한 상황이더라도 소량 데이터를 조인할 때는 NL 조인이 가장 유리하다.

3. ---

🍒 문제 해설

1. NL 조인은 랜덤 액세스 위주의 조인 방식인데 이는 인덱스 구성이 아무리 완벽해도 대량 데이터 조인할 때 NL 조인이 불리한 이유다.
2. 조인을 한 레코드씩 순차적으로 진행한다.따라서 부분범위 처리가 가능하다면 조인할 대상 레코드가 아무리 많아도 빠른 응답 속도를 낼 수 있다.
3. 순차적으로 진행하므로 먼저 액세스되는 테이블 처리 범위에 의해 전체 일량이 결정된다.
4. 조인 컬럼에 대한 인덱스가 있느냐 없느냐, 있다면 컬럼이 어떻게 구성됐느냐에
   따라 조인 효율이 크게 달라진다.

✍️ 7번 : 가장 효과적인 SOL 튜닝 방안

SOL에 대한 튜닝 방안으로 가장 효과적인 것

* 한 달 간 거래 건수는 평균 20만 건
select p. 상품코드, p. 상품가격, t.거래일자, t.거래수량, t.거래금액
from 상품 p. 거래 t
where p. 상품분류코드 = 'KT6'
and p. 상품가격 between 100000 and 200000
and t. 상품코드 = p. 상품코드
and t. 거래일자 between '20210101' and '20210131·

1. ③ 상품_X01 인덱스에 컬럼을 추가한다. 👉 ⭕️

🍋 기출 포인트

1. 상품_X01 인덱스는 (1) 상품분류코드가 선두 컬럼이고 상품가격은 포함하지 않거나, (2) 상품가격이 선두 컬럼이고 상품분류코드는 포함하지 않는다. 상품에 대한 조건절이 2개인 상황에서 인덱스에서 9,185건을 얻었는데 테이블 액세스 후에 69건으로 줄었기 때문이다.

✍️ 8번 : 가장 효과적인 SOL 튜닝 방안

아래 SOL에 대한 튜닝 방안으로 가장 효과적인 것

[인덱스 구성 ]
상품_PK : 상품코드
상품_X01 : 상품분류코드 + 상품가격 + 공급업체코드
거래_PK : 거래번호
거래_X01 : 거래일자 + 상품코드
거래_X02 : 상품코드 + 거래구분코드 + 거래일자

select p. 상품코드, p. 상품가격, t.거래일자, t.거래수량, t.거래금액
from 상품 p. 거래 t
where p. 상품분류코드 = 'KTG'
and p. 공급업체코드 = 'SP83732'
and p. 상품가격 between 10000 and 100000
and t. 상품코드 = p. 상품코드
and t. 거래일자 between '20210101' and '20210131';

1. ① 상품_X01 인덱스의 컬럼 순서를 조정한다. 👉 ⭕️

🍋 기출 포인트

1. 주어진 조건에 대한 상품_X01 인덱스 스캔 효율이 매우 좋지 않다. 69 로우를 얻기 위해 인덱스에서 922개 블록을 읽었기 때문이다.한 블록에 평균 1,000개 로우가 저장돼 있다면, 922,000개 가량의 로우를 읽은 셈이다.

✍️ 9번 : 튜닝을 위해 가장 우선적으로 검토할 사항

튜닝을 위해 가장 우선적으로 검토할 사항

[인덱스 구성]
상품_PK : 상품코드
상품_X01 : 공급업체코드 + 상품가격
거래_PK : 거래번호
거래_X01 : 거래일자 + 상품코드
거래_X02 : 상품코드 + 거래일자

select p. 상품코드, p. 상품가격, t.거래일자, t.거래수량, t.거래금액
from 상품 p. 거래 t
where p. 공급업체코드 = 'SP83732'
and p. 상품가격 between 100000 and 200000
and t. 상품코드 = p. 상품코드
and t. 거래일자 between '20210101' and '20210131';

1. 거래_X01 대신 거래_X02 인덱스 사용을 검토한다. 👉 ⭕️

🍋 기출 포인트

1. 거래_X01 인덱스에서 368 로우를 얻는 과정에 25,257개 블록을 읽었다. 인덱스를 69번(조인 액세스가 69번) 수직 탐색한 것에 비해 상당히 많은 인덱스 블록을 읽었으므로 인
   맥스 스캔 효율이 좋지 않다는 사실을 알 수 있다.
2. 거래_X01 인덱스 선두 컬럼인 거래일자가 BETWEEN 조건이므로 공급업체코드 조건을 만족하는 상품마다 한 달 치 거래를 모두 스캔하면서 상품코드를 필터링하는 것이 문제다.

🍒 문제 해설

1. NL 조인을 우선 검토한 후에 성능 개선 방안을 찾기 어려울 때 해시 조인을 검토하는 것이 올바른 순서다.

✍️ 10번 : 가장 효과적인 SOL 튜닝 방안

아래 SOL에 대한 튜닝 방안으로 가장 효과적인 것

[인덱스 구성 ]
상품_PK : 상품코드
상품_X01 : 상품분류코드 + 상품가격
거래_PK : 거래번호
거래_X01 : 상품코드 + 거래일자
거래_X02 : 거래일자

▶ 한 달간 거래는 평균 10만 건

select p.상품코드, p. 상품가격, t.거래일자, t.거래수량, t.거래금액
from 상품 p, 거래 t
where p. 상품분류코드 = 'KTG'
and p. 상품가격 between 10000 and 100000
and t. 상품코드 = p. 상품코드
and t. 거래일자 between 20210101' and '20210131' ;

1. ④ 해시 조인으로 유도한다. 👉 ⭕️

🍋 기출 포인트

1. 인덱스가 모두 최적인 상황에서 조인 순서를 변경해도 좋은 성능을 기대하기 어렵다면
   다른 조인 메소드를 고려해야 한다. 해시 조인으로 유도하면 거래 테이블은 거래_X02 인
   덱스를 통해 액세스하게 된다.

🍒 문제 해설

1. 거래_X02 인덱스의 클러스터링 팩터가 성능을 좌우하겠지만, 최악의 경우를 가정해도 지금보다는 I/O 발생량이 상당히 줄 것으로 기대된다.

✍️ 11번 : 최적 인덱스 구성안 고르기

아래 두 SQL을 위한 PRA_HST_STC 테이블의 최적 인덱스 구성안을 고르시오.

[SQL 1]
SELECT /*+ ORDERED USE_NL(B) */ *
FROM PRA_HST_STC A, ODM_TRMS B
WHERE A.SALE_ORG_ID = :SALE_ORG_ID
AND A.STRO_GRP_ID = B.STRD_GRP_ID
AND A.STRD_ID = B.STRD_ID
ORDER BY A.STC_DT

[ SQL 2 ]
SELECT /*+ ORDERED USE_NL(B) */ *
FROM PRA_HST_STC A, OOM_TRMS B
WHERE A.USER_ID = :USER_ID
AND A.SALE_ORG_ID = :SALE_ORG_ID
AND A.STRD_GRP_ID = B.STRD_GRP_ID
AND A.STRD_ID = B.STRD_ID
ORDER BY A.STC_DT

1. ④ PRA_HST_STC_X1 : SALE_ORG_ID + USER_ID 👉 ⭕️
2. ①
   PRA_HST_STC_X1 : SALE_ORG_ID + STRO_GRP_ID + STRO_ID + STC_DT
   PRA_HST_STC_X2 : USER_ID + SALE_ORG_ID + STRO_GRP_ID + STRD_ID + STCLOT 👉 ❌
3. ② PRA_HST_STC_X1 : SALE_ORG_ID + USER_ID + STRO_GRP_ID + STRO_ID + STC_DT 👉 ❌
4. ③ PRAHST_STCX1 : SALE_ORG_ID + USER_ID + STC_DT + STRD_GRP_ID + STRO_ID 👉 ❌

🍋 기출 포인트

1. NL 조인으로 처리할 때 STRD_GRP_ID와 STRD_ID 컬럼이 OOM_TRIMS 테이블의 인덱스 구성 요소로는 필요하지만, PRA_HST_STC 테이블의 인덱스 구성 요소로는 전혀 불필요하다는 사실을 이해하는 것이 문제의 핵심!!

✍️ 12번 : 최적 인덱스 구성안

아래 SQL을 위한 두 테이블의 최적 인덱스 구성안을 고르시오

SELECT
FROM (SELECT A.*, B.TRMS_DT, B.TRMS_DESC
FROM PRA HST STC A, ODM TRMS B
WHERE A.SALE ORG_ID = :SALE ORG_ID
AND B.STRD GRP_ID = A.STRD_GRP_ID
AND B.STRD_ID = A.STRD_ID
AND B.TRMS_DT BETWEEN TRMS DT1 AND TRMS_DT2
ORDER BY A.STC DT DESC)
WHERE ROWNUM <= 10

1. 👉 ⭕️
   PRA_HST_STC_X1 : SALE ORG_ID + STC_DT
   ODM_TRMS_X1: STRD_GRP_ID + STRD_ID + TRMS DT
2. PRA_HST_STC_X1: SALE ORG_ID
   ODM_TRMS_X1: STRD_GRP_ID + STRD_ID + TRMS_DT
3. PRA_HST_STC_X1: SALE ORG_ID
   ODM_TRMS_X1: TRMS_DT + STRD_GRP_ID + STRD_ID
4. PRA_HST_STC_X1 : SALE ORG_ID + STC_DT
   ODM_TRMS_X1: TRMS_DT + STRD_GRP_ID + STRD_ID

🍋 기출 포인트

1. 부분범위 처리가 가능하도록(=소트 생략 가능하도록) 그리고 NL 방식 조인에 최적화되도록 인덱스를 구성해야 한다.
2. A.STC DT 인덱스 수직 액세스 가능 컬럼으로 두어야 한다.즉 소트 연산을 생략하려면 드라이빙 테이블인 PRA_HST_STC의 인덱스를 「SALE_ORG_ID + STC_DT 순으로 구성해야 한다.
3. NL 조인을 효과적으로 처리하려면 Inner 쪽 테이블인 CON_TRMS의 인덱스를 「STRO_GRP_ID + STRD_ID + TRMS_DT」 또는 「STRO_ID + STRO_GRP_ID + TRIS_DT」 순으로 구성해야 한다.

✍️ 13번 : 테이블의 인덱스 구성

아래 SQL을 위한 ODM_TRMS 테이블의 인덱스 구성에 대한 설명으로 올바른 것

[ SQL ]
SELECT /*+ ORDERED USE_NL (B) */ *
FROM PRA_HST STC A, ODM TRMS B
WHERE A.SALE ORG_ID=:SALE ORG_ID
AND B.STRD_ID = A.STRD_ID
AND B.STRD GRP_ID= :STRD_GRP_ID
AND B.TRMS DT BETWEEN TRMS DT1 AND TRMS_DT2
ORDER BY A.STC DT;

[인덱스 구성 ]
ODM_TRMS_X1: STRD_GRP_ID + TRMS_DT + STRD_ID
COM_TRMS_X2: STRD_ID + STRD_GRP_ID + TRMS_DT
ODM_TRMS_X3: STRD_GRP_ID + STRD_ID + TRMS_DT

1. ODM_TRMS_X2, ODM_TRMS_X3 인덱스가 최적이며 , 둘 간의 성능 차이는 없다.ODM_TRMS_X2, ODM_TRMS_X3 인덱스가 최적이며 , 둘 간의 성능 차이는 없다. 👉 ⭕️

🍋 기출 포인트

1. ODM_TRMS_X2처럼 조인 컬럼을 인덱스 앞쪽에 두는 게 유리하다고 처음에 생각했지만,
   INNER 테이블인 COM_TRMS를 액세스하는 시점에 STRO_ID와 STRO_GRP_ID 모두 '' 조건이므로
   둘 중 어느 것이 앞으로 모든 인덱스 액세스 효율에는 차이가 없다.

✍️ 14번 : 나타날 수 있는 실행계획

오라클 11g 이상 버전에서 아래 SQL에 대해 나타날 수 있는 실행계획으로 가장 부적절한 것

select /*+ ordered use_nl(t) index(p) index(t) +/
p. 상품코드, p. 상품가격, t.거래일자, t.거래수량, t.거래금액
from 상품 p. 거래 t
where p.공급업체코드 ='abcabc111'
and t. 상품코드 = p. 상품코드
and t.거래일자 between "28210101" and "20210131";

1. **** 👉 ⭕️
   
2. **** 👉 ⭕️
   
3. **** 👉 ❌
   
4. **** 👉 ⭕️
   

🍋 기출 포인트

1. ①번은 전통적인 방식의 NL 조인 실행계획이다.
2. ②번은 Inner 쪽 테이블 액세스가 NESTED LOOPS 위쪽에 위치하였다. 이는 Inner 쪽 테이블
   에 대한 디스크 I/O 과정에 테이블 Prefetch 기능이 작동할 수 있음을 표시하기 위해 91 버
   전부터 나타나기 시작한 실행계획이다.
3. Outer 쪽 테이블 액세스가 NESTED LOOPS 위쪽에 위치하는 실행계획은 나타나지 않는다.
4. Inner 쪽 테이블 액세스가 Inner 쪽 인덱스 스캔 아래쪽에 위치하였다.
5. 이는 Inner 쪽 테이블에 대한 디스크 I/O 과정에 배치 1/0 기능이 작동할 수 있음을 표시하기 위해 11g 버전부터 나타나기 시작한 실행계획이다.

✍️ 15번 : 스칼라 서브쿼리 부분 튜닝 방안

아래는 튜닝이 필요한 SQL에서 스칼라 서브쿼리 부분만 발췌한 것이다. 스칼라 서브쿼리를 수행하는 부분에서 현재의 문제점을 기술하고, 튜닝 방안을 제시하시오.

1. 👉 ⭕️

SELECT
FROM
WHERE
(SELECT CASE WHEN A. 일할계산여부 = 'Y'
    THEN NVL(A. 총청구건수, 0) - NVL(A. 청구횟수, 0)
    ELSE B.할부개월수 - NVL(A. 청구횟수, 0) END
FROM 서비스별할부 A, 할부계획 B
WHERE A.서비스계약번호 = MV.서비스계약번호
AND A. 할부상태코드 = 'XR'
AND B.할부계획ID(+) = (CASE WHEN A. 일할계산여부 = 'Y' THEN NULL
                    ELSE A. 할부계획ID END)
AND ROWNUM = 1) AS 청구횟수, ....

🍋 기출 포인트

1. 할부개월수는 일할계산여부가 'Y'가 아닐 때만 필요하다. 따라서 할부계획은 서비스별할부의 일할계산여부가 'Y'가 아닐 때만 조인하도록 한다.