IT/데이터자격증 기록

[SQLD/SQLP] 전문가가이드 3과목 4장 인덱스와조인 암기 요약 정리 (+오라클성능고도화)

JJo 2018. 10. 10. 13:16



3과목 4장 인덱스와조인 요약정리




'무조건 인덱스를 타는것이 좋다'

'무조건 인덱스를 타는것은 좋지 않다'

라는 말이 있을 때 어떤 상황에서인지가 가장 중요합니다.

특정 테이블에서의 데이터는 변동이 심한지

데이터가 많은지 선택도는 어떤지 업무상 많이 쓰이는지..

와 같은 전제조건들이 너무 많아 

명확한 인덱스를 만드는 규칙이나

조인방법은 없다고 말할 수 있습니다.


그래서 3과목이 더 어려운 이유이기도 하구요..

여러번 반복공부하면서 자신만의 기준을 세울 수 있으면 좋을 것 같습니다!

물론 그 기준이 잘못되었는지 봐줄 수 있는분이 있다면 더 좋을거구요







클러스터 인덱스
- 클러스터 키 값이 같은 레코드가 한 블록에 모이도록 저장하는 구조
- 클러스터 인덱스의 키 값은 Unique, 레코드와 1:M

클러스터 테이블 관련 성능이슈
1. DML부하
2. Direct Path Loading수행 X
3. 파티셔닝 기능을 함께 적용X IOT는 Partitioned IOT가능
4. 다중 테이블 클러스터를 Full Scan할 때는 다른 테이블 data까지 스캔하므로 불리
5. 클러스터 테이블에는 Truncate Table을 사용할 수 없다.

클러스터형인덱스 / IOT
- 별도의 테이블을 생성하지 않고 모든 행 데이터를 인덱스 리프페이지의 저장
- Direct Path Insert가 작동하지 않는다
- 활용
  1. 넓은 범위 탐색
  2. 크기가 작고 NL로 반복룩업
  3. 컬럼수가 적고 로우수가 많은
  4. 데이터 입력조회 패턴 상이

두개 이상의 인덱스 사용하는 법
- And-equal, Index combie, Index Join

인덱스 CF가 좋다
- 인덱스 정렬순서와 테이블 정렬순서가 비슷하다
- 클러스터링 팩터를 좋게만들려면 테이블을 Reorg해야함

컬럼 추가에 따른 클러스터링 팩터변화
- 기존 클러스터링 팩터가 좋아도 변별력이 좋지 않은 컬럼뒤에 변별력이 좋은 컬럼이 오면
정렬이 바뀌어 클러스터링 팩터가 나빠질 수 있다

Hash Join
- 2개 table hash순서 조정할 때는 leading ordered 가능하지만 3개 이상은 swap_join_inputs
- ordered나 leading힌트는 조인 순서를 결정 Build input순서는 아니다
해시조인은 조인컬럼의 인덱스를 사용하지 않기 때문에
서술형 문제 풀 때 야간배치 이렇게 나오면 Hash Join으로 풀릴 때 !
인덱스 쓸대없이 주지 않도록 주의

Sort Merge Join 인덱스 대체할 수 있는 대상은?
- 소트머지조인에서 인덱스를 이용해 소트연산을 대체할 수 있는 대상은 outer table만이다.

클러스터 테이블 Size옵션이 뭔지?
-클러스터키 하나당 레코드 개수가 많지 않을 때 클러스터마다 하나의 블록을 통째 할당 낭비
하나의 블록에 여러 키값 상주할 수 있도록 Size옵션

해시클러스터 테이블
- 해시 함수에서 반환된 값이 같은 데이터를 물리적으로 함께 저장하는 구조
해시함수가 인덱스 역할을 대신하고 나머지 비슷..
클러스터키로 데이터를 검색하고 저장할 위치를 찾을때는 해시함수 사용

