데이터와 사람들 SQLP29기 교육 3주차(2025.01.18) 정리

누 적합계
면 분이력 끊기
최 종 결과값 조인 (게시판)
데복 - 데이터 복제
점 이력
선 분이력 조인
징 검다리(X) - 시험에 안나옴
아 크

1. 누적매출 구하기
비교문에서 큰쪽이 그룹바이 + 나머지, 작은쪽이 누적액

* ROWS(RANGE) BETWEEN UNBOUNDED PREDEDING ANG CURRENT ROW(UNBOUNDED FOLLOWING)

2. (면)선분이력 끊기
GREATEST[A.시작일자, B.시작일자]
LEAST[A.종료일자, B.종료일자]
AND A.시작일자 <= B.종료일자
AND A.정료일자 >= B.시작일자

3. 데이터 복제를 통한 소계 구하기
CONNECT BY LEVEL -> 카테시안 곱
GROUPING -> NAMING 할때 주로 사용 ( 그룹된 결과값이면 1 아니면 0 )
GROUP BY ROLLUP -> 지정한 컬럼별 소계 출력
GROUPING SETS -> UNION ALL에서 각각 GROUP BY한 결과값을 한 쿼리로 합칠 수 있음

4. 상호배타적 관계의 조인
외래키 분리 방법 -> 온라인권 테이블, 실권 테이블의 각각의 PK(기본키)값이 상품권결제 테이블의 FK(외래키)로 구분
외래키 통합 방법 -> 상품권결제 테이블에서 상품권번호(FK)가 상품권구분(예시로 1이면 온라인 2이면 실권)에 의해 온라인인지 실권인지 구분

외래키 분리 방법

외래키 분리인 경우 온라인권 테이블과 실권 테이블이랑 조인할 때 아우터 조인 해야함.
그래야 둘 중 하나라도 값이 없을 경우 결과값이 나올 수 있기 때문이다

[외래키 분리 쿼리]

상품권결제 A, 온라인권 B, 실권 C
AND B.온라인권번호(+) = A.온라인권번호
AND C.실권번호 (+) = A.실권번호

외래키 통합 방법

DB는 상품권구분이 1일때 뭔지 2일때 뭔지를 알 수 없기 때문에 사용자가  DECODE문으로 정의
인덱스가 [결제일자 + 상품권구분] 인 경우

상품권결제 A, 온라인권 B, 실권 C
AND B.온라인권번호(+) = DECODE(A.상품권구분, '1', A.상품권번호)
AND C.실권번호 (+) = DECODE(A.상품권구분, '2', A.상품권번호)

인덱스가 [상품권구분 + 결제일자] 인 경우

WHERE A.상품권번호 = 1
AND B.온라인권번호 = A.상품권번호

UNION ALL

WHERE A.상품권번호 = 2
AND C.실권번호 = A.상품권번호

외래키 통합의 경우 인덱스 설계에 따라 쿼리도 다르게 적용해야 한다라는 것만 알아두면 됨.

5. 최종 출력 건에 대해서만 조인하기 - 게시판 조회 - 쿼리 블록이 항상 3개

게시판 쿼리 읽기순서
1. 가장 안쪽 ORDER BY - 테이블 랜덤 액세스를 없애는게 중요(인덱스 설계시 [=조건 + ORDER BY 컬럼 + 나머지 컬럼] 순으로 설계해야 정렬을 생략할 수 있음 )
2. 바깥쪽 ROWNUM - 내가 40건을 뽑을꺼다 하면 +1해서 ROWNUM <= 41이렇게 해야함.(혹시나 다음 페이지를 읽을 경우 미리 다음 페이지의 데이터가 있는지 확인하는 작업으로 효율을 더 높일 수 있음) + 절대로 ROW_NUM , RNUM 이런식으로 쓰면 안됨 그러면 오라클 내부적으로 정한 스탑키 알고리즘이 작동하지 않음**
3. 가장 바깥쪽 - 이건 그냥 안에서 읽은거 마지막에 뿌려주는 용도고 최종적으로 몇건 가져올건지 정의 하면 됨( BETWEEN 31 AND 40 ) 이런식으로

6. 징검다리 테이블 조인을 이용한 튜닝 - 시험 안나와서 패스 함

