[SQLP] 시험 직전에 볼 내용들 모으기

공부하면서 헷갈리는 것들 계속 추가할 예정
 
✔ Order by가 없는 Inline View는 View Merging이 일어날 확률이 있으니 필요시 no_merge 힌트를 고려하자
✔ Rownum <= 100 등 Top-N이 필요한 부분 집합 쿼리라면 NL 조인으로 풀어라
✔ 조인 집합이 작으면서 코드성 비슷한데 대부분의 데이터가 사용된다면? Hash Join의 Build Input 으로 풀자
✔ 스칼라서브쿼리는 실행계획의 최상단에 나온다
✔ 해시조인에서 Probe Input에 조인이 있다면 먼저 수행되야만 한다. 따라서 실행계획 순서가 일반 실행계획처럼 나오지 않는다!!
✔ 조인 회수를 줄이기 위해 일부 쿼리를 먼저 Group by 하는 식으로 접근해보자
✔ 해시조인은 둘 중 작은 쪽만 읽어서 PGA에 해시 맵을 저장하지만, 소트 머지 조인은 양쪽 집합을 정렬해서 PGA에 저장하므로 더 많은 메모리를 사용한다
✔ 배치 I/O가 발생하면서 SORT ORDER BY가 일어난다면 부분범위 처리가 불가능해진다
✔ 선두컬럼이 BETWEEN, 부등호, LIKE면 INDEX SKIP SCAN이 가능하다. IN이면 불가능하다
✔ 조건절에 선두컬럼만 있으면 INDEX RANGE SCAN은 항상 가능하다
✔ UNDO 블록도 DB 버퍼 캐시에 올라간다. SQL은 라이브러리 캐시에 캐싱된다.
✔ Latch : Shared pool 대기 이벤트는 하드 파싱을 할 때 발생한다.
✔ 응답시간분석 방법론 : Response Time = Service Time + wait Time
✔  대기 이벤트(Wait Event)는
1. 프로세스가 CPU를 OS에 반환하고 수면(SLEEP) 상태로 진입할 때, 
2. 프로세스가 필요로 하는 특정 리소스가 다른 프로세스에 의해 사용 중일 때,
3. 프로세스가 할 일이 없을 때 발생한다.
 
✔ 결과집합을 일부만 출력 = 부분범위 처리 = 소트 연산 생략 = 인덱스를 확인 후 DESC > group by 하는 등 inline view가 있으면 no_merge push_pred 반드시 고려
✔ 배타적인 테이블(아크)일 경우 인덱스 구성 보고 아래 중에 골라서 쿼리 작성

-- 작업구분코드가 인덱스에 있을 때
and DECODE(A.작업구분코드, 'A', B.개통접수번호, 'B', C.장애접수번호) = A.접수번호
또는 UNION ALL

-- 작업구분코드가 인덱스에 없을 때
-- 조건이 NULL = ~~ 로 변하니까 조인 시도하지 않음
and DECODE(A.작업구분코드, 'A', A.접수번호) = B.개통접수번호(+)
and DECODE(A.작업구분코드, 'B', A.접수번호) = C.장애접수번호(+)

 
✔ 변경이력 등 관련에서 12c 이상이라는 조건 보이면 닥 아래 쿼리 고고

-- 12C 부터 가능한 쿼리!!
SELECT E.사원번호, E.사원명, E.부서코드, H.직급 AS 최종직급
  FROM 사원 E, 발령이력 H
 WHERE E.사원번호 = H.사원번호 
   AND E.부서코드 = '10'
   AND (H.변경일자, H.이력순번) = (SELECT 변경일자, 이력순번
                                    FROM (SELECT 변경일자, 이력순번
                                            FROM 발령이력
                                           WHERE 사원번호 = E.사원번호 
                                           ORDER BY 변경일자 DESC, 이력순번 DESC)
                                    WHERE ROWNUM <= 1)
;

 
✔ 선분이력은 조건 풀어서..

and 시작일시 <= sysdate
and 종료일시 >= sysdate

✔ 선분이력인데 직전이라면 아래처럼!!

and 시작일시 < 최종변경일시 -- 같으면 안됨 작아야함
and 종료일시 >= 최종변경일시 - 1/(60*60*24) -- 일시분초라서 1초 빼는거임

 
✔ 인덱스를 이용한 테이블 액세스 비용(Cost) 산정 때 사용하는 것
비용 = 브랜치 레벨
+ (리프 블록 수 x 유효 인덱스 선택도)
+ (클러스터링 팩터 x 유효 테이블 선택도)
 
