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

8. 인덱스 스캔 효율

인덱스 선행컬럼이 등치[=] 조건인 경우
-> 조건을 만족하는 레코드가 한데 모여 있다. ( 필요한 범위만 스캔한다. )

인덱스 선두 컬럼이 BETWEEN 조건인 경우
-> 조건을 만족하지 않는 레코드까지 스캔 후 버리는 비효율 발생

BETWEEN조건을 IN-LIST로 변경 했을 경우
-> UNION ALL 사용한 효과를 얻어서 등치[=] 조건과 같은 범위 스캔을 할 수 있다. ( IN 인자값이 너무 많으면 오히려 수직적 탐색 비용이 늘어서 무조건 좋은건 아님 )

결론
-> 체크조건 앞의 컬럼들이 변별력이 좋아서 검색 구간을 줄였다면 BETWEEN 조건이 오히려 유리하다.
즉, 뭐가 유리한지 판단하려면 IN이나 BETWEEN으로 바꿔서 실행계획 확인해보면 알 수 있음

인덱스가 C1 + C2 + C3로 이루어져 있을 때
C1+C2의 변별력이 좋다면 -> SKIP SCAN이 유리하고
C1+C2의 변별력이 좋지 않다면  -> IN-LIST가 유리하다.

범위검색 조건을 남용할 때 발생하는 비효율
-> 개발자의 편의를 위해 특정 조건을 LIKE로 처리하는 경우 (LIKE :date || '%')

위와같이 LIKE조건으로 인해 비효율이 생기는 경우 해결하는 방법 문제가 나올 경우 정답은?
1. SQL분기 -> UNION ALL로 :date가 NULL 인경우 + :date가 NULL이 아닌 경우 2가지 작성
2. 주문일자 칼럼이 NOT NULL인 경우 -> NVL(:date, 주문일자) 이런식으로 NVL 처리만 해주면 됨
-> NOT NULL컬럼이여야 하는 이유는? 1=1은 TRUE고 NULL=NULL은 FALSE기 때문

같은 컬럼에 두 개의 범위검색 조건 사용 시 주의 사항
1. 똑같은 컬럼을 조회조건에 사용할 경우 드라이빙에 유리한 조건을 쓰면 된다. ( 드라이빙이 아닌 조건에 TRIM으로 감싸주기 )

BETWEEN과 LIKE 스캔 범위 비교
1. BETWEEN을 사용하면 성능적으로 손해볼 것은 없다.
2. 편리에 의해 LIKE를 쓰지 말 것 ( BETWEEN >= LIKE )

***인덱스 구성에 따른 BETWEEN , LIKE 사용방법
판매구분 = 등치조건[=]
판매월 = BETWEEN OR LIKE 조건
인덱스 구성 예) 판매구분 + 판매월 -> 판매월까지 드라이빙 되기 때문에 BETWEEN이나 LIKE 둘 다 상관없음
인덱스 구성 예) 판매월 + 판매구분 -> 판매월만 드라이빙 되기 때문에 BETWEEN 써야 비효율이 줄어듬

선분이력의 인덱스 스캔 효율
1. 최근 데이터를 주로 읽을 때 인덱스 구성 -> 종료일자 + 시작일자
2. 과거 데이터를 주로 읽을 때 인덱스 구성 -> 시작일자 + 종료일자
3. 인덱스 수정 불가능한 상황인 경우 -> INDEX_DESC힌트 사용
4. 중간 지점을 읽어야할 경우 -> 어떠한 인덱스 든 비효율이 발생하지만 ROWNUM <= 1 조건 활용

위에 내용을 쭉 정리하면 (인비스 범같이선)
-> 인씨 성을 가진 비서(스)가 범같이 서(선)있네

인 덱스 매칭도
비 BETWEEN, IN-LIST
스 SKIP SCAN
범 위 조건 남용
같 은 컬럼 조회
이 BETWEEN VS LIKE
선 분이력

인덱스 스캔 효율은 위와 같이 정의할 수 있다.

---

9. 인덱스 종류

가. B-TREE 인덱스
1. Unbalanced Index
2. Index Skew 현상
3. Index Sparse
4. 인덱스 재생성

나. 비트맵 인덱스
1. distinct value개수가 적을 때 사용, oltp환경에서 안씀
2. 팩트성 테이블에 주로 사용
3. dml 부하 과다라서 dw환경에 많이 사용
4. 대용량 데이터 검색 성능 향상에 효과적
5. or조건을 써도 성능에 문제가 없는 장점 ( 0과 1로 구성돼있어서 그럼 )

다. 함수기반 인덱스( 거의 안씀 )

라. 리버스 키 인덱스( 거의 안씀 )

마. 클러스터 인덱스 ( 1주차에 설명 함 )

바. 클러스터형 인덱스 / IOT ( 이것도 )

---

10. 인덱스 설계

결합 인덱스 구성을 위한 기본 공식
1. 조건절에 항상 사용되는가?
2. =조건으로 사용되는가?
3. 카디널리티가 좋은가?
4. 소트 오퍼레이션을 생략 가능한가?

즉,

1. =조건
2. order by에 있는지
3. 나머지 조건

순서로 인덱스 구성을 하면 됨

비용 순서
1. 테이블 랜덤 액세스(where절 조건, index컬럼 사용) -> 2. 수직적 탐색 -> 3. 수평적 스캔(드라이빙)