7. 선분 이력 조인 - 회사에서 직원 인사 이력 중 최종 전 단계를 조회하는 것

보통 시작일자, 종료일자, 최종변경일자가 있다.

최종 전 단계를 조회하기 위해 아래와 같이 쿼리를 짬( 직전 선분 구하기라고도 함 )

고객 C, 고객등급이력 H
AND H.시작일자 < C.최종변경일자
AND H.종료일자 >= TO_CHAR(TO_DATE(C.최종변경일자)-1),'yyyymmdd')

8. 점이력 조회

- 특정 상품의 최종 값 조회 - 선분이 아니라서 시작일자 종료일자 대신 변경일자를 사용한다.
1. 인라인 뷰 쿼리를 통해 1건만 읽도록 처리 ( =조건 + ORDER BY 컬럼 인덱스 설계 필요 )
2. 바깥쪽 쿼리에서 ROWNUM <=1 로 한건만 가져오기

- 전체 상품의 최종 값 조회
1. ROW_NUMBER() OVER(PARTIOTION BY 상품ID ORDER BY 변경일자 DESC, 순번 DESC) R_NUM
순위 함수 사용하여 상품ID별 뒤에서부터 순위 매기기
2. R_NUM = 1 조건으로 상품ID별 1등인 결과값 추출

- 전체 상품별, 상태코드 별 최종 값 조회
GROUP BY 속에 GROUP BY라고 생각하면 편함
문제에는 상품ID 별, 상태코드별 이라고 나와있는데 두개로 데이터를 뽑으려다보니 추가적으로 그룹한 상태에서 한 번더 다른 컬럼별(최종변경일자의 최종순번, 최종변경일자의 최종변경순번의 상품가격을 뽑아오라고 할 때 사용)

,MAX(순번) KEEP (DENSE_RANK LAST ORDER BY 변경일자) 최종순번
,MAX(상품가격) KEEP (DENSE_RANK LAST ORDER BY 변경일자, 순번) 최종상품가격
~~~
GROUP BY 상품ID, 상태코드

이렇게 하면 됨.

- 원장과 이력테이블의 조인 ( 이런 패턴은 외워야 함 )
문제의 테이블 구조가 1:N 관계라면
WHERE절 서브쿼리안에서 메인쿼리 컬럼을 조인하는 방식을 사용해야 함 --12C NEW FEATURE

1. 미리 문제에 나온 구분코드 값으로 조건을 필터링 하고
2. AND(H.변경일자, H.순번) = 서브쿼리안에서 메인쿼리 컬럼 조인하여 1건만 추출

*주의사항 ROW_NUMBER로 최종 1건을 조회하여 구분코드로 필터링할 경우 쓸모없는 값까지 스캔하기 때문에 비효율이 발생한다. 그래서 12C NEW FEATURE 기능을 사용해야 정답임. ( ROW_NUMBER쓸거면 USE_HASH사용해야 함 )

---

제 6장 고급 SQL 튜닝

1. CASE문 활용

월별납입방법별집계 테이블을 납입방법에 따라서 여러번 INSERT할 시 불필요한 액세스 발생
1. DECODE문으로 분기하여 비효율 제거
OR
2. PIVOT 함수 사용

시험에 두 가지 패턴이 종종 나오니 외워야 함.( 두 방법의 성능상 큰 차이는 없음 )

2. UNION ALL을 활용한 M:M 관계의 조인

FULL OUTER JOIN을 대체 -> UNION ALL + GROUP BY로 튜닝
1. SELECT 절에서 서로 다른 값은 TO_NUMBER(NULL) 처리
2. UNION ALL처리한 결과값을 GROUP BY 처리
3. NULL인 값은 0으로 대체하기 위해 NVL처리

3. WITH 구문 활용 - 인라인 뷰랑 똑같은 기능

1. ORACLE 9I 버전부터 지원
2. MATERIALIZE 방식/*+MATERIALIZE*/ - 내부적으로 임시 테이블을 생성함으로써 반복 재사용
3. INLINE 방식/*+INLINE*/ - 참조된 횟수만큼 런타임 시 반복 수행

WITH문은 튜너들은 잘 안쓰려고 하는 방식 중 하나

