Tip
2011.01.28 17:38

partition table에 index 생성시

조회 수 18169 추천 수 0 댓글 0

partition table에 index생성시 4가지 방법이 있습니다.

 

1.Local prefixed partitoned index

2.Local non-prefixed partitoned index

3.Global prefixed non-partitoned index

4.Global prefixed partitoned index

 

1.Local Prefixed Index

Index Column의 구성에서 선두 Column이 Partition Key Column인 Index이다.

 

이 Partition Key는 Local Index가 모 Table과 Equipartitioning되어 있으므로Table의 Partition Key와 동일하다. 즉 각 Table Partition에 Row가 Partition Key 값에 따라 분산되어 있고, Index는 각 Partition에 분리된 값의 기준(Partitioning Key)에 따라 정렬되어 Index가 구성된다.

위의 Index 생성 Script에서 PREFIXED를 지정하지 않았지만 Dictionary정보를 검색해 보면 DBMS가 Index의 선두 Column을 조사하여 PREFIXED로 지정함을 알 수 있다. 또한 위의 Index 생성 Script에서는 Index가 몇 개의 Partition으로 나누어 지는지 지정하지 않았다.

 

그러나 이부분 역시 "Local" Option으로 인해서 DBMS가 동일한 수의 Partition으로 나누었고 각 Index의 Partitioning Bound역시 Table의 Partitioning Bound와 동일하게 설정하였음을 Dictionary를 조사해 보면 알 수 있다. 즉 스스로 Equipartitioning 된 것이다.

 

USER_PART_INDEXES, USER_IND_PARTITIONS, USER_TAB_PARTITIONS를 조회하면 위의 내용을 확인할 수 있다.

 

2.Local Non-prefixed Index

Index의 선두Column이 Partition Key Column으로 시작하지 않는 Local Index이다.

 

Partition Key Column이 Index에 중간에 올 수도 있지만 항상 선두에 오지 않는 한 Nonprefixed index이다. 즉 Local Prefixed Index와 다른 점은 두 Index Type 모두 Partition Key에 따라 동일한 범위에 값을 가지고 있지만 Local Prefixed Index는 Partition Key를 기준으로 정렬되어 있는 반면, Nonprefixed Index는 정렬기준이 Partition Key가 아니라는 점이다.

 

Local Nonprefixed Index는 Unique Index가 되려면 Index column에 Partition Key Column을 포함하고 있어야 하는 제약을 안고 있다.

 

이러한 제약으로 인해 Partition Key가 포함되지 않은 Column들로 Unique Index를 만들고자 할 경우는 Global Index로 만들어야 한다.

 

Local Nonprefixed Index는 Table과 Equipartitioning 되어 있어 가용성, 관리의 용이성을 제공하면서도 Partition Key 이외의 Column에 의한 조건 검색시 Partition별 처리를 할 수 있으므로 Historical Table에서 유용하게 사용되어 질 수 있다.

 

Local Nonprefixed Index는 Index의 선두 Column이 Table Partition Key와 다를 뿐이며 생성하는 것은 Local Prefixed Index와 동일하다

 

3. Global Prefixed Partitioned Index

 

Global index는 local index에 비하여 생성시, 전체적인 활용 측면 및 영향을 충분히 고려하여 설계하고 생성해야 한다. 모 Table의 특정 Partition이 Drop된 경우 Global Index의 특정 Partition에만 영향이 있다고 간주 할 수 없기 때문에 모든 Global Index에 영향을 미친다.

 

Partition table의 경우 일정기간동안 데이타를 On-line으로 유지하고 그 기간이 지난 데이타는 백업받은 후 해당 Partition을 Drop하게 되는데 Global Index는 Partitioned Table에 대해서 Partition별 독립성이 없기 때문에 이 경우에는 전체 Index에 대해 수정 사항이 반영되도록 Rebuild작업을 해주어야 한다.

 

Partitioned Table에서는 대부분의 경우 Local Index가 성능이나, 가용성 및 관리의 용이성이 Global Index 보다 우수하다. 그러나 업무의 특성 및 기타 Access 의 형태에 따라 Partition Index를 만들고자 하나 Local Index를 생성할 수 없는 경우에 Global Index를 고려한다.

 

가령 Partition Key를 포함하지 않으면서 몇 개의 Column이 결합된 Unique Index를 만들어야 할 때, 또는 Index 선두Column을 구성하는 Column이 업무적으로 또는 Access 형태별로 분리가 확실하여 Partition 되는 Index가 너무 많지 않게 생성할 수 있을 경우에 활용 할 수 있다.

 