✔ I/O 비용 모델의 비용(Cost)는 디스크 I/O Call 횟수다. CPU 비용 모델에서는 I/O 시간, CPU 사용시간을 구한 후 Single Block I/O 시간으로 나눈 값을 비용으로 사용한다.
✔ 라이브러리 캐시 최적화 방안 : 커서 공유 가능하게 SQL 작성, 세션 커서 캐싱, 애플리케이션 커서 캐싱
✔ Open_cursors 파라미터 : 세션 당 오픈할 수 있는 커서 개수 지정
✔ 공유 커서 : SGA, 세션 커서 : PGA
✔ 트레이스 결과에서 Misses in ~~ during parse : 1 << 1이 하드파싱 회수임
✔ 트레이스 결과에서 Parse 부분이 1인데 Execute 등이 1이 넘는다? = 어플리케이션 커서 캐싱 된 거임
✔ JAVA는 Static SQL 지원 X. 또 쿼리 툴에서 수행하는 SQL은 모두 Dynamic SQL이다
✔ CURSOR_SHARING = FORCE 하면 상수를 강제로 바인드변수로 변경해 주는 것. 기본은 EXACT.
✔ use_concat 반대는 no_expand
✔ 서브쿼리에 rownum을 쓰면 unnesting이 안된다.
✔ 서브쿼리 unnest 하고 join 유도할 때!
not exists : hash_aj, nl_aj
1:M : hash_sj, nl_sj 등 

조인 유형	SQL문	힌트
중첩 루프 세미 조인	EXISTS	NL_SJ
해시 세미 조인	EXISTS	HASH_SJ
중첩 루프 안티 세미 조인	NOT EXISTS	NL_AJ
해시 안티 세미 조인	NOT EXISTS	HASH_AJ

 
✔ 조인 대상 조건 가져와서 쓰기(인라인뷰 등 말고) : 조건절 Transitive(이행)
✔ 정렬할 대상 집합을 SGA에서 읽어서 PGA의 Sort Area 공간에서 정렬 작업 완료하면 Optimal,
공간이 부족하여 Temporary Space에 I/O가 발생하고 그 저장한 중간 단계 집합을 'Sort Runs'라고 함.
디스크에 한 번만 쓰면 Onepass 소트, 여러 번 쓰면 Multipass 소트.
✔ sorts(memory) = 1 sort(disk) = 1이면 두 개의 소트 오퍼레이션 중 하나는 PGA, 하나는 Temporary에서 진행된 거임.
✔ 결과 집합이 어딘가?
데이터 양 확인(NL, HASH 선택)
데이터의 건수 비율 보고 필터링 할지 조인할지 고려
결과집합이 작은가? (대략 10만 이하?) -> NL or filtering 고려
결과집합이 크다? -> Full Scan, Hash조인 등등 고려
결과집합이 큰데(Hash) 그보다 조인 집합이 더 작으면서 중복데이터도 적다? -> 적은 쪽을 Build input 
 
✔ Parallel 쓸려면!!

-- 1. PARALLEL 쓰려면 써야됨.
ALTER SESSION ENABLE PARALLEL DML;

-- 2. APPEND나 PARALLEL에 NOLOGGING까지 하면 REDO 로그까지 최소화됨.
ALTER TABLE T_TABLE NOLOGGING;

-- 3. 인덱스나 FK, PK 꺼버려(명칭을 문제에서 지정해줄듯)
ALTER TABLE T_TABLE DISABLE CONSTRAINT T_TABLE_PK;


-- 4. PARALLEL 쓰면 자동 DIRECT PATH I/O 발생. APPEND 힌트 안써도 됨.
-- INSERT와 SELECT 둘다 PARALLEL을 맞춰줘야 병목이 없음.
INSERT /*+ PARALLEL(T 4) */ INTO T_TABLE T
SELECT /*+ PARALLEL(S 4) */ * FROM S_TABLE S;

-- 5. 다시 켜
ALTER SESSION DISABLE PARALLEL DML;
ALTER TABLE T_TABLE LOGGING;
ALTER TABLE T_TABLE ENABLE CONSTRAINT T_TABLE_PK;

✔ Nologging 옵션은 Insert에서만 가능! (update 에선 안된다)
✔ 야간 배치 등에서 중간 TEMP 테이블 생성하는 경우라면 NOLOGGING 추가하자