---

 

제 2절 소트 튜닝

SORT RUNS - 소트량이 많아 디스크의 TEMP 영역에 저장한 중간 집합

메모리 소트 - INTERNAL SORT OR OPTIMAL SORT라고 부름, 메모리 내에서 완료 하는 것

디스크 소트 - EXTERNAL SORT, ONEPASS 소트(디스크에 한 번만 씀), MULTIPASS 소트(디스크에 여러 번 씀)

소트를 발생시키는 오퍼레이션의 종류 6가지 - 조까유 ( JO GA UW )

J  SORT J OIN
O  SORT O RDER BY
G  SORT G ROUP BY
A  SORT A GGREGATE
U  SORT U NIQUE
W SORT W INDOW


J - 소트머지 조인을 수행할 때 발생
O - ORDER BY절 사용
G - 그룹별 집계를 수행할 때 발생, ORDER BY절과 함께 명시하지 않으면 대부분 HASH GROUP BY 방식 도입 , 결과 값이 정렬되지 않는다.
A - 전체 로우를 대상으로 집계를 수행할 떄 발생 MAX, MIN, SUM (실행계획에 SORT라는 표현을 사용하지만 실제 소트가 발생하진 않는다.)
U - DISTINCT, 중복 레코드 제거를 위해 SORT UNIQUE 오퍼레이션이 먼저 수행, UNNESTING
W - 분석함수를 수행할 때 발생, OVER, PARTITION BY 같은 거

소트튜닝 요약


1. DISTINCT는 EXISTS로 대체한다. - EXISTS는 조인 시 1건이라도 있으면 다음건부터 검색하지 않음. (실행계획에 NESTED LOOP SEMI로 표시 됨)

2. 불필요한 COUNT(*) 연산 제거 - 풀스캔이니까 1건이라도 있다면~ 으로 찾는 쿼리로 사용하기

3. 인덱스를 이용한 소트 연산 대체 - 인덱스 설계 시 SORT연산 생략하려면 [ =조건 + ORDER BY컬럼 + 나머지로 설계 ]

4. 소트를 완료하고 데이터 가공하기 - 인라인 뷰로 데이터 정렬된 상태로 추출하고 바깥쪽 메인 쿼리에서 가공하기(LPAD든 뭐든)

5. TOP - N 쿼리 작동 - 인라인 뷰 정렬 생략한 후에 바깥쪽 메인 쿼리에서 ROWNUM 사용 - ROWNUM 사용 시 선언한 크기만큼 배열을 할당함
* 1. ORDER BY 컬럼 인덱스 선언(정렬 생략하라는 거임) 2. ROWNUM 쓰기
* R_NUM, ROW_NUM 이딴 거 쓰면 TOP - N 쿼리 미작동하니 주의
* RANK, ROW_NUMBER 사용 시에도 TOP - N 알고리즘 작동 ( MIN,MAX 안됨 )

6. 소트 영역 크기 조정
1. WORKAREA_SIZE_POLICY = AUTO -> DBMS내 자동 계산
2. WORKAREA_SIZE_POLICY = MANUAL -> 수동전환

작업공간은 최대 2GB

대량 배치 작업 시
ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL -> 수동전환 하고
ALTER SESSION SET HASH_AREA_SIZE = 2,147,483,647 -> 대량 해쉬조인 필요 시 이거 선언
ALTER SESSION SET SORT_AREA_SIZE = 2,147,483,647 -> 대량 정렬 작업 필요 시 이거 선언

*주의할 점은 PARALLEL작업 시 작업공간 사이즈 설정을 같이 사용해선 안됨. - 병렬처리라 메모리 왕창 갖다써서 큰일남


게시판 쿼리 TOP - N 쿼리 작동하도록 변경 하기
1. 사용자 정의 함수가 있는지? -> 스칼라 서브쿼리(중복된 값이 많으면) OR OUTER JOIN으로 변환(중복된 값이 없으면)
2. 인덱스가 정렬을 생략할 수 있도록 설계 돼있는지? - [=조건 + ORDER BY컬럼 + 나머지]
3. 바깥쪽 메인쿼리에서 ROWNM이라고 정확하게 썼는지? - RNUM, ROW_NUM 안됨

---