====== 4.1.6 인덱스 선정절차 ====== =====가) 테이블의 엑세스 형태를 최대한 수집===== ==== ▶ 개발 단계에서의 엑세스 형태 수집==== ===1) 반복 수행되는 엑세스 형태를 찾는다. === - 반복 수행되는 엑세스는 자신의 수행속도에 반복횟수를 곱한 만큼의 부하를 가져오므로 수행속도에 미치는 영향이 아주 크다. - Nested Loop Join, Sub-Query, Fetch후 루프 내에서 반복되는 쿼리 ===2) 분포도가 아주 양호한 컬럼들을 발췌하여 엑세스 유형을 조사한다.=== - 테이블에는 아주 양호한 분포도를 가진 컬럼이 있기 마련이다. - 인덱스 컬럼 순서를 정할 때등 여러 경우에서 크게 활용될수 있음 ===3) 자주 넒은 조건이 부여되는 경우를 찾는다.=== - 인덱스는 넓은 범위를 처리하게 될 때 많은 부담을 주게 된다. - 전체 범위 처리 시, 넓은 범위 처리는 인덱스 사용 때 수행속도가 좋지 않을 수 있음. - 처리 범위의 최대 크기와 평균 예상 범위, 자주 사용되는 정렬의 순서, 처리 유형을 수집하면 유용한 자료가 될 것임 ===4) 조건에 자주 사용되는 주요 컬럼들을 추출하여 엑세스 유형을 조사한다.=== - 빈번하게 사용된다는 것은 시스템 전반에 미치는 영향이 그 만큼 크기 때문에 아주 중요하다. ===5) 자주 결합되어 사용되는 컬럼들의 조합 및 정렬되는 순서를 조사한다.=== - 업무적인 측면에서 각 컬럼들간의 상호관계를 잘 파악해 보면 특정 컬럼들끼리 자주 조합하여 사용되는 경합형태를 찾을 수 있다. - 중심이 되는 컬럼을 찾아 이와 연관되는 컬럼들을 검토 ===6) 역순으로 정렬되어 있는 경우를 찾는다.=== - End User는 조건의 범위는 넓게 부여하면서 최근의 데이터만 보려고 하는 경우가 많음. 빠른 응답까지 요구함 ===7) 통계 자료 추출을 위한 엑세스 유형을 조사한다.=== - 통계자료를 추출하는 경우는 대개 범위가 넓다. - 넓은 범위 처리가 대부분 임. 클러스터나 잘 조합된 결합 인덱스 이용하는 것이 좋음. ====▶ 운영 단계에서의 엑세스 형태 수집==== 시점: Test 단계 또는 정상 가동 중인 시스템의 인덱스 교정 시점 ===1) 어플리케이션 소스 코드에서 SQL을 추출하여 분석용 테이블에 보관한다.=== - 기능: 어플리케이션을 분석하여 SQL 문장을 Repository 에 저장. 손쉽게 SQL 문장을 가져올 수 있음. - 한계: 동적 SQL 과 같은 쿼리는 가져오는데 한계가 있으며, 수행 횟수와 같은 자세한 사항은 알 수 없음 ===2) SQL-Trace 파일을 파싱하여 SQL 문장 뿐만 아니라=== ===실행계획, 현재 적용되고 있는 인덱스 , 실행 횟수, 처리 범위등의 매우 상세한 정보 획득할 수 있다.=== - 기능: 전체 커다란 trace 결과에 대해 하나의 상세한 레포트도 얻을 수 있으며 상용 SW에 따라 Advice 역할 까지 해주기도 함. - 한계: 시스템에 큰 부하를 줌. 그러므로 trace 기간은 제한 되어야 하며 그 기간내의 분석 밖에 행할수 없음 ===3) 공유 SQL 영역에서 직접 SQL을 찾아오는 방법은 보다 SQL 수집을 간편하게 한다. === - 기능: 서버에 부담을 덜 주므로 장기간 수집하는 것이 가능하다. - 단점: trace에 비해 정보가 빈약하다. 어떤 어플리케이션에서 수행되었는지와 같은 장기간 수집을 하더라도 여전히 모든 SQL 구문 수집하는 것이 어렵다. ====▶ 수집된 SQL을 테이블 별로 출력하여 엑세스 형태 기록==== - 유사한 형태의 SQL들이 정렬되도록 출력 - SQL문장 별로 정렬하는 것 보다 조건절에 사용된 컬럼별로 정렬하는 것이 보다 유리 =====나) 인덱스 대상 컬럼의 선정 및 분포도 조사===== ====1) 인덱스 대상 컬럼 선정 및 분포도 분석==== ===- 인덱스 대상 컬럼 기준==== - 엑세스 유형에 자주 등장하는 컬럼 - 인덱스의 앞부분에 지정해야 할 컬럼 - 기타 수행속도에 영향을 미칠 것으로 예상되는 컬럼 {{:notepad:테이블조사서.jpg|}} ====2) 현행 인덱스 존재 시, 조사한 엑세스 형태가 어떤 인덱스를 타는지 조사==== ===사용 인덱스 이외에 해당 연산 수행 시 예상되는 범위 또한 조사=== =====다) 특수한 엑세스 형태에 대한 인덱스 선정===== ===반복해서 엑세스 되는 형태(Critical Access Path)를 찾아내는 것이 우선으로 해야할일임=== =====라) 클러스터링 검토===== ===넓은 범위 처리를 자주하는 컬럼에 대해 클러스터링 검토=== ===클러스터링 = 슈퍼스타 영입=== ===인덱스 : 클러스터링 = 안타 : 홈런=== ==='단일테이블클러스터링'을 말함=== -대량의 동일 컬럼값을 가진 로우들을 하나의 클러스터에 저장하여 클러스터링 팩터를 향상 -오로지 검색의 속도향상을 위한 클러스터링이므로 대량의 트랜잭션처리되는 경우는 부하발생 -SALE_DEPT + SALEDATE =====마) 결합 인덱스 구성 및 순서의 결정===== ===앞서 설명한 결합 인덱스 순서 구성 원리에 따라 선정=== - 항상 사용하는가? - 항상 ‘=‘로 사용하는가? - 어느 것이 더 좋은 분포도를 가지는가? - 자주 정렬되는 순서는 무엇인가? - 부가적으로 추가시킬 컬럼은? =====바) 시험 생성 및 테스트===== =====사) 수정이 필요한 애플리케이션 조사 및 선정===== ====== 4.2 클러스터링 형태의 결정 기준 ====== {{:notepad:테이블1.jpg|}} ===테이블 가정=== - TAB3, TAB4는 대용량의 데이터를 가짐 - TAB3 는 매우 빈번하기 COL3의 대량을 처리범위에서 TAB2와 TAB1을 조인 - TAB4 는 COL4로 자주 넓은 범위를 처리 ====== 4.2.1 포괄적인 클러스터링 ====== ===두 테이블이 아닌 그 이상을 결합하는 형태=== ===지나치게 강한 결합은 독립성을 저하=== ===다른 집합과의 유연성 저하=== - TAB1 + TAB2 + TAB3 ====== 4.2.2 부분적인 클러스터링 ====== ===결합도가 매우 강한 테이블만 클러스터링=== ===원래 하나의 테이블이 정규화로 분할된 테이블=== ===다른 집합과는 거의 조인하지 않는 경우=== - TAB1 + TAB2(COL1) - CLUSTER(TAB3) ====== 4.2.3 단일테이블 클러스터링 ====== ===주로 넓은 범위처리가 빈번하게 발생하는 경우=== ===넓은 범위 처리를 스캔방식으로 유도하려는 경우에 적용=== ====== 4.2.4 단위 클러스터의 크기 결정 ====== {{:notepad:테이블2.jpg|}} ====1. 블록당 유효 저장공간 크기 = (블록크기 – 블록헤더 크기) * (100-PCTFREE)/100==== - (8000-100)*0.9=7110 ====2. 로우의 평균 길이 산정==== - 평균로우 길이 100바이트로 가정 ====3. 클러스터키 별 로우 수를 구한다.==== SELECT AVG( CASE WHEN row_cnt > 71 THEN 71 ELSE row_cnt END) average_row_count FROM ( SELECT cluster_key_columns, count(*) row_cnt FROM table_name WHERE sampling_conditions.... GROUP BY cluster_key_columns ); ====4. 단위 클러스터의 크기를 계산하고 결정한다.==== - AVG_ROW_LEN * average_row_count - 7110/2000 =3.5 - 블록에 3개까지 단위클러스터가 존재. ==== *클러스터링 생성절차 예 ( SALES TABLE )*==== ===1. 클러스터 생성=== CREATE CLUSTER sales_cluster (sale_date varchar2(8)) STORAGE ( storage_clause……) PCTFREE 10 PCTUSED 60 SIZE 2000; ===2. 클러스터 인덱스 생성=== CREATE INDEX sales_cluster_idx ON CLUSTER sales_cluster PCTFREE 2 STORAGE (INITIAL 20K NEXT 10K); ===3. 기존테이블 명칭 변경=== RENAME sales TO sales_copy ; ===4. 클러스터내에 테이블 생성=== CREATE TABLE sales ( saleno varchar2(6) not null, sale_dept char(4), sale_date varchar2(8) not null, …………………………………………………………… ) CLUSTER sales_cluster (sale_date) ; ===5. 테이블 내에 데이터 저장=== INSERT INTO sales SELECT * FROM sales_copy WHERE sale_date >= '20010101'; ===6. 기존 테이블 삭제, 새로운 인덱스 생성=== DROP TABLE sales_copy ; CREATE INDEX …………………………………………………; ====== 4.2.5 클러스터 사용을 위한 조치 ====== ====- 클러스터 키 컬럼을 첫번째로 하는 결합 인덱스가 존재하면==== ==== 경우에 따라 클러스터를 사용하지 않고 결합 인덱스를 사용하는 실행계획을 수립==== ====- 클러스터가 반드시 사용되어지기를 원한다면 액세스 경로를 고정시킨다.==== SELECT SUM(count(*)) FROM sales WHERE sale_date between ‘20051201’ and ‘20051225’ AND sael_dept = ‘11200 ====- 클러스터 사용이 유리할 경우 힌트 사용하여 실행계획 고정==== SELECT /*+ CLUSTER(sales) */ SUM(count(*)) FROM sales WHERE sale_date between ‘20051201’ and ‘20051225’ AND sael_dept = ‘11200’