[SQLP 막간정리 1] [4] 조인 튜닝

(0) 실기

47번 (162 page)

[인덱스 재구성]
주문상품_X1 : 할인유형코드 + 주문일시

SELECT /*+ LEADING(P) USE_HASH(O) FULL(P)*/
	P.상품코드 , P.상품명 , P.상품가격 , O.총주문수량 , O.총주문금액 
FROM ( SELECT /*+ INDEX(A 주문상품_X1) NO_MERGE*/ 상품코드 ,SUM(O.주문수량) 총주문수량 , SUM(O.주문금액) 총주문금액
  FROM 주문상품 A
  WHERE 주문일시 >= ADD_MONTHS(SYSDATE,-1)
  AND 할인유형코드 = 'K890'
  GROUP BY 상품코드 ) O , 상품 P
WHERE O.상품코드 = P.상품코드
ORDER BY 총 주문금액 DESC , 상품코드

48번 (163 page)

SELECT /*+ LEADING(P) USE_HASH(O) FULL(P)*/
	P.상품코드 , P.상품명 , P.상품가격 , O.총주문수량 , O.총주문금액
FROM ( SELECT /*+ FULL(A) NO_MERGE*/ 상품코드 ,SUM(O.주문수량) 총주문수량 , SUM(O.주문금액) 총주문금액
  FROM 주문상품 A
  WHERE 주문일시 >= ADD_MONTHS(SYSDATE,-1)
  AND 할인유형코드 = 'K890'
  GROUP BY 상품코드 ) O , 상품 P
WHERE O.상품코드 = P.상품코드
ORDER BY 총 주문금액 DESC , 상품코드

49번 (164 page)

SELECT /*+ LEADING(O) USE_NL(P) */
	P.상품코드 , P.상품명 , P.상품가격 , O.총주문수량 , O.총주문금액
FROM ( SELECT /*+ FULL(A) NO_MERGE*/ 상품코드 ,SUM(O.주문수량) 총주문수량 , SUM(O.주문금액) 총주문금액
  FROM 주문상품 A
  WHERE 주문일시 >= ADD_MONTHS(SYSDATE,-1)
  AND 할인유형코드 = 'K890'
  GROUP BY 상품코드 ) O , 상품 P
WHERE O.상품코드 = P.상품코드
ORDER BY 총 주문금액 DESC , 상품코드

⭐️ 50번 ⭐️ (165 page , order by 2번 !!!)

SELECT /*+ LEADING(O) USE_NL(P)*/
	P.상품코드 , P.상품명 , P.상품가격 , O.총주문수량 , O.총주문금액
FROM ( SELECT /*+ FULL(A) NO_MERGE */ 상품코드 ,SUM(O.주문수량) 총주문수량 , SUM(O.주문금액) 총주문금액
  FROM 주문상품 A
  WHERE 주문일시 >= ADD_MONTHS(SYSDATE,-1)
  AND 할인유형코드 = 'K890'
  GROUP BY 상품코드 
  ORDER BY 총 주문금액 DESC , 상품코드) O , 상품 P
WHERE O.상품코드 = P.상품코드
AND ROWNUM <=100
ORDER BY 총 주문금액 DESC , 상품코드

⭐️ 51번 ⭐️ (p.166 , 대부분의 상품이 스캔이 됨)

• ⭐️ 속성이 많아도 부분범위 처리 하므로 많이 안읽는다. ⭐️

[인덱스 추가]
상품_X1 :등록일시
[인덱스 재구성]
주문상품_X1 :할인유형코드 + 주문일시

SELECT /*+ LEADING(P) USE_NL(O) INDEX_DESC(P 상품_X1)*/
	P.상품코드 , P.상품명 , P.상품가격 , O.총주문수량 , O.총주문금액 
FROM ( SELECT /*+ INDEX(A 주문상품_X1) NO_MERGE PUSH_PRED*/ 상품코드 ,SUM(O.주문수량) 총주문수량 , SUM(O.주문금액) 총주문금액
  FROM 주문상품 A
  WHERE 주문일시 >= ADD_MONTHS(SYSDATE,-1)
  AND 할인유형코드 = 'K890'
  GROUP BY 상품코드 ) O , 상품 P
WHERE O.상품코드 = P.상품코드
ORDER BY P.등록일시 DESC

