오라클 성능 고도화 원리와 해법 II (2016년)
인덱스 파티셔닝 0 0 3,569

by 구루비 파티셔닝 파티션인덱스 [2017.06.07]


h1.3.인덱스 파티셔닝

h3.(1) 인텍스 파티선 유형

  • 비파티션 테이블(Non-Partitioned Table)
  • 파티션 테이블(Partitioned Table)
    • 비파티션 인텍스(Non-Partitioned Index)
    • 글로벌 파티션 인텍스(Global Partitioned Index)
    • 로컬 파티션 인텍스(Local Partitioned Index)
  • 인텍스를 글로벌과 로컬로 먼저 나누고, 글로벌을 다시 파티션과 비파티션으로 나누는분류 방식도 있다
    (이때는 비파티션 인덱스를 '글로벌 비파티션 인덱스' 라 부른다)

  • 비파티션 테이블은 비파티션 인텍스와 글로벌 파티션 인덱스를 가질 수 있다.(???)
  • 파티션 테이블은 비파티션 인텍스 글로벌 파티션 인텍스 로컬 파티션 인텍스를 가질 수 있다.
  • 비파티션 테이블에 대한 비트맵 인덱스는 파티셔닝이 허용되지 않고, 파티션 테이블에 대한 비트맵 인덱스는 로컬 파티셔닝만 허용된다.

h3.(2) 로컬 파티션 인텍스

  • 각 파티션 마다 인덱스를 가진다. ( 테이블 파티션과 1:1 대응 관계)

h3.(3) 비파티션 인텍스

  • 파티셔닝하지 않은 인덱스

h3.(4) 글로벌 파티션 인텍스

  • 테이블 파티션과 독립적인 구성을 갖도록 파티셔닝
  • 파티션을 묶어서 인덱스를 생성한다.

9i부터 아래와 같이 update global indexes 옵션을 주변 파티션 DDL 작업에 의해 영향받는 인텍스 레코드를 자동으로 갱신해 주므로 인텍스가 unusable 상태로 빠지지 않는다.

alter table .. .
split partition ...
update global indexes ;

주의!
파티션 DDL로 인해 영향 받는 레코드가 전체의 5% 미만일 때만 유용하다.
5% 이상일 때는 인텍스를 재생성하는 것보다 오히려 늦다는 뜻(평균적으로)

h5.글로벌 해시 파티션 인텍스

  • 글로벌 파티션 인덱스의 경우, 9i까지는 글로별 Range 파티션만 가능했지만 lOg부터는 글로벌해시 파티션도 가능
  • 테이블과 독립적으로 인텍스만 해시 키 값에 따라 파티셔닝 할수 있다.
  • 글로벌 해시 파티션 인텍스는 Right Growing 인텍스 처럼 Hot 블록이 발생하는 인덱스의 경합을 분산할 목적으로 주로 사용
  • 글로벌 결힘(Composite) 인텍스 파티셔닝은 불가

h3.(5) Prefixed vs. Nonprefixed

  • Prefixed
    파티션 인텍스를 생성할 때 파티션 키 컬럼을 인텍스 키 컬럼 왼쪽 선두에 두는것
  • Nonprefixed
    파티션 인텍스를 생성할 때, 파티션 키 컬럼을 인텍스 키 컬럼 왼쪽 선두에 두지 않는 것
PrefixedNonprefixed
로컬 파티션 인덱스12
글로벌 파티션 인덱스34(Not Support)

h5.4가지 유형

  • 비파티션인텍스
  • 글로벌 Prefixed 파티션 인텍스
  • 로컬 Prefixed 파티션 인텍스
  • 로컬 Nonprefixed 파티션 인텍스

h3.(6) 파티션 인덱스 구성 예시
h5.파티션 인텍스유형별 구성 예시

h3.(7) 글로벌 파티션 인덱스의 효용성

  • 글로벌 파티션 인텍스는 경합을 분산시키려고 글로벌 해시 파티셔닝하는 경우 외에는 거의 사용하지 않는다.
  • 글로벌 파티션 인덱스보다는 로컬 파티션 인텍스와 비파티션 인텍스가 주로 사용

