Tip
2011.01.28 17:38

partition table에 index 생성시

조회 수 18168 추천 수 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 84911
100 TM Oracle Silent Install Guide 3 file 송기성 2011.03.13 20252
99 TM 엔지니어필수품 file perfstat 2011.03.03 14117
98 TM RAC10g R2 patch(10.2.0.1->10.2.0.5) file 윤현 2011.03.02 10715
97 Tip windows os 에서 운영중 listener.log 삭제하기 2 file dbkill 2011.02.21 22267
» Tip partition table에 index 생성시 perfstat 2011.01.28 18168
95 TM oracle client 와 database 간 상호 호환성(version) 1 담벼락 2011.01.24 23203
94 Tip DB계정에 profile설정 perfstat 2011.01.05 14595
93 TM Automatic Storage Management file 담벼락 2010.12.31 10629
92 TM 10g Transportable Tablespace 에 대한 세미나 자료 입니다 file 송기성 2010.12.29 9204
91 TM Replication 세미나 자료 file 고구마 2010.12.29 11200
90 TM SystemState 세미나자료 file 고구마 2010.12.29 11024
89 TM Database Replay 담벼락 2010.12.27 9515
88 TM ORA-1578 조치를 위한 event 10231 1 이성철 2010.12.22 8456
87 TM (10g) 자동 통계정보 수집(AUTOMATIC OPTIMIZER STATISTICS COLLECTION) 담벼락 2010.12.16 16384
86 TM 오라클 디렉토리 변경 송기성 2010.12.16 25829
85 Tip em 실행시 에러 발생 조치 1 dbkill 2010.12.15 27311
84 Q&A RHEL5에 11gR2 RAC 구성시 ASM 관련 에러 file 송기성 2010.12.11 17733
83 Tip NetCA 실행시 VM 관련 에러 대처법 2 송기성 2010.12.09 18180
82 TM UPDATABLE SNAPSHOT 구축하기 고구마 2010.08.23 15507
81 TM replication table 다른 tablespace 로 옮기기 고구마 2010.08.23 13822
Board Pagination Prev 1 2 3 4 5 6 7 8 Next
/ 8