인덱스 Fragmentation현상으로 인한 스캔효율 저하시
- 인덱스를 Coalesce, Shrink, Rebuild 한다
이렇게 되면 인덱스 구조가 슬림화되어 저장효율/스캔효율이 증가하지만
빈 공간이 없어 인덱스 분할이 자주발생해 DML성능이 떨어진다.
그렇기 때문에 아래와 같을 때 Rebuild해준다
1. 인덱스 분할에 의한 경합이 현저히 높을 때
2. 자주 사용되는 인덱스 스캔효율을 높이고자 할 때,특히 NL조인에서 반복되는 높이가 증가했을 시
3. 대량의 delete작업 이 후 다시 레코드가 입력되는데 오랜시간이 걸릴 경우
4. 총 레코드수가 일정한대도 인덱스가 계속 커질 경우

Prefetch/Batch
 - Outer테이블로부터 액세스 되는 Inner쪽 테이블 블록에 대한 디스크 I/O Call 횟수를 줄이기 위해
 - Prefetch( nlj_prefetch(d) ) - 곧이어 읽을 가능성이 큰 블록들을 캐시에 미리 적재해두는 기능 여러 single block io동시수행

 - Batch( nlj_batching(d) ) (정렬순서 달라질 수 있음) - 인덱스만으로 조인하고 나서 테이블과의 조인은 나중에 일괄처리
  / 부분범위처리 정상작동

Insert ALL구문
- 대용량 테이블인 거래당사자 테이블을 한번만 읽고 처리할 수 있음
원래는 두개의 Insert문으로 나뉘어져 있던 쿼리..!

선분이력 조회관련
-과거/현재/미래
:dt between c1.시작 and c1.종료
:dt between c2.시작 and c2.종료
-현재시점(미리 데이터입력)
c1.종료 = '99991231'
c2.종료 = '99991231'
-현재시점(미리 데이터입력 하지 않음)
to_char(sysdate, 'yyyymmdd') between c1.시작 and c1.종료
to_char(sysdate, 'yyyymmdd') between c2.시작 and c2.종료

선분이력 스캔효율 높이는 방법
1. 최근데이터 [종료일+시작일] 
2. 과거데이터 [시작일+종료일]
3. 업무적미래시점 데이터 안넣을 경우 to_char(sysdate, 'yyyymmdd') between 시작 and 종료일
4. 업무적미래시점 데이터 넣을 경우 종료일 = '99991231'
5. 인덱스구성이 [시작일+종료일] 일때 최근데이터 조회하면 index_desc사용

- 손익분기점 극복
1. 클러스터형 Index / IOT
2. SQL Server:Include index
3. 오라클 : 클러스터테이블
4. 파티셔닝

- 암기
인덱스 클러스터링 팩터를 좋게하려면?? 테이블을 재생성해야한다
인덱스 칼럼순서를 아무리 바꾸어도?? Table Randon Accescc 횟수는 줄지 않고 인덱스 스캔효율만 올라감!

-SQL Server에서 옵티마이저 힌트를 지정하는 방법
테이블힌트
조인힌트
쿼리힌트(조건절힌트X)

-인덱스 사용이 불가능한 경우
부정형인경우
WHERE EVENT_TYPE <> '20';
-- 튜닝 방안: WHERE EVENT_TYPE IN ('10', '30'); // EVENT_TYPE이 '10', '20', '30'인 경우
-- INDEX FULL SCAN은 가능
IS NOT NULL 조건
WHERE EVENT_SDATE IS NOT NULL;
-- INDEX FULL SCAN은 가능
-- 단일 컬럼 인덱스일 경우: 오라클 인덱스는 NULL값은 저장하지 않기 때문에 인덱스 전체 레코드가 모두 조건 만족
-- 결합 인덱스일 경우: 인덱스 구성 컬럼 중 하나라도 NULL값이 아닌 레코드는 저장, INDEX FULL SCAN 하면서 필터링
IS NULL 조건
WHERE EVENT_SDATE IS NULL;
-- 인덱스 사용 불가능
-- 단일 컬럼 인덱스일 경우: 저장이 안되어 있음
-- 결합 인덱스일 경우: 모든 인덱스 구성 컬럼이 NULL값인 경우는 저장이 안되어 있음
-- 다른 인덱스 구성 컬럼 중 어느 하나에 NOT NULL 제약이 걸려있다면 무조건 INDEX RANGE SCAN 가능
-- 다른 인덱스 구성 컬럼에 IS NULL 이외의 조건식이 있으면 INDEX RANGE SCAN 가능(둘 중 하나가 선두 컬럼일 때)
-- ex) WHERE EVENT_SDATE IS NULL AND EVENT_TYPE = '10';
-- 만약 EVENT_SDATE가 인덱스 선두 컬럼이라면 맨 뒤에 NULL값이 저장되는 곳부터 SCAN