h5.테이블과 같은 컬럼으로 파티셔닝하는 경우
▶조건

  • 테이블은 날짜 컬럼 기준으로 월별 파티셔닝
  • 인덱스는 분기별 파티셔닝히는 경우
    ▶결과
  • 글로벌 파티션 인텍스에는 Prefixed 파티션만 허용되므로 날짜 컬럼을 선두에 위치해야함
  • 날짜 조건은 주로 범위 검색 조건인데 인덱스 스캔 효율면에서 불리
  • '=' 조건 컬럼을 인텍스 선두에 둘 수 있다는 측면에선 로컬 Nonprefixed 파티션 인텍스가 훨씬유리
  • 두달이상 넓은 범위 조건은 로컬인덱스에서도 비효율 발생 이럴땐 비파티션 인덱스가 좋은 선택

h5.테이블과 다른 컬럼으로 파티셔닝하는 경우
▶조건

  • 컬럼으로 글로벌 파티션 생성
    ▶결과
  • 인덱스 경합을 분산하는 경우가 아니면 로컬 인덱스가 무조건 유리

h5.(8)로컬 Nonprefixed 파티션 인덱스의 효용성

  • 이력성 테이블은 거의 대부분 날짜 컬럼을 파티션 키로 사용


select sum( 거래량), sum( 거래금액)
 from 일별계좌별거래
where 계좌번호 = :acnt_no
  and 거래일자 between :dl and :d2


▶ 계좌번화가 인덱스 선두에 없으므로 효율이 저하됨

예)

  • prefix_index
  • nonprefix_index

h5.글로벌 Prefixed 파티션 인텍스와 비교

  • 글로벌 파티션 인텍스는 Prefixed 파티션만 허용
  • 로컬 Prefixed 파티션과 마찬가지로 인텍스 스캔 효율이 나쁘다

h5.비파티션 인텍스와비교

  • 계좌번호를 선두에 둔 비파티션 인텍스는 여러 달에 걸친 거래일자로 조회하더라도 인텍스 스캔상 비효율은 없다.
  • 아주 넓은 범위(예를 들어 10년체의 거래일자로 조회하거나 계좌번호만으로 조회할 때는 테이블 Random 액세스 부하

h3.(9) 액세스 효율을 고려한 인텍스 파티셔닝 선택 기준
h5.날짜 컬럼 기준 파티셔닝

  • 관리적 측면뿐만 아니라 병렬 쿼리 활용 측면에서도 로컬 파티션 인텍스가 좋은 선택
    (비파티션 인텍스는 index fast full scan 이 아닌 한 병렬 쿼리 에 활용할 수 없다)
  • 로컬 인텍스 중에서는 Nonprefixed 파티션 인텍스가 성능 변에서 유리할 때가 많다.

h5.OLTP성 애플리케이션 환경

  • OLTP성 애플라케이션 환경에서는 비파티션 인텍스가 좋은선택
  • Right Growing 인텍스에 대한 동시 Insert 경합을 분산할 목적으로 해시 파티셔닝하는 경우가 아니라면 글로벌 파티션 인텍스는효용성이 낮다.
  • OLTP성이더라도 테이블이 파티셔닝돼 있다변 특히 로컬 파티션 인텍스는 테이블 파티션에 대한 DDL 작업 후 인텍스를 재생성하지 않아도 되므로 가용성 측면에서유리
  • 로컬 인텍스 중 Prefixed 파티션이 Nonprefixed 파티션보다 유리하다고 오라클 매뉴얼을 포함한 여러 문서에 설명(저자는 동의 X )

h3.(10) 인텍스 파티셔닝 제ξ떨 고려한 데이터베이스 설계

  • Unique 파티션 인텍스를 정의할 때는 인텍스 파티션 키가 모두 인텍스 구성 컬 럼에 포함돼야 한다.
  • 글로별 파티션 인텍스는 Prefixed 파티션이어야 한다
"구루비 주주클럽 스터디모임" 에서 2016년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3380

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입