---

제 2절 조인의 원리와 활용

[조인 기본]
조인 시 먼저 읽은 것을 드라이빙 테이블 or 아우터 테이블
조인 시 나중에 읽은 것을 룩업 테이블 or 이너 테이블

룩업 테이블은 depth가 얇은게 유리하다.

즉, 작은쪽에서 큰 쪽으로 조인하는게 유리함.

Nested Loop 조인
1. 중첩 루프
2. 대용량 처리 시 한계
3. 인덱스 구성이 매우 중요
4. oltp환경에 적합
5. 룩업 테이블의 조인 컬럼은 주로 인덱스가 필요하다. ( 드라이빙 테이블에 따라서 조인 컬럼이 어느쪽으로 갈지 확실하게 알아야 함 )

Sort Merge 조인
1. 소트 단계와 머지 단계로 나뉨( 양쪽 집합을 조인 컬럼 기준으로 정렬하는게 소트 / 정렬된 양쪽 집합을 merge하는게 머지 )
2. 조인을 위해 실시간으로 인덱스를 생성하는 것과 같은 효과
3. 양쪽 집합을 정렬하고 NL조인하는거랑 같음, 양쪽이 정렬돼있다는 것만 다를 뿐
4. PGA 영역에서 처리해서 빠르다.
5. 조인 컬럼에 인덱스 유무 상관없음
6. 소트 부하만 감수하면 NL조인보다 유리
7. 양쪽이 정렬돼있기 때문에 부분범위 처리가 가능*
8. 조인 조건식이 등치 조건이 아닐 때 사용하면 좋음 ( 그런 경우는 별로 없지만.. )

Hash 조인
1. 두 개의 테이블 중 작은 집합을 읽어 Hash Area에 적재 -> 빌드 인풋
2. 반대쪽 큰 집합을 읽어 해시 테이블을 탐색 -> 프로브 인풋
3. 해시 함수 사용
4. 등치[=] 조건만 사용 가능
5. 랜덤 액세스 부하 + 소트 부하 없음
6. 수행빈도가 낮고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 조인 할 때 사용( 배치 )
7. HASH조인 실행 계획
7-1. 등호형 -> 빌드 인풋 1개 나머지 프로브 인풋, 실행계획 생긴게 > 이렇게 생겨서 등호형, 위에서부터 읽어내려오면 됨, 빌드 인풋 1개만 SWAP_JOIN_INPUTS 나머지 프로브 인풋은 NO_SWAP_JOIN_INPUTS
7-2. 계단형 -> 프로브 인풋 1개 나머지 빌드 인풋, READING 힌트는 아래서부터 위로 읽어야하는 특징이 있음( 근본 실행계획 순서에 어긋나는 유형 ), 아래서 P.I이 완료되어야 다음 빌드 인풋과 조인할 수 있기 때문, 프로브 인풋 1개만 NO_SWAP_JOIN_INPUTS 나머지 빌드 인풋은 SWAP_JOIN_INPUTS

처음 빌드 인풋 + 프로브 인풋은 생략 가능( 리딩 순서를 제대로 기입 했다면 )

**소트 머지와 해시 둘다 PGA 공간을 사용하고 PGA 공간의 장점은 래치 흭득 과정이 없다는 것이다.

조인 순서의 중요성
***Sort Merge 조인 Dist Sort가 필요한 경우를 제외하고(스와핑 떄문에), 전부 작은 쪽에서 큰쪽으로 하는 것이 유리함.

Outer 조인
[NL JOIN, SORT MERGE JOIN ]
1. [+]의 반대쪽이 드라이빙 테이블로 선택
2. LEADING 힌트로 순서 변경 불가능 -> 논리적 모순

[HASH JOIN]
1. OUTER TABLE을프로브 인풋으로 사용할 수 있어서 LEADING 힌트로 순서 변경 가능

스칼라 서브쿼리를 이용한 조인
1. 캐싱한다는 장점이 있음
2. 중복값이 있으면 유리하고, 없을 경우 비효율 발생( 공통코드를 스칼라 서브쿼리로 사용하는 이유이기도 함 )
3. 배치작업에 사용하면 안됨..
4. 1개의 결과값만 나온다면 WHERE절에 나와도 스칼라 서브쿼리임
5. INTAS 같은 스칼라 서브쿼리 문제가 나오면 JOIN문으로 풀어야한다.
6. 1개 이상의 값을 리턴하고 싶을 경우 FROM절에서 한줄로 가져와서 잘라서 쓰기.

---

8. 고급 조인 테크닉

1. 누적매출 구하기
-> 조건절 부등호를 보고 작은쪽이 누적액, 큰쪽이 그룹바이절과 나머지(SELECT절)
-> 만약 윈도우 함수를 활용해서 누적매출을 구한다면 아래 함수를 외울 것

***무조건 외우기
SUM[매출액] OVER[PARTITION BY 지점 ORDER BY 판매월 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW] 누적액

CURRENT ROW -> 현재(나까지)
PRECEDING -> 위로
FOLLOWING -> 아래로
UNBOUNDED -> 범위가 정해지지 않은 ( 끝까지 )

ROWS BETWEEN이 아니라 RANGE BETWEEN인 경우 내가 갖고있는 값 기준이라서 동률이 있을 수 있다.