DB2 INDEX 설계
DB2 |
2007. 2. 2. 18:12
DB2 index 설계 기법
1. DB2의 Access Path 방법과 Index의 성질을 이해한 후에 적절한 Index를 선택하는 순서를 이해한다.
2. 유효한 Index 와 그렇지 않은 Index를 판단할 수 있다.
3. Index의 물리설계를 이해한다.
▣ Index 목적과 성질
◈ Index의 목적
1. 행의 Unique 성을 보증한다.
2. 추출하기의(예, 조회) Access Performance를 향상한다.
◈ Index의 성질
1. Unique 한가
. Unique Index
. Non Unique Index(Duplicate/Nonunique Index)
2. Cluster화 하는가
. Cluster화 Index(Clustering Index)
. 비 Cluster화 Index(Nonclustering Index)
3. Cluster성의 정도는 어느 정도인가
. Clustered Index(Clustered Index)
. 비 Clustrered Index(Nonclustered Index)
4. 복수의 열로부터 되는가
. 단일열 index
. 복합열 Index(Compound Index)
※ 참고
◐ Clustering Index
CREATE INDEX IXI ON TI (...) CLUSTER
☞ 행의 삽입시에 그 Index Key에 기초하여 삽입되어지고 1 Table 당 1 개
예 : create index ix1 on ta(c1)
create index ix2 on ta(c2) cluster
☞ LOAD, REORG(LOAD)의 때에 행의 Load순은 File의 순번대로
☞ Clustering, 동시에 Clustered로 할 때는 Load전에 미리 File을 그 Key 순으로 Sort해 놓는다.
☞ CLUSTER 지정 Index가 없을 때에는, 최초로 만들어진 Index를 Cluster화 Index로 간주한다.
☞ REORG(UNLOAD)가 Cluster화 Index를 사용하여 Unload하는 것은
- 1 Table / TableSpace인 때
- Segmented TableSpace인 때
◐ Clustered Index
☞ 행이 그 index 순번으로 물리적으로 거의 나열해 있는 경우
☞ Data page 내에는 Key순으로 꼭 나열해 있을 필요는 없고, 가까운 key를 갖고 있는 다른 행과 같은 page에 뭉쳐 있으면 좋다.
☞ 다음의 Key를 갖는 행이 같은 page에 있든가, 이웃한 Page에 있는 비율이 95% 를 넘을 때, 그 Index는 Clustered Index로 간주되어진다.
☞ 1 개의 Table에 복수의 Cluster Index가 존재할 수 있다.
◐ Index의 성질은 DB2 Catalog에 기록되어져 있다.
SYSIBM.SYSINDEXES
NAME Index
COLCOUNT 1 단일열 Index
n 복합열 Index
UNIQUEQULE U Unique Index
D Non Unique Index
CLUSTERED Y Clusered Index
N Non Clustered Index
CLUSTERING Y Cluster화 Index
N 비 Cluster화 Index
CLUSTERRATIO n Cluster 율
위의 정보들은 Version에 따라서 다를 수가 있으며, 특히 중요한 의미를 갖는 것은 다음의 세가지이다.
◐ Unique Index인가, Non Unique Index 인가
◐ Clustered Index 인가, Non Clustered Index인가, Cluster 율
◐ 다일열 Index 인가, 복합열 Index 인가
▣ Index의 설계 순서
◈ 개요
1. Index의 필요성의 검토
2. 기본적인 Index후보의 선택
Program 설계와 SQL설계를 한 뒤에 행한다.
3. 추가 Index 후보의 검토
4. Clustered Index의 검토
5. Index 후보의 취사 선택과 결정
6. 중요한 조회에 대한 추가의 Index를 검토
(1) Index만의 Access
(2) 복합열 Index
7. Index의 물리설계
8. Index의 유효성과 검증
EXPLAIN
※ 3번 ~ 8번 을 반복 수행한다.
◈ 세부내용
1. Index의 필요성과 검토
(1) Unique성이 필요한 Table에는 꼭 Index를 만든다.
(2) 삽입/삭제/Index열의 갱신이 있는 작은 Table이나 빈 Table에는 index를 붙이지 않는다.
☞ Lock 에 의한 경합
☞ Index의 갱신 부하
(3) Join을 필요로 하는 컬럼에는 반드시 Index를 만든다.
(4) Foreign Key에는 반드시 Index를 만든다.
☞ Lock 에 의한 경합
2. 기본적인 Index의 선택
(1) Primary Key
☞ Unique 인 동시에 NOT NULL
(2) Foreign Key
☞ 경합열이 된다
(3) Primary Key외에 Unique성이 필요한 열
(4) 조회 성능을 높이기 위해 필요한 열
3. 추가 Index후보의 검사
(1) 상기 이외로 빈번하게 조회(갱신, 삭제를 포함한다)의 술어로 사용되어지는 열을 조사한다.
(가) 예를들면, Transaction 종류의 20% 혹은 10 ~ 20 개의 조회, 갱신 삭제에 사용되어지는 술어를 나타낸다.
(나) 상기의 Index의 선두행에 전혀 ㅍ함되지 않은 열이 단독으로 사용되어지고 있는 경우도 Index 의 후보로 한다.
(다) 상기의 Index가 등호술어에 전부 Match 하는가, 일부에 Match 하는가, 일부에 Match 하는가 어떤가를 검토한다.
※ c1 = and c2 =
☞ Index(c1,c2)가 있으면 완전하게 Match하고 있으므로 이 조회에 대해서는 추가 Index가 필요 없다.
☞ Index(c1), Index(c2) 밖에 없을 때에는 복합열 Index(c1,c2)를 추가의 Index 후보로 한다.
(라) Index 칼럼의 순서는 COLCARD가 큰 쪽을 앞으로 한다.
(2) 빈번하게 ORDER BY, GROUP BY 의 대상이 되는 열
(3) 상기의 2 이외로 Join에 사용되는 열이 있으면 Index의 후보로 한다.
4. Cluster화 Index(Clustering)의 검토
(1) 어느 Index에 Cluster 속성을 주는가를 결정한다.
(가) 가장 사용 빈도가 높고, 동시에 범위로 검색할 수 있는 것이 많은 Index (Unique할 필요는 없다)
(나) 그 Key 순으로 조회할 수 있는 것이 많은 Index
(다) 입력 Data의 발생순서에 가장 가까운 Index
(라) Foreign Key로 Join되어지는 일이 많은 경우에는 Foreign Key의 Index
(2) 선택한 Index가 Clustered Index로도 된다고 가정한다.
☞ Primary Key를 꼭 Clustering Index로 할 필요는 없다.
5. Index 후보의 취사 선택과 결정
(1) Index의 수는 5 개 이하를 표준으로 한다.
(가) 검색만의 Table 에는 몇 개의 Index를 붙여도 좋다.
☞ Load시의 Index 작성 또는 Create Indext시에 많은 시간 소요
☞ Index를 위해 DASD 용량을 염두에 둘 것
(나) 갱신이라도 Index의 열을 갱신하지 않는 경우는 검색과 같다.
(다) Index의 열을 갱신/삽입/삭제를 하는 경우는, Index의 수를 적게 한다.
☞ 타당한 Index의 수는 Processor 능력과 응답시간 요구의 정도에 의존한다.
☞ ANDB2의 Cost Model로 부하를 추정하는 것이 바람직하다.
(2) COLCARD 가 작은 열의 Index는 피한다.
(가) 중복이 대단히 많은 경우, INSRT/DELETE의 부하가 크다.
(나) 다음의 부등식을 만족시키지 않는 Non Clustered Index는 제외
(3) 일반적으로 40Byte 이상의 Key의 Index는 피한다.
(4) 일반적으로 VARCHAR, VARGRAPHIC과 같은 가변길이 열의 Index는 피한다.
6. 중요한 조회에 대개 개별적으로 추가의 Index를 검토한다.
(1) Index만의 Access로 끝나는 Index를 검토한다.
(2) 단일열/복합열 Index의 추가로 Performance의 향상이 보여 지는가 어떤가를 검토한다.
☞ 모든 Index의 열이 등호 조건으로 사용되어지는 것이 유효하다
(3) 복합 Index의 유효성을 평가한다.
☞ FIRSTKEYCARD가 작은 Index는 피한다.
☞ 복합열 Index가 다른 Index Key를 포함하는 경우에는 짧은 Key의 Index를 우선한다.
※ Index(c1,c2) 와 Index(c1)중에서 Index(c1)을 우선한다.
7. Index의 물리설계를 행한다.
CREATE INDEX 의 Parameter를 결정한다.
(1) 열의 순번
(2) 오름순인가 내림순인가
(3) PCTFREE
☞ Random한 삽입이 있는 경우에 지정한다.
(4) SUBPAGES
☞ 삽입이 있다. -----------> 기본적으로 1 지정
☞ 행의 갱신만 있다. -----------> 1 이상 지정
(5) 값이 단조 증가하는 열을 선두에 갖는 Index는 DASD 용량에 주의힐 필요
예 : 날짜, 채번에 의해 발생한 수주번호와 출하번호
이유 :
① 오래된 Key를 삭제해도, 그 Key 범위가 아니면 Index Page는 재 사용되어지지 않는다.
② 최후의 Index Page 가 꽉차게 되는 때에 Split 이 생기고 Index Page의 반밖에 유효하게 사용되어지지 않는다
8. Index의 유효성을 검증한다.
(1) Data 가 존재하고 DB2가 가동하고 있는 때
☞ EXPLAIN
(2) Data 가 존재하지 않지만 DB2가 가동하고 있는 때
☞ EXPLAIN
(3) DB2가 가동하고 있지 않은 때
☞ ANDB2 APS
▣ FULLKEY CARD(COLCARD)가 작은 경우의 고려사항
◈ FULLKEYCARD가 작은 Non Cluster Index는 일단 사용하지 않는다
예 : Index(성별 [남,녀])
Index(대분류Code [1,2,3])
1. Optimezer가 Access Path 로써 선택하지 않는 것 뿐만 아니라 다른 Access Path가 Performance가 좋기 때문이다.
◈ 할 수 없는 경우의 대응책
Optimezer에게 Non Cluster 이지만 COLCARD 와 FULLKEY CARD가 크다고 인식 시킨다.
◐ 순서
1. 여러 Key 값을 갖는 Dummy 행을 INSERT 시킨다.
2. RUNSTATS Utility 를 실행 시킨다.
3. Dummy 행을 모두 DELETE 한다.
4. BIND 한다.
◐ 고려사항
1. 희망하는 Access Path 가 얻어진 뒤에는 RUNSTATS 와 BIND를 하지 않는다.
2. RUNSTATS Utility 실행의 필요가 생겨난 경우는(재편성해야 옳은가 어떤가를 알기 위해), 이 Index를 RUNSTATS의 대상으로부터 떼내고 상기의 Step을 반복한다.
▣ 정리
◈ 언제 CREATE INDEX 하는가
1. LOAD 전
2. 빈번하게 Access하는 열이 나중에 발견되었을 때
3. 결합열(Join)이 새롭게 발견되었을 때
4. ORDER BY, GROUP BY의 Performance를 개선하고 싶을 때
◈ 언제 DROP INDEX 하는가
1. EXPLAIN에 그 Index가 사용되어지지 않는 것이 판명되었을 때
2. SYSPLANDEP로부터 어떤 Plan도 그 Index를 사용하고 있지 않음을 알았을 때
◈ 언제 RUNSTATS를 실행하는가
1. LOAD 후
2. Reorganization 후
3. 삽입, 삭제, 가변길이의 갱신이 많이 발생한 때
4. CREATE INDEX 후
5. DROP TABLE 한 때
※ 주의사항
☞ Tablespace 와 Index 양쪽을 동시에 조사할 것
☞ RUNSTATS를 실행하여도 BIND하지 않으면 Access Path는 바뀌지 않는다.
☞ Access Path를 바꾸고 싶지 않을 때 처리하지 않는 것도 생각되어 진다.
1. DB2의 Access Path 방법과 Index의 성질을 이해한 후에 적절한 Index를 선택하는 순서를 이해한다.
2. 유효한 Index 와 그렇지 않은 Index를 판단할 수 있다.
3. Index의 물리설계를 이해한다.
▣ Index 목적과 성질
◈ Index의 목적
1. 행의 Unique 성을 보증한다.
2. 추출하기의(예, 조회) Access Performance를 향상한다.
◈ Index의 성질
1. Unique 한가
. Unique Index
. Non Unique Index(Duplicate/Nonunique Index)
2. Cluster화 하는가
. Cluster화 Index(Clustering Index)
. 비 Cluster화 Index(Nonclustering Index)
3. Cluster성의 정도는 어느 정도인가
. Clustered Index(Clustered Index)
. 비 Clustrered Index(Nonclustered Index)
4. 복수의 열로부터 되는가
. 단일열 index
. 복합열 Index(Compound Index)
※ 참고
◐ Clustering Index
CREATE INDEX IXI ON TI (...) CLUSTER
☞ 행의 삽입시에 그 Index Key에 기초하여 삽입되어지고 1 Table 당 1 개
예 : create index ix1 on ta(c1)
create index ix2 on ta(c2) cluster
☞ LOAD, REORG(LOAD)의 때에 행의 Load순은 File의 순번대로
☞ Clustering, 동시에 Clustered로 할 때는 Load전에 미리 File을 그 Key 순으로 Sort해 놓는다.
☞ CLUSTER 지정 Index가 없을 때에는, 최초로 만들어진 Index를 Cluster화 Index로 간주한다.
☞ REORG(UNLOAD)가 Cluster화 Index를 사용하여 Unload하는 것은
- 1 Table / TableSpace인 때
- Segmented TableSpace인 때
◐ Clustered Index
☞ 행이 그 index 순번으로 물리적으로 거의 나열해 있는 경우
☞ Data page 내에는 Key순으로 꼭 나열해 있을 필요는 없고, 가까운 key를 갖고 있는 다른 행과 같은 page에 뭉쳐 있으면 좋다.
☞ 다음의 Key를 갖는 행이 같은 page에 있든가, 이웃한 Page에 있는 비율이 95% 를 넘을 때, 그 Index는 Clustered Index로 간주되어진다.
☞ 1 개의 Table에 복수의 Cluster Index가 존재할 수 있다.
◐ Index의 성질은 DB2 Catalog에 기록되어져 있다.
SYSIBM.SYSINDEXES
NAME Index
COLCOUNT 1 단일열 Index
n 복합열 Index
UNIQUEQULE U Unique Index
D Non Unique Index
CLUSTERED Y Clusered Index
N Non Clustered Index
CLUSTERING Y Cluster화 Index
N 비 Cluster화 Index
CLUSTERRATIO n Cluster 율
위의 정보들은 Version에 따라서 다를 수가 있으며, 특히 중요한 의미를 갖는 것은 다음의 세가지이다.
◐ Unique Index인가, Non Unique Index 인가
◐ Clustered Index 인가, Non Clustered Index인가, Cluster 율
◐ 다일열 Index 인가, 복합열 Index 인가
▣ Index의 설계 순서
◈ 개요
1. Index의 필요성의 검토
2. 기본적인 Index후보의 선택
Program 설계와 SQL설계를 한 뒤에 행한다.
3. 추가 Index 후보의 검토
4. Clustered Index의 검토
5. Index 후보의 취사 선택과 결정
6. 중요한 조회에 대한 추가의 Index를 검토
(1) Index만의 Access
(2) 복합열 Index
7. Index의 물리설계
8. Index의 유효성과 검증
EXPLAIN
※ 3번 ~ 8번 을 반복 수행한다.
◈ 세부내용
1. Index의 필요성과 검토
(1) Unique성이 필요한 Table에는 꼭 Index를 만든다.
(2) 삽입/삭제/Index열의 갱신이 있는 작은 Table이나 빈 Table에는 index를 붙이지 않는다.
☞ Lock 에 의한 경합
☞ Index의 갱신 부하
(3) Join을 필요로 하는 컬럼에는 반드시 Index를 만든다.
(4) Foreign Key에는 반드시 Index를 만든다.
☞ Lock 에 의한 경합
2. 기본적인 Index의 선택
(1) Primary Key
☞ Unique 인 동시에 NOT NULL
(2) Foreign Key
☞ 경합열이 된다
(3) Primary Key외에 Unique성이 필요한 열
(4) 조회 성능을 높이기 위해 필요한 열
3. 추가 Index후보의 검사
(1) 상기 이외로 빈번하게 조회(갱신, 삭제를 포함한다)의 술어로 사용되어지는 열을 조사한다.
(가) 예를들면, Transaction 종류의 20% 혹은 10 ~ 20 개의 조회, 갱신 삭제에 사용되어지는 술어를 나타낸다.
(나) 상기의 Index의 선두행에 전혀 ㅍ함되지 않은 열이 단독으로 사용되어지고 있는 경우도 Index 의 후보로 한다.
(다) 상기의 Index가 등호술어에 전부 Match 하는가, 일부에 Match 하는가, 일부에 Match 하는가 어떤가를 검토한다.
※ c1 = and c2 =
☞ Index(c1,c2)가 있으면 완전하게 Match하고 있으므로 이 조회에 대해서는 추가 Index가 필요 없다.
☞ Index(c1), Index(c2) 밖에 없을 때에는 복합열 Index(c1,c2)를 추가의 Index 후보로 한다.
(라) Index 칼럼의 순서는 COLCARD가 큰 쪽을 앞으로 한다.
(2) 빈번하게 ORDER BY, GROUP BY 의 대상이 되는 열
(3) 상기의 2 이외로 Join에 사용되는 열이 있으면 Index의 후보로 한다.
4. Cluster화 Index(Clustering)의 검토
(1) 어느 Index에 Cluster 속성을 주는가를 결정한다.
(가) 가장 사용 빈도가 높고, 동시에 범위로 검색할 수 있는 것이 많은 Index (Unique할 필요는 없다)
(나) 그 Key 순으로 조회할 수 있는 것이 많은 Index
(다) 입력 Data의 발생순서에 가장 가까운 Index
(라) Foreign Key로 Join되어지는 일이 많은 경우에는 Foreign Key의 Index
(2) 선택한 Index가 Clustered Index로도 된다고 가정한다.
☞ Primary Key를 꼭 Clustering Index로 할 필요는 없다.
5. Index 후보의 취사 선택과 결정
(1) Index의 수는 5 개 이하를 표준으로 한다.
(가) 검색만의 Table 에는 몇 개의 Index를 붙여도 좋다.
☞ Load시의 Index 작성 또는 Create Indext시에 많은 시간 소요
☞ Index를 위해 DASD 용량을 염두에 둘 것
(나) 갱신이라도 Index의 열을 갱신하지 않는 경우는 검색과 같다.
(다) Index의 열을 갱신/삽입/삭제를 하는 경우는, Index의 수를 적게 한다.
☞ 타당한 Index의 수는 Processor 능력과 응답시간 요구의 정도에 의존한다.
☞ ANDB2의 Cost Model로 부하를 추정하는 것이 바람직하다.
(2) COLCARD 가 작은 열의 Index는 피한다.
(가) 중복이 대단히 많은 경우, INSRT/DELETE의 부하가 크다.
(나) 다음의 부등식을 만족시키지 않는 Non Clustered Index는 제외
(3) 일반적으로 40Byte 이상의 Key의 Index는 피한다.
(4) 일반적으로 VARCHAR, VARGRAPHIC과 같은 가변길이 열의 Index는 피한다.
6. 중요한 조회에 대개 개별적으로 추가의 Index를 검토한다.
(1) Index만의 Access로 끝나는 Index를 검토한다.
(2) 단일열/복합열 Index의 추가로 Performance의 향상이 보여 지는가 어떤가를 검토한다.
☞ 모든 Index의 열이 등호 조건으로 사용되어지는 것이 유효하다
(3) 복합 Index의 유효성을 평가한다.
☞ FIRSTKEYCARD가 작은 Index는 피한다.
☞ 복합열 Index가 다른 Index Key를 포함하는 경우에는 짧은 Key의 Index를 우선한다.
※ Index(c1,c2) 와 Index(c1)중에서 Index(c1)을 우선한다.
7. Index의 물리설계를 행한다.
CREATE INDEX 의 Parameter를 결정한다.
(1) 열의 순번
(2) 오름순인가 내림순인가
(3) PCTFREE
☞ Random한 삽입이 있는 경우에 지정한다.
(4) SUBPAGES
☞ 삽입이 있다. -----------> 기본적으로 1 지정
☞ 행의 갱신만 있다. -----------> 1 이상 지정
(5) 값이 단조 증가하는 열을 선두에 갖는 Index는 DASD 용량에 주의힐 필요
예 : 날짜, 채번에 의해 발생한 수주번호와 출하번호
이유 :
① 오래된 Key를 삭제해도, 그 Key 범위가 아니면 Index Page는 재 사용되어지지 않는다.
② 최후의 Index Page 가 꽉차게 되는 때에 Split 이 생기고 Index Page의 반밖에 유효하게 사용되어지지 않는다
8. Index의 유효성을 검증한다.
(1) Data 가 존재하고 DB2가 가동하고 있는 때
☞ EXPLAIN
(2) Data 가 존재하지 않지만 DB2가 가동하고 있는 때
☞ EXPLAIN
(3) DB2가 가동하고 있지 않은 때
☞ ANDB2 APS
▣ FULLKEY CARD(COLCARD)가 작은 경우의 고려사항
◈ FULLKEYCARD가 작은 Non Cluster Index는 일단 사용하지 않는다
예 : Index(성별 [남,녀])
Index(대분류Code [1,2,3])
1. Optimezer가 Access Path 로써 선택하지 않는 것 뿐만 아니라 다른 Access Path가 Performance가 좋기 때문이다.
◈ 할 수 없는 경우의 대응책
Optimezer에게 Non Cluster 이지만 COLCARD 와 FULLKEY CARD가 크다고 인식 시킨다.
◐ 순서
1. 여러 Key 값을 갖는 Dummy 행을 INSERT 시킨다.
2. RUNSTATS Utility 를 실행 시킨다.
3. Dummy 행을 모두 DELETE 한다.
4. BIND 한다.
◐ 고려사항
1. 희망하는 Access Path 가 얻어진 뒤에는 RUNSTATS 와 BIND를 하지 않는다.
2. RUNSTATS Utility 실행의 필요가 생겨난 경우는(재편성해야 옳은가 어떤가를 알기 위해), 이 Index를 RUNSTATS의 대상으로부터 떼내고 상기의 Step을 반복한다.
▣ 정리
◈ 언제 CREATE INDEX 하는가
1. LOAD 전
2. 빈번하게 Access하는 열이 나중에 발견되었을 때
3. 결합열(Join)이 새롭게 발견되었을 때
4. ORDER BY, GROUP BY의 Performance를 개선하고 싶을 때
◈ 언제 DROP INDEX 하는가
1. EXPLAIN에 그 Index가 사용되어지지 않는 것이 판명되었을 때
2. SYSPLANDEP로부터 어떤 Plan도 그 Index를 사용하고 있지 않음을 알았을 때
◈ 언제 RUNSTATS를 실행하는가
1. LOAD 후
2. Reorganization 후
3. 삽입, 삭제, 가변길이의 갱신이 많이 발생한 때
4. CREATE INDEX 후
5. DROP TABLE 한 때
※ 주의사항
☞ Tablespace 와 Index 양쪽을 동시에 조사할 것
☞ RUNSTATS를 실행하여도 BIND하지 않으면 Access Path는 바뀌지 않는다.
☞ Access Path를 바꾸고 싶지 않을 때 처리하지 않는 것도 생각되어 진다.
'DB2' 카테고리의 다른 글
| SQL을 이용한 DB2 XML 데이터 쿼리 (0) | 2007.02.14 |
|---|---|
| DB2 Viper 시작하기 (0) | 2007.02.14 |
| DB2에서 *.sql 파일을 읽어들여 실행하기 (0) | 2006.12.31 |
| Column Size 변경 (0) | 2006.12.19 |
| db2와 oracle에서 프로시저 호출하기 (0) | 2006.06.05 |
