데이터와 사람들 SQLP29기 교육 4주차(2025.01.25) 정리
제 3절 파티션 활용
1. 파티션 개요
파티션 키에 따라 물리적으로 별도의 세그먼트에 데이터 저장
-> 파티션 키(입력일자 같은 거 202401, 202402 ... 202412 등)
<특징>
보관 주기가 지난 데이터를 별도 장치에 백업하고 삭제하는 작업 시 높은 효율
특정 파티션만 풀 스캔으로 높은 효율 가능 -> 시퀀셜 액세스, 멀티블록 I/O, 테이블 랜덤 액세스 없어짐으로 효율 엄청 좋음
논 파티션 테이블 - dml 작업 시 인덱스 리빌딩 필요
로컬 파티션 테이블 - 해당 파티션만 제거하므로 유지보수에 용이
2. 파티션 유형
1) Range 파티셔닝
파티션 키 값의 범위로 분할, 주로 날짜 칼럼을 기준, 다중 칼럼 활용 가능
2) Hash 파티셔닝
나머지 함수랑 같음, 데이터가 고르게 분산되도록 dbms가 관리 -> 각 로우의 저장위치 예측 불가
병렬처리 시 성능효과 극대화
dml경합 부산에 효과적
다중 컬럼 활용 가능
3) List파티셔닝
불연속적인 값의 목록을 각 파티션에 지정
단일 컬럼만 가능
순서와 상관없이 사용자가 미리 정한 그룹핑 기준에 따라 데이터를 분할 저장( 판매 데이터를 지역별로 분할 )
4) Composite 파티셔닝
range나 list 파티션 내에 또 다른 서브 파티션 구성
range나 list 파티션이 갖는 이점 + 각 서브 파티션 구성의 이점
파티션 구성 방법 - 가끔 4지선다 나옴
PARTITION BY RANGE(매출일자)
(
PARTITION P2019_1Q VALUES LESS THAN ('20190401') -- 1분기
PARTITION P2019_2Q VALUES LESS THAN ('20190701') -- 2분기
PARTITION P2019_3Q VALUES LESS THAN ('20191001') -- 3분기
PARTITION P2019_4Q VALUES LESS THAN ('20200101') -- 4분기
PARTITION P_MAX VALUES LESS THAN (MAXVALUE) --나머지
);
3. 파티션 Pruning
불필요한 파티션을 액세스 대상에서 제외하는 기능을 파티션 Pruning 이라고 한다.
정적 파티션 Pruning -> 파티션 키 칼럼을 상수 조건으로 조회하는 경우 ( 실행계획에 pstart , pstop이 파티션 키 값으로 명시 됨)
동적 파티션 Pruning -> 파티션 키 칼럼을 바인드 변수로 조회하는 경우 ( 실행계획에 pstart , pstop이 KEY 값으로 명시 됨)
파티션 Pruning은 dbms가 지능적으로 수행하는 메커니즘으로 사용자가 크게 신경쓰지 않아도 된다.
단, 파티션 키 카럼에 대한 가공이 발생하지 않도록 주의한다. -> 안그러면 수직적 탐색이 엄청나게 발생한다.
pk칼럼은 무조건 where 조건에 있어야 함 -> 개발자가 꼭 지켜줘야 하는 요구사항
파티션 테이블이 룩업 테이블이 때 파티션 키를 where 조건에 안주면 수직적 탐색 엄청 함. -- 주의사항
4. 인덱스 파티셔닝
local 파티션 인덱스 -> 테이블 파티션과 1:1로 대응 되도록 파티셔닝한 인덱스
인덱스 파티션 키를 사용자가 따로 지정하지 않으며, 테이블과 1:1 관계를 dbms가 자동 유지
global 파티션 인덱스 -> 테이블 파티션과 독립적인 구성을 갖도록 파티셔닝한 인덱스
Prefixed 파티션 인덱스 -> 파티션 인덱스를 생성할 때, 파티션 키 칼럼을 인덱스 선두 칼럼에 위치 ( 입력일자 + ... )
Nonprefixed 파티션 인덱스 -> 인덱스 선두칼럼이 아닌 곳에 파티션 키 칼럼 위치 ( ... + 입력일자 ), 파티션이 값이 인덱스에 없는 경우
Local + nonprefixed -> oltp 환경에 적합 대부분 일자가 index 구성 시 뒤쪽으로 오기 때문( 따라서 인덱스 매칭도가 중요함 )
제 4절 배치 프로그램 튜닝
1. 배치 프로그램 튜닝 개요
사용자와 상호작용 없이 대용량 데이터를 처리하는 일련의 작업 or 정기적으로 반복 수행 또는 정해진 규칙에 따라 자동 수행
정기 배치, 이벤트성 배치, 온디맨드 배치
온라인 프로그램 -> 최초 응답속도 최적화
배치 프로그램 -> 전체 처리속도 최적화
절차형 프로그램 -> 커서를 열고 루프 내에서 다른 sql 반복 처리 형태, random 액세스, single 블록, 중복 액세스, 반복적인 dbms call 발생
One sql위주 프로그램 -> 집합적으로 정의된 여러 sql을 단계적으로 실행, parse, fetch, excute, hash join이 유리, 병렬 처리 유리
2. 병렬 처리 활용
Granule - 반땅 나누는거라고 생각하기
블록 기반 Granul
파티션 여부에 상관 없이 병렬도 지정 가능
Granul 사랑은 QC 가 수행 (QC는 서버 프로세서)
실행계획에 PX BLOCK ITERATOR로 표기
파티션 기반 Granul
하나의 파티션을 하나의 병렬 프로세스가 처리
병렬도는 전체 파티션 개수 이하로 설정 가능
실행계획에 PX PARTITION RANGE ALL OR PX PARTITION RANGE ITERATOR 로 표기
Full partition wise join -> 두 테이블 모두 join 컬럼 기준으로 파티셔닝 되어 있는 경우
Partial partition wise join -> 하나의 테이블만 join 칼럼 기준으로 파티셔닝 되어 있는 경우
Dynamic partition wise join -> 두 테이블 모두 join 컬럼 기준으로 파티셔닝 되어 있지 않은 경우
1. 단일 테이블 패러럴 처리 시 order by + group by 유무에 따른 실행계획
2. 두 테이블 모두 파티션(분배 필요없음) or 한쪽만 파티션이 경우(파티션 없는쪽이 분배)
3. 둘다 해시펑션 파티션(분배 없고 해시펑션으로 던진) or 작은 쪽에서 분배(broadcast)
4. 노패러럴 + broadcast or 노패러럴 + 해쉬
PQ_Distribute -> 분배 방식이라고 함 [이너 분배아우터 분배이너] 순으로 분배
제 4장 옵티마이저 원리
옵티마이저 - 최저 비용의 실행 계획 선택
RBO - 정해 놓은 규칙에 따라 실행 계획 선택
CBO - 쿼리를 수행하는데 소요되는 일량 또는 시간을 비용으로 산정하여 최적화(객관식 빈칸 자주 나왔대)
SQL 최적화 과정
최적화 목표
1. Alter system set optimizer_mode = all_rows; --전체 처리속도 최적화
2. Alter session set optimizer_mode = first_rows; --최초 처리속도 최적화
3. Select /*+ all_rows */ --이 SQL만 수행
[Oracle에서 옵티마이저 모드 변경 힌트]
Select /*+ first_rows */
[SQL Server에서 옵티마이저 모드 변경 힌트]
Select * from t where ... OPTION[fast 10]; --옛날 단답식에 많이 나옴
옵티마이저 행동에 영향을 미치는 요소
1. SQL 연산자 형태
2. 옵티마이징 팩터
3. DBMS 제약 설정
4. 옵티마이저 힌트
5. 통계 정보
6. 옵티마이저 관련 파라미터
7. DBMS 버전과 종류
테이블, 인덱스, 컬럼은 오브젝트 통계
시스템 통계는 하드웨어
통계정보를 이용한 비용계산 원리
1. 선택도 -> 1 / distinct value 수(성별이면 2(남,여)
2. 카디널리티 -> 총 로우 수 x 선택도
distinct가 10이면 선택도는 0.1
row숙 1,000이면 100개의 결과집합으로 예상
3. 히스토그램 -> 각 값에 따라 데이터가 몇 건 존재하는지를 그래프로 표현
4. 높이 균형 히스토그램 -> distinct가 많을 때 (255개 이상 일 경우) 그래프로 표현
5. 상위 도수분포 히스토그램 -> 상위 n개의 값 빈도수 저장
6. 하이브리드 히스토그램 -> 도수분포와 높이균등의 특성 결합
옵티마이저 힌트가 무시되는 경우
1. 문법적으로 안 맞게 기술한 힌트
2. 의미적으로 안 맞게 기술한 힌트
3. 잘못된 참조 사용(없는 테이블 또는 인덱스 지정)
4. 논리적으로 불가능한 액세스 경로(=조건 없는데 hash조인 사용)
5. 버그
6. 쿼리 변환
성능상 옵티마이저가 빨라도 사용자 힌트가 있으면 힌트가 적용됨.
Sql server는 에러를 띄우지만 oracle은 그냥 수행한다.
힌트와 힌트 사이 컴마는 안됨.
힌트인자에는 콤마 써도되고 안써도 됨.
서브쿼리 unnesting -> 서브쿼리를 메인쿼리와 같은 레벨로 변환
1. 서브쿼리 실행계획 -> filter
2. Unnesting - 인라인 뷰 사용 시 실행계획 -> view
3. View merging 시 실행계획 -> nested loops
**
Select *
From dept d, emp e -- dept가 1 , emp가 m쪽 집합일 때
-----> 방향은 1쪽 드라이빙 nested loops semi 조인 발생
<----- 방향은 m쪽 드라이빙 sort unique 발생
1. push_suq ( 사용방법 )
-> 조인과 subq - 원래는 조인 후 subq(메인 -> 조인 -> subq)순 이지만 (메인 -> subq -> 조인) 순으로 진행
2. 메인쿼리에서 던져주는 컬럼을 index 포함 ( 사용방법 )
-> index끼리 연산 후 메인 테이블과 랜덤 액세스하기 때문
3. 서브쿼리의 캐싱효과가 클때만 사용한다. ( 사용요건 )
-> 메인쿼리(M), 서브쿼리(1) -> 이럴때만 push_suq를 사용한다.
즉, 실행계획에 filter가 있는데 메인쿼리 -> 조인 -> subq 했을 때 결과가 비효율인 경우 ( subq는 2건 읽는데 메인과 조인 시 만건 이상이라던가...)
실행계획 읽기순서 예외처리되는 3가지
1. 스칼라 서브쿼리
2. 해시조인
3. push_seq
서브쿼리 push_seq 실행계획 순서 시험에 잘 나온다고 함.
주의할 점은 실행계획상 서브쿼리가 메인쿼리와 같은 레벨로 풀린다면 메인쿼리에서 서브쿼리로 인덱스를 보낸거라고 생각하면 됨.
2. 메인쿼리에서 던져주는 컬럼을 index 포함 ( 사용방법 ) <---- 이거 사용했다는 뜻
요약
1. 코드값처럼 구분값이 얼마없는 테이블인 경우 먼저 풀어서 메인쿼리와 조인시키면 좋다.
2. 메인쿼리 = 조건과, 서브쿼리로 넘겨주는 조인조건을 index로 만들어야 랜덤 액세스를 효과적으로 줄인다.
뷰 머징이 불가능한 경우 -> 집 C R 함수 그룹 d는 가능( 집계 함수, 커넥트 바이, 로우넘, 집계함수, 분석함수는 불가능 그룹바이, distinct는 가능)
조건절 pushing
1. 메인쿼리 조건을 쿼리블록 안으로 밀어 넣는 것
2. 인라인뷰에서 메인쿼리 액세스 불가
3. 서브쿼리는 가능
4. no_merge push_pred 같이 사용
5. 12c버전부터 LATERAL문으로 대체 가능