대용량데이터베이스 솔루션 요약 작성자: 이 현 석 이 문서는 oracleclub.com 데이터베이스 스터디 모임에서 작성 한 것을 짜깁기 한 것입니다. 이 문서의 출처는 이화식님저 대용량데이터베이스를 참고하였습니다.

제1장 액세스 효율의 향상 1.인덱스의 활용 2.조인의 최적화 3.클러스터링의 활용 4.부분범위 처리 5.다중처리의 활용

1 인덱스의 활용 인덱스의 구조 Index ? Page 4~5 인덱스는 일반 테이블이나 클러스터에서 쓰여지는 선택적인 객체로서, 오라클 데이터베이스 테이블내의 원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조. - 이화식씨 : 옵티마이저가 최적의 처리 경로를 결정하기 위해 사용하는 요소(Factor). - 테이블 로우(Row)에 하나씩 대응되는 별도의 객체(Object). - 인덱스는 생성시킨 컬럼의 값과 테이블 ROW의 ROWID 로 구성되어 있고, 이들간에 서로 정렬(Sort)되어 있다. 동일한 값이 여러 개 존재하면 같은 값의 인덱스 ROW는 다시 ROWID로 정렬되어 저장된다. - 인덱스는 하나의 테이블에 여러 개를 지정할 수 있고, 하나의 컬럼은 여러 개의 인덱스에 포함 될 수도 있다. - 테이블과 무관하게 생성 및 삭제를 독립적으로 할 수 있다. - 자동 인덱스 : 프라이머리 키 또는 UNIQUE 제약조건에 의해 자동적으로 생성되는 인덱스. - 수동 인덱스 : CREATE INDEX 명령을 실행해서 만드는 인덱스.   [인덱스] 컬럼 값의 순서로 정렬되어 있음 [테이블] 컬럼 값의 순서에 관계없이 기록됨 1.B*Tree 방식으로 조건을 만족하는 첫 번째 인덱스 ROW를 찾는다. 2.조건에 해당하는 처리가 끝날 때 까지 차례대로 다음 ROW를 스캔 한다. 3.인덱스 ROW에 있는 ROWID 정보를 이용하여 테이블에 있는 실제 ROW를 랜덤하게 액세스 한다. 4.결국 액세스되는 테이블 ROW의 순서는 인덱스 ROW의 순서와 일치한다. 5.그러므로 인덱스를 이용하여 처리함으로써 정렬작업 없이 동일한 결과를 추출할 수 있다.

1 인덱스의 활용 나. 인덱스 컬럼의 선정 결합 인덱스의 특징 Page 38 – 41 ◈ 결합인덱스 : 하나 이상의 칼럼으로 생성된 인덱스 - 결합 인덱스가 where 조건에 나올 경우 순서에 의해 효율의 차이가 많이 생길 수 있다. → 결합인덱스 선택에서 equal(=)로 나오게 할 수 있느냐가 중요 하다. - 컬럼의 순서를 어떻게 하느냐에 따라 수행 속도에 많은 영향을 미친다. ※ 첫 번째 컬럼이 '='로 사용되지 않았을 때 뒤에 있는 컬럼이 '='로 사용했다 하더라도 처리 범위는 줄어들지 않는다. 그러므로 첫 번째 위치한 컬럼이 '='로 사용되는 것이 아주 중요하다.

항상 사용하는가 ? 항상 '='로 사용되는가 ? 분포도가 좋은 컬럼 우선 SORT 순서는 ? 어떤 컬럼을 추가 ? (후보선수) 1 인덱스의 활용 Page 47 – 48 나. 인덱스 컬럼의 선정 결합인덱스의 컬럼 순서 결정 방법 항상 '='로 사용되는가 ? SORT 순서는 ? 어떤 컬럼을 추가 ? (후보선수) 항상 사용하는가 ? 분포도가 좋은 컬럼 우선

2 조인의 최적화 Page 82~88 2.1 조인효율 향상의 원리 가. 조인순서