뷰 안에 rownum, 분석함수를 사용하면
Non-mergeable View, Non-pushable View 가 된다 
값이 달라질 수도있기때문에!

문제풀이(묵시적 형변환이 발생하는 경우 조심하기) - 답 3,4

Index Skew
한쪽으로 치우친상태로 레코드가 모두 삭제된 블록은 언제든 재사용가능하지만,
문제는 다시 채워질 때까지 인덱스 스캔효율이 낮다는 데에 있다
SQL Server에선 주기적으로 b tree인덱스를 체크하므로써 지워진 레코드와 페이지를 정리해주는 메커니즘을 갖고있어
index skew현상이 발생하지 않는다.

Index Sparse
인덱스 전반에 걸쳐 밀도가 떨어지는 현상
지워진 자리에 인덱스 정렬순서에 따라 새로운값이 입력되면 그 공간은 재사용되지만
대량의 delete작업이 있고 난 후 한동안 인덱스 스캔 효율이 낮다는 데에 문제가 있다.
index skew처럼 블록이 아예 텅 비면 곧바로 freelist로 반한돼 언제든 재사용되지만
index sparse는 지워진 자리에 새로운 값이 입력되지 않으면 영영 재사용 되지 않을 수 있다.

클러스터인덱스 정의방법
- create cluster c_deptno# (deptno number(2)) index;
create index i_deptno# on cluster c_deptno#

오라클IOT와 SQL Server클러스터형인덱스 차이
- 오라클IOT는 PK에만 생성할 수 있는데 반해 SQL Server는 중복값이 있는 칼럼에도 생성가능 uniquifier라는 값을 함께 저장

오라클에서 IOT생성법
create table 영업실적 (사번 ..., constraint 영업실적_PK primary key(사번, 일자)) organization index;

Logical Rowid = PK + physical guess
IOT레코드의 위치는 영구적이지 않기 때문에 Oracle은 senondary 인덱스로부터 IOT레코드를
가리킬 때 물리적 주소대신 Logical Rowid를 사용한다. Logical Rowid는 PK와 physical guess로 구성

is null조회 시 
오라클 : 단일 X 복합일떄 not null조건에 해당하는게 있으면 O
SQL Server : 단일/복합 O

테이블 Random 액세스 최소화 튜닝
1. 인덱스 칼럼 추가
2. Corvered Index
3. Include Index
4. IOT,클러스터형인덱스, 클러스터 테이블
5. 수동으로 클러스터링 팩터 높이기

인덱스 스캔범위 최소화
1. 인덱스 선행칼럼이 범위조건일 때의 비효율
2. 범위조건을 IN-List로 전환
3. 범위조건을 2개 이상 사용할 때의 비효율

결합 인덱스 구성을 위한 기본 공식
1. 조건절에 항상 사용되거나, 적어도 자주 사용
2. '='
3. 소트오퍼레이션 생략하기 위해 칼럼 추가

SortMerge Join
-Oralce : '=', 부등호조인 가능
-SQL Server : '='만 가능

Hash Join
- 사용기준 2가지 : 한쪽 테이블이 가용 메모리에 담길 정도로 작아야함 / Build Input 해시 키 칼럼에 중복 값이 거의 없어야함

고급 조인 기법
1. 인라인뷰 활용
->


2.배타적 관계의 조인
1) 개통신청번호, 장애접수번호 두 칼럼을 따로 두고, 레코드별로 둘 중 하나의 칼럼에만 값을 입력한다
2) 

3. Rowid 활용


