[SQLP 막간정리 2] [1] 고급 SQL 튜닝(1)

(0) 실기

⭐️ 17번 (28 page)

• ⭐️ max 보다 top(1) 쿼리가 더 낫나 ? ⭐️
  • 변경 구분 코드 값을 모두 필터링하므로 FIRST_ROW가 안 먹힌다.
  • FIRST_ROW가 먹히지 않는 다는 건 전체를 스캔할 수 밖에 없다는 소리
    • ⭐️ 그래서 부분범위처리를 해주는 거임 ! ⭐️

SELECT 변경일시 AS 최종변경일시 
FROM (
  SELECT 변경일시
  FROM 상품변경이력
  WHERE 상품번호 = 'ZE367'
      AND 변경구분코드 ='C2'
  ORDER BY 상품번호 , 변경일시 DESC
)
WHERE ROWNUM =1 ;

⭐️ 18번 (28 page)

• ⭐️ 분석함수는 where절 이후에 실행된다. ⭐️
  • 따라서 변경구분코드는 바깥에서 조건 처리를 해줘야한다.

SELECT 변경일시 AS 최종변경일시 , 상품번호
FROM (
  SELECT 변경일시 , 변경구분코드 , 상품번호
  ,ROW_NUMBER() OVER(PARTITION BY 상품번호 ORDER BY 변경일시 DESC) RNUM
  FROM 상품변경이력
  WHERE 변경일시 >= TO_DATE('20220301','YYYYMMDD')
  AND 변경일시 < TO_DATE('20220401','YYYYMMDD')
)
WHERE RNUM =1
AND 변경구분코드 = 'C2';

19번 (28 page)

SELECT 기준일자 , 할인율 AS 최종할인율
FROM (
  SELECT 할인율 , 기준일자
  ,ROW_NUMBER() OVER(PARTITION BY 기준일자 ORDER BY 변경순번 DESC) RNUM
  FROM 상품할인율
  WHERE 기준일자 BETWEEN '20220301' AND '20220331'
  AND 상품번호 = 'R0014' 
)
WHERE RNUM =1
ORDER BY 기준일자;

20번 (29 page)

SELECT 할인율 AS 최종할인율
FROM (
  SELECT 할인율
  FROM 상품할인율
  WHERE 상품번호 = 'R0014'
  ORDER BY 기준일자 DESC , 변경순번 DESC
)
WHERE ROWNUM <= 1;

22번 (29 page)

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

33번 (35 page)

UPDATE (
  SELECT /*+ LEADING(A) USE_NL(B) */ A.법정대리인_연락처, B.연락처
  FROM 고객 A , 고객 B
  WHERE
  A.성인여부 = 'N'
  AND B.고객번호 = A.법정대리인_고객번호
  AND B.연락처 <> A.법정대리인_연락처 --이미 같으면 업데이트 하지 않는다.
  AND B.연락처 IS NOT NULL )
SET 법정대리인_연락처 = 연락처;

40번 (39 page)

SQL >
BEGIN      
	UPDATE 수납 A
    	SET A.수납금액 = ( SELECT SUM(입금액)
        				FROM 은행입금내역
                        WHERE 입금일시 >= TO_DATE('20210329','YYYYMMDD')
                        AND 입금일시 < TO_DATE('20210330','YYYYMMDD')
                        AND 고객ID = A.고객ID)
    AND A.수납일자 ='20210329'
    AND EXISTS ( SELECT 'X'
        		 FROM 은행입금내역
                 WHERE 입금일시 >= TO_DATE('20210329','YYYYMMDD')
                 AND 입금일시 < TO_DATE('20210330','YYYYMMDD')
                AND 고객ID = A.고객ID);
    
	INSERT INTO 수납 (고객ID , 수납일자 , 수냡금액) 
    SELECT 고객ID , '20210329' , SUM(입금액)
    FROM 은행입금내역 A
    WHERE 입금일시 >= TO_DATE('20210329','YYYYMMDD')
    AND 입금일시 < TO_DATE('20210330','YYYYMMDD')
    AND NOT EXISTS (
      SELECT 'X'
      FROM 수납
      WHERE 수납일자 = '20210329'
      AND 고객ID = A.고객ID
    )
    GROUP BY 고객ID;
    
    COMMIT;
END;

(1) 소트 튜닝

10번 : ⭐️ 부분범위 처리했지만 I/O량 제일 많았음

• 이거 계속 틀림 ㅠ

(2) 대량 DML 튜닝

(3) 데이터베이스 CALL 최소화

50번

• 스칼라 서브쿼리에 대한 캐싱은 SQL 단위이다.
• Deterministic 함수에 대한 캐싱은 Fetch Call 단위이다.따라서 Fetch Call이 많으면 같은 입력 값에 대한 Recursive Call 횟수도 많아진다.
• 스칼라 서브쿼리 및 Deterministic 함수 결과는 PGA에 캐싱된다.
• Result 캐시는 SGA 영역에 캐싱된다. 따라서 데이터를 읽고 쓸 때마다 래치를 획득해야 하는 부담이 있다.
• Native 컴파일 기능을 활용하면 코드를 인터프리팅 하는 부하를 줄여준다.
• Native 컴파일 기능을 활용해도 Context Switching과 Recursive Call에 대한 부하는 줄여주지 못한다.
• 🍋 기출 포인트
  스칼라 서브쿼리(SQL단위,PGA) > Deterministic 함수에 대한 캐싱(Fetch
  Call 단위,PGA) > Result 캐시(SGA) > Native 컴파일 순으로 효과적이다.