2 조인의 최적화 2.1 조인효율 향상의 원리 다. 연결고리 상태 (1) 연결고리 정상 다. 연결고리 상태 Page 82~88 2.1 조인효율 향상의 원리 다. 연결고리 상태 (1) 연결고리 정상 연결고리가 정상인 상태에서는 어느 방향으로 연결작업이 수행되든 간에 발생되는 연결작업은 동일하다. 그러나 처리범위를 줄여주는 테이블이 먼저 처리되면 수행속도가 향상된다. (연결고리가 정상인 경우 수행속도를 좌우하는 것은 연고리가 아니라 먼저 연결되는 테이블의 처리범위이다.) 다. 연결고리 상태 (2) 한쪽 연결고리 이상

2 조인의 최적화 2.4 Nested Loop 조인과 Sort Merge조인 가. Nested Loop 조인 Page 110~120 2.4 Nested Loop 조인과 Sort Merge조인 가. Nested Loop 조인 어떤 테이블의 처리범위를 하나씩 액세스 하면서 그 추출된 값으로 연결할 테이블을 조인하는 방식 나. Sort Merge 조인 양쪽 테이블의 처리범위를 각자 액세스하여 정렬한 결과를 차례로 스캔하면서 연결고리의 조건으로 머지해 가는 방식

4 부분범위처리 부분범위처리의 자격 부분범위처리가 불가능한 경우 부분범위처리로의 유도 논리적으로 전체범위를 읽어 추가적인 가공을 하지 않고도 동일한 결과를 추출할 수 있을 경우 부분범위처리가 불가능한 경우 SELECT ord_dept,ordqty*1000 FROM order WHERE ord_date like ‘9503%’ ORDER BY ord_dept SELECT deptno, empno FROM EMP1 WHERE sal > 1000000 UNION FROM EMP2 WHERE hierdate between ‘01-jan-90’ and sysdate SELECT SUM(ordqty) FROM order WHERE ord_date like ‘9503%’ SELECT ord_dept, COUNT(*) FROM order WHERE ord_date like ‘9503%’ GROUP BY ord_dept 부분범위처리로의 유도 인덱스나 클러스터를 이용한 SORT의 대체 인덱스만으로 액세스 MAX의 처리 (기본키의 마지막 일련번호 찾기) EXISTS를 활용한 부분범위처리 REONWM의 활용 SQL의 이원화

1. MONITOR 2. SQL_TRACE 3. TKPROF 4. EXPLAIN PLAN 5. ANALYZE 제4장 시스템 진단과 튜닝 1. MONITOR 2. SQL_TRACE 3. TKPROF 4. EXPLAIN PLAN 5. ANALYZE 6. UTLBSTAT/UTLESTAT 7. 기타스크립트 8. 사용자스크립트 9. SQL Area튜닝 10. 병렬처리 튜닝

1. 뷰의 실체 2. 뷰의 특징 3. 뷰의 실행계획 수립 원리 4. 뷰의 활용 5. 뷰 파싱타임의 절약 제5장 뷰의 특징과 활용 1. 뷰의 실체 2. 뷰의 특징 3. 뷰의 실행계획 수립 원리 4. 뷰의 활용 5. 뷰 파싱타임의 절약