nvl(주문수량, 0) < 100 튜닝
- 주문수량이 인덱스를 타게 하려면 
주문수량 < 100 으로 바꾼다. 단, 주문수량이 not null 일때만!
만약 not null제약조건이 없으면 nvl을 없애지 못하고 풀스캔을 타겠지만
함수기반 인덱스를 만들면 인덱스를 탙 수도 있다 create index 주문idx on 주문(nvl(주문수량, 0));

묵시적형변환 인덱스 안타는 경우
1. 대상연월은 varchar타입, 파트너지원요청일자 varchar타입
and 대상연월 = substr(파트너지원요청일자,1,6) - 1
varchar에서 -1 숫자연산을 하게되면 숫자로 묵시적형변환이 일어나고
대상연월 또한 숫자로 형변환이 일어나
varchar인 대상연월이 숫자로 바뀌어 인덱스를 타지 못하는 상황이 발생
-> and 대상연월 = to_char(add_months(to_date(파트너지원요청일자, 'yyyymmdd'), -1), 'yyyymm')

2. max(decode(job, 'PRESIDENT', NULL, sal)) maxsal
-> decode문에서 세번째 인자가 네번째 인자에 데이터타입결정
즉, max(decode(job, 'PRESIDENT', to_number(NULL), sal)) maxsal   으로 형변환 해줘야함

Index Skip Scan에서 가장 좌측에 있는 리프블록과 마지막 블록은 항상 방문해야함
우리는 성별에 남,여 두개의 값만 존재한다는 사실을 알지만 옵티마이저는 모르기때문

Index Skip Scan작동조건(index_ss, no_index_ss)
1. 선두컬럼 조건절 누락됐고 후행컬럼의 distinct value가 많을 때 효과적
2. 선두컬럼은 있고 중간컬럼에 대한 조건절이 누락된 경우
3. distinct value가 적은 두 개의 선두컬럼이 모두 누락된 경우
4. 선두 컬럼이 부등호,between 범위검색 조건일 때도 index skip scan가능

Index Fast Full Scan활용
select * from 공급업체
where 업체명 like '%네트웍스%'
->
select /*+ ordered use_nl(b) no_merge(b) rowid(b) */ b.*
from ( select /*+ index_ffs(공급업체 공급업체 X01) */ rowid rid
from 공급업체
where instr (업체명, 네트웍스) > 0 ) a, 공급업체 b
where b.rowid = a.rid

Index Range Scan Descending 활용
= 

클러스터 인덱스
- 인덱스 클러스터는 키 값이 같은 데이터를 물리적으로 한 곳에 저장해 둘 뿐, IOT처럼 정렬하지는 않는다.
- 클러스터의 인덱스 키 값은 항상 유니크하며, 테이블 레코드와 1:M 관계를 갖는다. 이런 구조적 특성 때문에 클러스터 인덱스를 스캔하면서 값을 찾을 때는 Random액세스가 값 하나당 한번씩만 발생한다. Sequential방식으로 스캔
- 수정이 자주 발생하는 컬럼은 클러스터키로 선정하지 않는것이 좋지만, 전체데이터를 지우거나 테이블을 통쨰로 drop할 떄 성능 문제가 생길 수 있다 (truncate안됨, drop하려할때도 내부적으로 건건이 delete가 수행), 그래서 전체 데이터를 빠르게 지우고 싶을 떄는 클러스터를 truncate하거나 drop하는것이 가장 빠르다 물론 이때, 클러스터링된 테이블은 전부삭제된다.
- 클러스터 키 하나 당 레코드 개수가 많지 않을 떄 클러스터마다 한 블록씩 통째로 할당하는 것은 낭비
그래서 오라클은 하나의 블록에 여러 키 값이 같이 상주할 수 있도록 size옵션을 둠

pctfree/pctused
- pctfree : 데이터 블록에 저장된 Row Data가 변경 작업에 따라 행 크기가 증가할 상황에 대비한 여유공간 기본값으로 10%
- pctused :블록 재 사용 여부를 결정하는 요소로 데이터가 사용하는 공간이 설정 값 이하일 경우 해당 블록을 재사용할 수 있다

일반 인덱스와 비트맵인덱스
일반 인덱스의 rowid는 절대 중복값 안나오지만
인덱스의 키값은 중복이 나올 수 있다 pk인덱스가 아니라면
비트맵 인덱스도 rowid는 절대 중복값 안나오지만
비트맵 인덱스의 키값 또한 절대 중복값이 안나온다.