Global Prefixed Partitioned Indexes는 Global 인덱스가 Partitioned 되어 있는 경우이며 관리측면을 고려하여 관리가 용이한 레벨에서 파티션을 고려하면 된다. 그리고 파티션 단위는 테이블의 파티션 조각들과 1:1 맵핑될 필요 없이 자유롭게 정하되 너무 많은 파티션으로 구성하면 넓은 범위 Index Range Scan시 인덱스 Jumping 현상이 생길 수 있으므로 주의하여야한다.

 

만일 Global인덱스를 Local Indexes와 같이 파티션 조각들과 일치하는 Equi-Global Partitioned Indexed를 생성한 경우는 Oracle은 이를 이용하여 실행계획을 세우거나, DDL문이 실행된 경우 Local Index와 같은 Partitioning의 이점을 활용하지 못하므로 유의하여야 한다.

 

 

4. Global Prefixed Non-Partitioned Index

인덱스가 Partitioned 되어 있지 않은 형태로 일반적인 형태이다. Primary Key를 생성하게 되면 기본적으로 이 형태로 만들어진다.

 

CREATE INDEX global_partitioned ON t_part (prod_id) GLOBAL;

 

5. Local Index로 PK 구현 방안

 

Partition table에 index를 생성할 때 Default로 생성되는 index type은 Golbal index이기 때문에 Pk생성시 생성되는 Pk unique Index도 Global index로 만들어진다.

 

Local unique index를 먼저 만든 후 이를 이용하여 Pk Constraint를 생성하면 Pk index는 이미 만들어져 있는 Unique index를 이용하게 된다.

 

유의할 점은 Local unique index명과 Pk Constraint명을 동일하게 주어야 하며 Local index가 Unique하기 위해서는 반드시 Partition key를 포함하여야 하듯이 이 경우도 Pk컬럼에 Partition key가 포함될 경우에 사용할 수 있다.

 

이렇게 함으로서 임의의 Partition이 Drop되더라도 Pk index와 Constraint를 유지할 수 있어 관리적인 측면에서 Local index의 장점을 살릴 수 있다

 

요약 -------

local 인덱스생성시 P.K값에 partition key을 넣어야 합니다.

global 은 상관없음..


List of Articles
번호 분류 제목 글쓴이 날짜 조회 수
공지 Q&A Oracle관련 게시물만 Sean 2014.04.09 84920
60 Tip Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1] 담벼락 2011.03.23 17574
59 Tip NL JOIN에 대하여... 1 고구마 2011.03.21 13176
58 Tip Outer Join의 정확한 이해 file 고구마 2011.03.21 13028
57 Tip 쿼리 튜닝 사례 고구마 2011.03.21 14449
56 Tip windows os 에서 운영중 listener.log 삭제하기 2 file dbkill 2011.02.21 22296
» Tip partition table에 index 생성시 perfstat 2011.01.28 18169
54 Tip DB계정에 profile설정 perfstat 2011.01.05 14762
53 Tip em 실행시 에러 발생 조치 1 dbkill 2010.12.15 27311
52 Tip NetCA 실행시 VM 관련 에러 대처법 2 송기성 2010.12.09 18207
51 Tip Lock Check 승현짱 2010.05.30 11795
50 Tip 11g Newfeature 승현짱 2010.05.30 11477
49 Tip EXPORT/IMPORT 이용한 TABLE과 INDEX 분리하는 방법 고구마 2010.05.24 25472
48 Tip TABLE(INDEX) 다른 TABLESPACE로 옮기기(8i) 고구마 2010.05.24 23009
47 Tip reoder column order 1 유주환 2010.05.19 11394
46 Tip RECOVERY 2 고구마 2010.05.19 19916
45 Tip TEMP TABLESPACE 사용현황 조회하기 1 고구마 2010.05.19 44234
44 Tip 통계복원하기 고구마 2013.05.13 8055
43 Tip 유저별 Segment 개수 및 사이즈 조회 쿼리, n2bfree 2013.03.12 11077
42 Tip online reorg script 담벼락 2013.01.10 10630
41 Tip 오라클11g에서 SE1,SE,EE제품의 옵션비교입니다. file perfstat 2012.03.07 27016
Board Pagination Prev 1 2 3 4 5 6 7 8 Next
/ 8