제5장 뷰의 특징과 활용 5.4 뷰의 활용 가. 보안관리를 위한 뷰 나. 사용편의를 위한 뷰 다. 수행속도 향상을 위한 뷰 보안등급에 따른 컬럼 및 범위선정 결과만 제공하고 알고리즘을 숨기기 위한 뷰 데이터의 변경을 원천적으로 봉쇄하는 뷰 명칭을 감추기 위한 뷰 나. 사용편의를 위한 뷰 검색조건의 단순화를 위한 뷰 컬럼명, 테이블명을 한글화한 뷰 조인문장의 단순화를 위한 뷰 GROUP BY, SUM(DECODE…)처리를 위한 뷰 다. 수행속도 향상을 위한 뷰 GROUP BY 뷰를 이용한 수행속도 향상 클라이언트 툴의 문제 해결을 위한 뷰 악성검색을 방지하기 위한 뷰 힌트를 사용한 뷰 라. 융통성향상을 위한 뷰 데이터 모델의 변경에 따른 독립성을 위한 뷰 업무규칙의 변경에 독립성을 부여하기 위한 뷰 상황에 따라 액세스 경로를 변경시키기 위한 뷰 명칭을 통일하기 위한 뷰 마. SQL기능 향상을 위한 뷰 불규칙적인 정렬을 위한 뷰 GROUP BY 결과를 다시 가공하기 위한 뷰 GROUP BY 결과를 같은 줄에 맞추기 위한 뷰 소계와 내역을 하나의 SQL로 처리하기 위한 뷰 바. 임시작업을 위한 뷰

제6장 데이터 모델링 1. 기본 개념 데이터 모델링 2. 상세 개념 데이터 모델링 3. 데이터베이스 설계

제6장 데이터 모델링 6.1 기본개념 데이터 모델링 가. 엔터티 (ENTITY) 나. 관계 (RELATIONSHIP) 정의: 실체(實體).사람, 위치, 목적물, 개념, 활동, 사건 등. 특징 - ENTITY NAME은 단수형이고 유일하게부여 - 이름만으로도 의미 전달이 되도록 고심하여 명명 - ENTITY 명을 ATTRIBUTE 명과 같게 사용하지 말 것 - 식별자는 현존하지 않더라도 개념적으로는 존재해야 함 - 만약, 유일한 식별자가 없다면 ENTITY가 아님 - ENTITY NAME은 대문자, 크게 표시 - ATTRIBUTE NAME은 소문자, 작게 표시 - ENTITY의 UID가 되는 ATTRIBUTE에는 #*을 표시 두개의 ENTITY나 그 자신과의 특정관계를 양방향으로 표현 현재의 관계나 장래 유용한 관계만 한정적으로 표시 각 방향의 관계에는 - 관계의 명칭(부사형으로) - 선택사양(Optionality) - 관계형태(Degree) 를 표시 다. 속성(Attribute) 라. 식별자 (UNIQUE IDENTIFIER) 엔터티 내에서 관리해야 할 정보들의 항목 엔터티명을 사용해서는 안됨 읽기의 용이성을 고려하여 가능한 복합명사 사용 단 하나의 엔터티에만 속하도록 지정 필수로 존재해야 하는 속성에는 *, 존재하지 않을 수 있는 속성에는 O를 붙여준다. 하나 혹은 그 이상의 속성으로 구성된다. 모든 엔터티는 반드시 식별자를 가져야 한다. 경우에 따라 인위적인 식별자를 생성해야 하는 경우도 있다.(Attificial) * 인위적인 식별자를 생성하는 경우 - 너무 긴 속성을 자주 사용할 때 - 사용자의 편의나 효율성을 위해 분류 - 식별자 막대에 의해 너무 많은 속성으로 구성될 때 - 사용자의 데이터처리에 관계없이 특정하게 부여된 식별자가 내부적으로만 많은 관계를 가지는 경우

제6장 데이터 모델링 6.2 상세개념 데이터 모델링 1. 정규화(Normalization) 작업 가. 제1 정규형 엔터티 내의 모든 속성은 반드시 하나의 값을 가져야 한다. (반복형태가 있어서는 안된다) - 예) 사원테이블(사번, 사원명, 부서코드, 부서명) 나. 제2 정규형 모든 속성은 반드시 식별자 전부에 직접적으로 종속되어야 한다. 예) 학과등록(학번, 코스코드, 평가코드 다. 제3 정규형 식별자가 아닌 모든 속성간에는 서로 종속될 수 없다. 2. M:M 관계의 해소 새로운 교차엔터티(Intersection)를 추가하여 M:1 관계로 바꾼다.