⭐️ 52번 ⭐️ (p.167 , no_merge 안 썼음)

• ⭐️ 서브쿼리 너무 많으면 의심해바야한다. 이건 no_merge 안 썼음 ⭐️

[인덱스 재구성]
주문상품_X1 : 할인유형코드 + 주문일시
[인덱스 추가]
상품_X1 : 등록일시 + 상품코드

SELECT /*+ LEADING(O) USE_NL(O) NO_NLG_BATCHING(P)*/
	P.상품코드 , P.상품명 , P.상품가격 , O.총주문수량 , O.총주문금액 
FROM ( SELECT /*+ FULL(A) INDEX_FFS(B) LEADING(B) USE_HASH(A) */ 상품코드 ,SUM(O.주문수량) 총주문수량 , SUM(O.주문금액) 총주문금액 , MIN(B.등록일시) 등록일시
  FROM 주문상품 A , 상품 B
  WHERE A.주문일시 >= ADD_MONTHS(SYSDATE,-1)
  AND A.할인유형코드 = 'K890'
  AND B.상품코드 = A.상품코드
  GROUP BY A.상품코드 
  ORDER BY 등록일시 DESC) O , 상품 P
WHERE ROWNUM <= 100
AND O.상품코드 = P.상품코드

53번 (아우터조인 , p.168)

• ⭐️ 일자 비교 (최근 한달) ⭐️ : 일자 >= trunc(add_months(sysdate,-1))
• NVL2 함수는 NULL이 아닌 경우 지정값1을 출력하고, NULL인 경우 지정값2를 출력한다.
• 함수 : NVL2("값", "지정값1", "지정값2") // NVL2("값", "NOT NULL", "NULL")

[인덱스 추가]
작업지시_X1 : 작업자ID + 실제 방문일자
select *
from (SELECT /*+ LEADING(A) USE_NL(B) USE_NL(C) INDEX(A 작업지시_X1)*/ 
	A.작업일련번호 , A.실제방문일자 ,
    NVL2(A.개통접수번호,'개통','장애') 접수구분,
    NVL2(A.개통접수번호,B.고객번호,C.고객번호) 고객번호 ,
    NVL2(A.개통접수번호,B.주소,C.주소) 주소
  FROM 작업지시 A , 개통접수 B , 장애접수 C
  WHERE 작업자ID = 'Z123456'
  AND B.개통접수번호(+) = 개통접수번호
  AND C.장애접수번호(+) = 장애접수번호
  AND A.작업일자 >= TRUNC(ADD_MONTHS(SYSDATE , -1))
  ORDER BY 실제방문일자 DESC ) 
WHERE ROWNUM <= 10;

54번

• 적절한 인덱스가 있어 ⭐️ UNION ALL 처리 ⭐️

SELECT 작업일련번호 , 작업자ID , '개통' 작업구분, 고객번호 , 주소
  FROM 작업지시 A , 개통접수 B
  WHERE A.작업구분코드 = 'A'
  AND 방문예정일자 = TO_CHAR(sysdate , 'YYYYMMDD')
  AND A.접수번호 = B.개통접수번호
UNION ALL
SELECT 작업일련번호 , 작업자ID , '장애' 작업구분, 고객번호 , 주소
  FROM 작업지시 A , 장애접수 C
  WHERE A.작업구분코드 = 'B'
  AND 방문예정일자 = TO_CHAR(sysdate , 'YYYYMMDD')
  AND A.접수번호 = C.장애접수번호

55번

• 적절한 인덱스가 없어 필터로 넘기면서 ⭐️ 한꺼번에 조인 처리 ⭐️

SELECT 작업일련번호 , 작업자ID , 
DECODE(작업구분코드,'A','개통','B','장애') 작업구분 ,
DECODE(작업구분코드,'A',B.고객번호,'B',C.고객번호) 고객번호 ,
DECODE(작업구분코드,'A',B.주소,'B',C.주소) 주소
FROM 작업지시 A , 개통접수 B , 장애접수 C
WHERE A.방문예정일자 = TO_CHAR(SYSDATE, 'YYYYMMDD')
AND B.개통접수번호(+) = DECODE(작업구분코드,'A',A.접수번호)
AND C.장애접수번호(+) = DECODE(작업구분코드,'B',A.접수번호);