CREATE TABLE T_TEMP
NOLOGGING -- 요놈
AS
~~~

✔ 야간 배치 때 UPDATE라면 MERGE 문을 사용하면서 UPDATE 하지 않아도 될 부분에 대한 조건을 사용하는 방법으로 성능 개선할 수 있다.

UPDATE 고객 C
   SET 법정대리인_연락처 = NVL((SELECT 연락처 
                                  FROM 고객
                                 WHERE 고객번호 = C.법정대리인_고객번호)
                                 , C.법정대리인_연락처)
 WHERE 성인여부 ='N';
 
--> MERGE문을 쓰면서 UPDATE 절의 WHERE에서 한번 더 EXISTS 쓰면서 고객테이블을 조회하지 않아도
-- (*) 법정대리인_연락처가 이미 서브쿼리절대로 UPDATE 되어있는 경우를 추가로 필터링 할 수 있다.

MERGE /*+ leading(T) use_nl(S) index(S 고객_PK) index(T 고객_X3) */
 INTO 고객 T
USING 고객 S
   ON (T.성인여부 = 'N' 
       AND T.법정대리인_고객번호 IS NOT NULL -- 인덱스 사용가능해진다. 자세한 내용은 핵심노트 2 P35 33번 참고
       AND T.법정대리인_고객번호 = S.고객번호)
 WHEN MATCHED THEN
 UPDATE
    SET T.법정대리인_연락처 = S.연락처
 WHERE T.법정대리인_연락처 <> S.연락처 -- (*) 이미 예전에 바뀐 놈은 UPDATE 안한다.
 
 /*
 -- merge에서 insert
 INSERT -- INTO 쓰지마라이
 (T.~~~~)
 VALUES
 (S.~~~)
 */

✔ 야간 배치 + UPDATE 이용한 문제가 나오면 불필요한 UPDATE (이미 변경된 것을 같은 값으로 변경하는)에 포커싱을 맞춰야 하나 보다. merge를 이용해서 풀이하고, 한번 더 같은 테이블을 access 하더라도 update 수를 줄이는 것에 좀 더 포커싱을 하자.
✔ PL/SQL에 내장된 SQL은 DBMS가 자동으로 커서를 캐싱한다. 따라서 Parse Call은 1회만 발생한다.
✔ 쿼리 내장한 DB 저장형 함수 읽기 일관성 어쩌고 = 스칼라 서브쿼리 조인해야 된다
✔ 조건으로 사용한 내장형 함수 호출 회수를 최소화하려면 해당 조건을 인덱스 Driving 조건으로 사용해야 한다.
> Driving 조건은 1회만 호출한다.
> 체크 조건으로 사용되면 체크 회수만큼 호출된다. 인덱스에 없어서 R.A해도 똑같다.
 

✔ parallel 할 때 한쪽이 작고 한쪽이 크면 작은 쪽을  Broadcast 해라!!
 
✔️ UNPIVOT

WITH 고객(고객번호, 고객명, 고객구분코드, 집전화번호, 사무실전화번호, 휴대폰번호) AS (
SELECT '1', '김두환', 'VIP', '02-1234-4567', '032-123-1234', '010-2523-3262' FROM DUAL UNION ALL
SELECT '2', '강낭콩', 'VIP', '02-6341-6367', '', '010-6324-3242' FROM DUAL
)
SELECT *
  FROM 고객 UNPIVOT (컬럼값 FOR 컬럼명 IN (집전화번호, 사무실전화번호, 휴대폰번호))
;

UNPIVOT 결과

UNPIVOT 절의 칼럼값, 칼럼명 부분 보자
값 FOR 명임.
✔️ PIVOT!!

WITH 고객(고객번호, 고객명, 고객구분코드) AS (
SELECT '1', '김두환', 'VIP' FROM DUAL UNION ALL
SELECT '2', '강낭콩', 'VIP' FROM DUAL
),
고객연락처(고객번호, 연락처구분코드, 연락처번호) AS (
SELECT '1', 'HOM', '02-1234-4567' FROM DUAL UNION ALL 
SELECT '1', 'OFC', '032-123-1234' FROM DUAL UNION ALL 
SELECT '1', 'MBL', '010-2523-3262' FROM DUAL UNION ALL 
SELECT '2', 'HOM', '02-6341-6367' FROM DUAL UNION ALL 
SELECT '2', 'MBL', '010-6324-3242' FROM DUAL
)
SELECT A.고객번호, A.고객명, B.집, B.사무실, B.휴대폰
  FROM 고객 A, 고객연락처 PIVOT (MIN(연락처번호) FOR 연락처구분코드 IN ('HOM' AS 집, 'OFC' AS 사무실, 'MBL' AS 휴대폰)) B
 WHERE A.고객번호 = B.고객번호
   AND A.고객구분코드 = 'VIP'