NL조인 비효율 찾기
- 성능고도화2 221p공부
- 인덱스 컬럼을 추가해야하는 경우 :
- 인덱스 컬럼 순서를 조정해야하는 경우 :
- 조인순서를 조정해야하는 경우 : 

-> 위와같은 작업을해도 별 소득이 없다면, 소트,해시 조인으로 변경!

Sort Merge Join
- 선행테이블만 인덱스 컬럼에 의한 sort 생략 가능
- 세컨드 테이블은 항상 정렬을 수행하므로 전체범위처리가 불가피 하지만 선행테이블만큼은 중간에 읽다가 멈출 수 있다
즉, 부분범위 처리가 가능하다.
- 충격적..  소트머지조인할 때 큰게 선행으로 오는게 효율적이다,.. 라는말이 이해가 안갔었는데
인덱스가 있는 큰 테이블이 선행으로 오면 부분범위처리가 가능하고 또한, sort까지 생략할 수 있으므로
선행으로 오는게 좋다는것...!!

Hash Join
-조인순서 조절법


Hash 조인 Build Input해시 키 값에 중복이 많을 때 발생하는 비효율

->

Hash 조인 사용기준
1. 한 쪽 테이블이 Hash Area에 담길정도로 충분히 작아야함
2. Build Input 해시 키 칼럼에 중복 값이 거의 없어야함
3. 조인 컬럼에 적당한 인덱스가 없어 NL 조인이 비효율적일 때
4. 조인컬럼에 인덱스가 있더라도 NL조인 드라이빙 집합에서 Inner 쪽 집합으로의 조인 액세스 량이 많아
Random 액세스 부하가 심할 때
5. 소트머지 조인하기에는 두 테이블이 너무 커 소트부하가 심할 때
6. 수행빈도가 낮고 쿼리 수행 시간이 오래걸리는 대용량 테이블을 조인할 때

Full Outer Join
- Left Outer 조인 + Union All + Anti조인(Not Exists필터)
- Union All을 이용한 Full Outer 조인(내가 자주 사용하는 방법)

수정가능조인뷰
조인뷰는 from절에 두 개 이상 테이블을 가진 뷰를 가리키며,
'수정가능 조인 뷰'는 말 그대로 입력,수정,삭제가 허용되는 조인 뷰를 말한다.
단, 1쪽 집합과 조인되는 M쪽 집합에만 입력,수정,삭제가 허용된다.

키 보존 테이블이란?
조인된 결과집합을 통해서도 중복 값 없이 Unique하게 식별이 가능한 테이블
->성능고도화 다시 공부

Merge Into ~ Using ~ On
merge into A a using B b on ( )
when matched then update
    set ...
    where ... (10g부터 가능)
    delete where ....(10g부터 가능)
when not matched then insert
    () ... values
    ()
    where ...


데이터 복제를 통한 소계 구하기
==롤업을 통한 소계

최종출력건에 대해서만 조인하기
보통 페이징처리 쿼리에서 마지막 페이징처리된건에 대해 조인을 걸어 처리

징검다리 테이블 조인을 이용한 튜닝
최종결과 건수는 얼마 되지 않으면서, 필터조건만으로 각 부분을 따로 읽으면
결과 건수가 아주 많을 때 튜닝하기 가장 어렵다.
-> 성능고도화 다시공부

-다음은 수정가능조인뷰와 키보존 테이블에 대한 설명이다. 틀린 것은? 3
1.DISTINCT처리가 포함된 경우에는 조인뷰의 수정이 불가능하다.
2.SUM,MIN,MAX,AVG,COUNT,STDDEV 등과 같은 그룹함수를 사용한 경우에는 수정이 불가능하다.
3.키보존 테이블의 연결되는 상대 테이블의 연결고리 컬럼이 반드시 UNIQUE일 필요는 없다.
4.UNION ALL, UNION, INTERSECT, MINUS등과 같은 집합처리를 한경우에는 수정이 불가능하다
[출처] 문