56번

• 날짜형 일시 비교(오늘) : 일시 >= TRUNC(sysdate) and 일시 TRUNC (sysdate+1)
• 인덱스에 작업 구분 코드 추가할 것 !
• union all 처리

[인덱스 생성]
작업지시_X1 : 접수구분코드 + 접수번호
개통접수_X1 : 접수일자 + 접수번호
장애접수_X1 : 접수일자 + 접수번호

SELECT A.작업일련번호 , A.작업자ID , '개통' AS 작업구분, B.고객번호 , B.주소
  FROM 작업지시 A , 개통접수 B
  WHERE A.작업구분코드 = 'A'
  AND B.개통접수일시 >= TRUNC(SYSDATE)
  AND B.개통접수일시 < TRUNC(SYSDATE+1)
  AND A.접수번호 = B.개통접수번호
UNION ALL
SELECT A.작업일련번호 , A.작업자ID , '장애' AS 작업구분, B.고객번호 , B.주소
  FROM 작업지시 A , 장애접수 B
  WHERE A.작업구분코드 = 'B'
  AND B.장애접수일시 >= TRUNC(SYSDATE)
  AND B.장애접수일시 < TRUNC(SYSDATE+1)
  AND A.접수번호 = B.장애접수번호

57번

SELECT P.장비번호 , P.장비명 , A.상태코드 AS 최종상태코드
, A.변경일자 AS 최종변경일자 , A.변경순번 AS 최종변경순번
  FROM 장비 P , 상태변경이력 A
  WHERE P.장비구분코드 = 'A001'
  AND A.장비번호 = P.장비번호
  AND (P.변경일자 , P.변경순번) = (
	  SELECT 변경일자 , 변경순번
      FROM (
        SELECT 변경일자 , 변경순번
        FROM 상태변경이력 
        WHERE 장비번호 = A.장비번호
        ORDER BY 변경일자 DESC , 변경순번 DESC
        )
     WHERE ROWNUM <=1 )
ORDER BY P.장비번호;

58번

SELECT P.장비번호 , P.장비명 , P.최종상태코드 , A.상태코드 AS 직전상태코드 ,
A.변경일자 AS 직전변경일자 , A.변경순번 AS 직전변경순번
FROM 장비 P , 상태변경이력 A
WHERE P.장비구분코드 = 'A001'
AND A.장비번호 = P.장비번호
AND (A.변경일자 , A.변경순번) =
(SELECT 변경일자 , 변경순번
FROM (
  SELECT 변경일자 , 변경순번
  FROM 상태변경이력
  WHERE 변경일자 < P.최종상태변경일자
  AND 장비번호 = P.장비번호
  ORDER BY 변경일자 DESC , 변경순번 DESC)
WHERE ROWNUM <= 1)
ORDER BY P.장비번호

59번

SELECT P.장비번호 , P.장비명 , A.상태코드 AS 최종상태코드
, TO_CHAR(A.유효시작일시,'YYYYMMDD') AS 최종상태변경일자
FROM 장비 P , 상태변경이력 A
WHERE P.장비구분코드 = 'A001'
AND A.장비번호 = P.장비번호
AND P.유효시작일자 <= SYSDATE
AND P.유효종료일자 > SYSDATE
ORDER BY P.장비번호;

60번

SELECT P.장비번호 , P.장비명 , P.최종상태코드 , H.상태코드 AS 직전상태코드
, TO_CHAR(P.최종상태변경일시,'YYYYMMDD') AS 최종상태변경일자
, TO_CHAR(H.유효시작일시,'YYYYMMDD') AS 직전변경일자
FROM 장비 P , 상태변경이력 H
WHERE P.장비구분코드 = 'A001'
AND H.장비번호 = P.장비번호
AND H.유효시작일시 < P.최종상태변경일시
AND H.유효종료일시 >= P.최종상태변경일시 - 1/(60*60*24) --최종상태변경일시 1초전 보다는 크다.
ORDER BY P.장비번호

(1) NL조인

(2) 소트 머지 조인

(3) 해시 조인

32번

• ⭐️ swap_join_inputs 힌트 중요 !!

(4) 스칼라 서브 쿼리

(5) 고급 조인 기법