;

 
✔️ Listagg

SELECT LISTAGG(expression, delimiter) WITHIN GROUP (ORDER BY expression)

SELECT A.고객번호, A.고객명, LISTAGG('('||B.연락처구분코드||')'||B.연락처번호, ', ') WITHIN GROUP (ORDER BY B.연락처구분코드, B.연락처번호) AS 연락처
  FROM 고객 A, 고객연락처 B
 WHERE A.고객번호 = B.고객번호
   AND A.고객구분코드 = 'VIP'
 GROUP BY A.고객번호, A.고객명

 
✔️ 전용(Dedicated) 서버 구성에서는 UGA, CGA가 PGA에 구성됨.
✔️ 한 세그먼트가 여러 데이터 파일에 구성될 수 있음. 여러 테이블스페이스는 안 됨.
✔️ Direct Path Write 시에는 "Exclusice 모드 TM Lock". 일반 DML일 때는 "RX 모드 TM Lock"
✔️라이브러리 경합 = 하드파싱
 
✔️Redo 로그의 목적
  🔹DB Recovery (=Media Recovery)
  🔹Cache Recovery (=Instance Recovery 에서 roll Forward 단계)
  🔹Fast Commit
 
✔️Undo 로그의 목적
  🔹Transaction Recovery
  🔹Instance Recovery의 rollback 단계에서 사용
 
✔️ 바인드 변수를 사용해도 SQL 사용자(user, schema)가 다르면 별도의 커서(SQL Area)를 사용한다
✔️ 세션 커서 캐싱은 트레이스에서 확인 못함. 아래 뷰에서 확인 가능

SELECT CURSOR_TYPE -- "SESSION CURSOR CACHED"
  FROM V$OPEN_CURSOR;

✔️ 버퍼캐시 확보 및 장애 복구 시 시간을 최소화 하기 위해 주기적으로 전체 또는 일부 Dirty 버퍼를 데이터파일에 기록하는 것 : Check Point
✔️ Hash join도 Probe input은 부분범위 처리 가능
✔️ Insert Session은 TX-X을 획득 하고 있음. 후에 Insert 시 PK 제약조건에 위배되면 TX-S을 요청하면서 블로킹됨.
✔️ 일반 DML은 RX(Row Exclusive) 모드로 lock을 획득하므로 문제에서 교착상태 관련 나오면 일반 dml의 row 수준인지 확인하자
✔️ 윈도우 함수에서 ORDER BY가 붙는 순간 결과가 ORDER BY 기준으로 DEPTH가 달라진다

WITH TMP(NO, DEPTCD, SAL) AS (
	SELECT 1, '001', 12000 FROM DUAL UNION ALL
	SELECT 2, '001', 62000 FROM DUAL UNION ALL
	SELECT 3, '002', 22500 FROM DUAL UNION ALL
	SELECT 4, '002', 35000 FROM DUAL UNION ALL
	SELECT 5, '002', 53000 FROM DUAL UNION ALL
	SELECT 6, '003', 10000 FROM DUAL UNION ALL
	SELECT 7, '003', 12000 FROM DUAL
)
SELECT NO, DEPTCD, SAL
     , SUM(SAL) OVER(PARTITION BY DEPTCD) AS NO_ORD
     , SUM(SAL) OVER(PARTITION BY DEPTCD ORDER BY NO) AS YES_ORD 
  FROM TMP
;

_ORD 컬럼 두개 확인

✔️ Direct Path Write(append, parallel) : Undo 안쌓. nologging : Redo도 최소화.
✔️ 여러 사용자 = 외부, 통합 관점 = 개념
✔️ 정규화 = 논리데이터 모델 때
✔️ Grouping > null 이면 1 즉, 1이면 합계
✔️ 관관선, 기설파
✔️ DML은 항상 ALL-ROWS로 최적화 함
✔️ 실행중인 SQL에 DDL 시도 시 -> Library cache lock
✔️ parallel에서 rownum은 row_number로 변환할것