문서의 선택한 두 판 사이의 차이를 보여줍니다.
양쪽 이전 판 이전 판 다음 판 | 이전 판 | ||
study:oracle:datadb:1week_2 [2010/05/06 15:22] ahmax |
study:oracle:datadb:1week_2 [2010/05/06 17:29] ahmax |
||
---|---|---|---|
줄 35: | 줄 35: | ||
- PGA버퍼에서 찾을 수 없다면 다시 다른 블록을 SGA에 액세스하여 새로운 블럭을 담고 위의 처리를 반복수행한다. | - PGA버퍼에서 찾을 수 없다면 다시 다른 블록을 SGA에 액세스하여 새로운 블럭을 담고 위의 처리를 반복수행한다. | ||
- 리프블록에서 액세스한 로우가 처리범위를 넘으면 처리를 종료한다. | - 리프블록에서 액세스한 로우가 처리범위를 넘으면 처리를 종료한다. | ||
- | + | === ROWID의 구성 | |
- | | + | |
* rowid라는 가상 컬럼에 저장된다. | * rowid라는 가상 컬럼에 저장된다. | ||
* Single row access를 위한 row 고유한 주소 | * Single row access를 위한 row 고유한 주소 | ||
줄 45: | 줄 44: | ||
==== 2.1.2 B-tree 인덱스의 조작(Operation) ==== | ==== 2.1.2 B-tree 인덱스의 조작(Operation) ==== | ||
- | * **인덱스 생성(Creation)** | + | ===가.인덱스 생성(Creation)=== |
{{: | {{: | ||
- 테이블을 액새스하여 정렬을 수행. | - 테이블을 액새스하여 정렬을 수행. | ||
줄 55: | 줄 54: | ||
* 위와 같은 방법으로 인덱스가 저장되기 때문에 인덱스 블럭에 많은 로우를 담게 될 경우 리프 블럭 감소, 브랜치 블럭 증가 둔화(블럭의 깊이 감소) | * 위와 같은 방법으로 인덱스가 저장되기 때문에 인덱스 블럭에 많은 로우를 담게 될 경우 리프 블럭 감소, 브랜치 블럭 증가 둔화(블럭의 깊이 감소) | ||
- | | + | |
- | * 큰 블럭 사이즈(DB_BLOCK_SIZE)를 지정. | + | == 인덱스 블록에 보다 많은 로우를 담기 위해 취할 수 있는방법 == |
- | * PCTFREE를 최소로 정의. | + | * 최대한 인덱스 컬럼의 수를 줄인다. |
- | * 압축을 활용하는 방법 | + | |
+ | | ||
+ | | ||
CREATE INDEX ord_customer_idx | CREATE INDEX ord_customer_idx | ||
줄 65: | 줄 66: | ||
: | : | ||
- | * **인덱스 블럭의 분할(SPILT)** | + | ===나.인덱스 블럭의 분할(SPILT)=== |
* 인덱스 로우는 정렬이 되어 저장 되어야 하는 이유 때문에 이미 생성된 구조에 새로운 로우가 삽입되면 기존의 위치에 파고 들어가는 문제 발생 | * 인덱스 로우는 정렬이 되어 저장 되어야 하는 이유 때문에 이미 생성된 구조에 새로운 로우가 삽입되면 기존의 위치에 파고 들어가는 문제 발생 | ||
{{: | {{: | ||
줄 73: | 줄 74: | ||
: | : | ||
- | * **데이터의 삭제 및 갱신** | + | ===다.데이터의 삭제 및 갱신=== |
* 데이터를 삭제했을 경우 테이블의 로우는 제거되지만 인덱스의 로우는 삭제되었다는 표시(flag)만 추가된다.(저장공간낭비와 스캔시 액세스 블럭 증가) | * 데이터를 삭제했을 경우 테이블의 로우는 제거되지만 인덱스의 로우는 삭제되었다는 표시(flag)만 추가된다.(저장공간낭비와 스캔시 액세스 블럭 증가) | ||
* 한 리프 블럭이 모두 삭제 되었을 경우 브랜치 블럭에 해당 리프 블럭을 가리키는 로우도 삭제 표시가 된다. | * 한 리프 블럭이 모두 삭제 되었을 경우 브랜치 블럭에 해당 리프 블럭을 가리키는 로우도 삭제 표시가 된다. | ||
줄 80: | 줄 81: | ||
* 데이터처리(DML)가 많이 수행되는 테이블은 정기적으로 재생성을 할 필요가 있다. | * 데이터처리(DML)가 많이 수행되는 테이블은 정기적으로 재생성을 할 필요가 있다. | ||
: | : | ||
- | * ** 인덱스를 경유한 검색** | + | ===라.인덱스를 경유한 검색=== |
{{: | {{: | ||
* lmc는 브랜치 블럭의 첫 번째 로우의 값보다 적은 값을 갖는 하위의 블럭의 주소정보(dba: | * lmc는 브랜치 블럭의 첫 번째 로우의 값보다 적은 값을 갖는 하위의 블럭의 주소정보(dba: | ||
- | | + | |
- | - 주어진 값보다 같거나 최소값을 찾는다.(찾는값 >= 인덱스값) | + | 1.루트 블록을 찾는다. |
- | - 찾는 값 < 인덱스 값 (Lmc에 있는 dba로 이동) | + | 2.주어진 값보다 같거나 최소값을 찾는다.(찾는값 >= 인덱스값) |
- | - 찾는 값 = 인덱스 값 (해당 dba로 이동) | + | 찾는값 < 인덱스 값 (Lmc에 있는 dba로 이동) |
- | - 찾는 값 > 인덱스 값 (검색 후 찾는 값 = 인데스 값이면 해당 dba로 이동 그렇지 않으면, 찾는 값 < 인데스 값을 만족하는 인덱스 최소값) | + | 찾는 값 = 인덱스 값 (해당 dba로 이동) |
- | - 리프 블록을 찾을 때까지 ② 의 단계를 반복해서 수행. | + | 찾는 값 > 인덱스 값 |
- | - 리프 블록에서 주어진 값을 가진 키를 찾아 존재하면 ROWID를 이용해 테이블을 액세스하고, | + | (검색 후 찾는 값 = 인데스 값이면 해당 dba로 이동 그렇지 않으면, |
+ | 찾는 값 < 인데스 값을 만족하는 인덱스 최소값) | ||
+ | 3.리프 블록을 찾을 때까지 ② 의 단계를 반복해서 수행. | ||
+ | 4.리프 블록에서 주어진 값을 가진 키를 찾아 존재하면 ROWID를 이용해 테이블을 액세스하고, | ||
+ | 그렇지 않으면 'No Data found' | ||
+ | | ||
+ | | ||
: | : | ||
- | === B-tree인덱스의 문제점 === | + | === 마.B-tree인덱스의 문제점 === |
* B-TREE인덱스에서는 실제 컬럼 값을 인덱스에도 보관하고 있어야 한다는 점이 대용량 데이터를 관리할 때 부담이 된다. | * B-TREE인덱스에서는 실제 컬럼 값을 인덱스에도 보관하고 있어야 한다는 점이 대용량 데이터를 관리할 때 부담이 된다. | ||
* B-TREE인덱스 컬럼값의 분포도가 좋아야 한다는 점 | * B-TREE인덱스 컬럼값의 분포도가 좋아야 한다는 점 | ||
줄 112: | 줄 119: | ||
* 루트 블록이나 브랜치 블록은 B-tree인덱스와 같은 구조로 되어 있으나 리프블록은 비트맵으로 구성되어 있다. | * 루트 블록이나 브랜치 블록은 B-tree인덱스와 같은 구조로 되어 있으나 리프블록은 비트맵으로 구성되어 있다. | ||
{{: | {{: | ||
- | + | {{: | |
- | === 생성절차 === | + | |
- | - 인덱스를 생성하고자 하는 컬럼의 값들을 찾기 위해 테이블 스캔을 한 후 | + | |
- | - bitmap generator에 의해 컬럼값, start rowid, end rowid , bitmap을 갖는 인덱스 엔트리를 생성한다. | + | |
- | - 2단계에서 생성된 Bitmap들을 B-tree구조에 넣기 쉽도록 key값과 start rowid 순으로 정렬한다. | + | |
- | - 마지막 단계에서는 정렬된 인덱스 엔트리들을 단순히 B-tree구조로 삽입한다. | + | |
=== 특성 === | === 특성 === | ||
줄 125: | 줄 127: | ||
* 수정이 빈번하게 발생하는 컬럼은 인덱스의 크기가 크게 증가하고 블록레벨 잠금(Block Level Locking)으로 인해 많은 부하가 유발될 수 있다. | * 수정이 빈번하게 발생하는 컬럼은 인덱스의 크기가 크게 증가하고 블록레벨 잠금(Block Level Locking)으로 인해 많은 부하가 유발될 수 있다. | ||
* 데이터 웨어하우스 업무에 주로 활용된다. | * 데이터 웨어하우스 업무에 주로 활용된다. | ||
+ | |||
+ | ** >> 데이터 웨어하우스 ** [[http:// | ||
+ | |||
+ | === 생성절차 === | ||
+ | - 인덱스를 생성하고자 하는 컬럼의 값들을 찾기 위해 테이블 스캔을 한 후 | ||
+ | - bitmap generator에 의해 컬럼값, start rowid, end rowid , bitmap을 갖는 인덱스 엔트리를 생성한다. | ||
+ | - 2단계에서 생성된 Bitmap들을 B-tree구조에 넣기 쉽도록 key값과 start rowid 순으로 정렬한다. | ||
+ | - 마지막 단계에서는 정렬된 인덱스 엔트리들을 단순히 B-tree구조로 삽입한다. | ||
=== 제한사항 == | === 제한사항 == | ||
줄 145: | 줄 155: | ||
- COL3 < 100 인 비트맵을 읽어 머지(BITMAP MERGE)를 수행하여 하나의 비트맵을 만든다. | - COL3 < 100 인 비트맵을 읽어 머지(BITMAP MERGE)를 수행하여 하나의 비트맵을 만든다. | ||
- **2**와 **3**에서 수행한 결과에 대해 OR연산(BITMAP OR)을 수행하여 조건을 만족하는 최종결과 비트맵을 만들고 ROWID로 변형하여 테이블을 엑세스 한다. | - **2**와 **3**에서 수행한 결과에 대해 OR연산(BITMAP OR)을 수행하여 조건을 만족하는 최종결과 비트맵을 만들고 ROWID로 변형하여 테이블을 엑세스 한다. | ||
- | |||
- | * **제한사항** | ||
- | * 파티션테이블에서 Global Index에는 비트맵인덱스를 만들 수 없다. | ||
- | * 비트맵인덱스는 RBO(Rule Base optimizxer) mode에서는 사용될 수 없다. | ||
===== B-Tree 인덱스와 비트맵(Bitmap)인덱스의 비교 ===== | ===== B-Tree 인덱스와 비트맵(Bitmap)인덱스의 비교 ===== | ||
줄 270: | 줄 276: | ||
| | ||
- | * ** 단 함수내에 참조 테이블이 있을 경우 버전 별로 차이가 있으므로 주의해서 사용해야한다. | + | * ** 단 함수내에 참조 테이블이 있을 경우 버전 별로 차이가 있으므로 주의해서 사용해야한다. |
+ | === 나. 오류데이터의 검색문제를 해결 === | ||
+ | == 대소문자나 공백이 혼재된 컬럼의 검색 == | ||
+ | |||
+ | | ||
+ | |||
+ | == 불필요한 공백을 제거한 후 비교를 해야하는 경우 | ||
+ | |||
+ | | ||
+ | |||
+ | == 접두사를 채워서 검색 == | ||
+ | * 전화번호 앞번호가 018이면 그냥 두고 아니면 016을 붙여서 비교할 때 | ||
+ | |||
+ | | ||
+ | on call_data (decode(substr(call_number, | ||
+ | |||
+ | === 다. 가공처리 결과의 검색 === | ||
+ | == 복잡한 계산결과의 검색 == | ||
+ | * 상품별로 데이터량이 많을 때 주문단가에서 할인금액을 뺀 실제 적용단가에 주문수량을 곱하여 금액별로 100개의 상품을 조회할때 복잡한 계산식을 함수기반인덱스로 만들어 결과값을 논리컬럼으로 저장해두면 힌트에서 인덱스를 사용할 수 있다. | ||
+ | |||
+ | | ||
+ | on order_items(item_cd, | ||
+ | |||
+ | | ||
+ | from order_items x | ||
+ | where item_cd = :b1 | ||
+ | and rownum <= 100 | ||
+ | |||
+ | * 말일, 단가, 율의 검색 : 해당월에 가장 높은 sal_amount가 발생한 건을 찾는 경우 | ||
+ | |||
+ | | ||
+ | |||
+ | * 기간을 검색하는 경우 | ||
+ | |||
+ | | ||
+ | |||
+ | * 길이를 검색하는 경우 . | ||
+ | |||
+ | | ||
+ | |||
+ | === 라. 오브젝트 타입의 인덱스 검색 === | ||
+ | |||
+ | * 육면체(cube)를 오브젝트타입으로 생성하오 이것을 이용하여 부피를 구하는 예 | ||
+ | |||
+ | 길이, | ||
+ | |||
+ | | ||
+ | ( | ||
+ | length number, | ||
+ | width number, | ||
+ | heigth number, | ||
+ | member function volume return number DETERMINISTIC | ||
+ | ); | ||
+ | | ||
+ | create or replace type body cube as | ||
+ | member function volume return number is | ||
+ | begin | ||
+ | | ||
+ | end; | ||
+ | end; | ||
+ | |||
+ | cube유형(TYPE)으로된 CUBE_TAB 테이블을 생성하고, | ||
+ | |||
+ | | ||
+ | | ||
+ | |||
+ | 인덱스를 경유하여 엑세스를 수행 | ||
+ | |||
+ | | ||
+ | |||
+ | === 마. 베타적 관계의 인덱스 검색 === | ||
+ | |||
+ | * 여기서 말하는 배타적(Exclusive)관계에 있는 컬럼이란 데이터 모델링 상에서 서로 다른 서브타입(Subtype)에 있는 컬럼들 중에서 동일한 속성으로 통합할 수 있는 컬럼들의 관계를 말한다. | ||
+ | |||
+ | == 배타적 관계의 유일성 보장 == | ||
+ | |||
+ | * 어떤 배타적 관계에 있는 속성들을 관찰하면 동일한 속성(subtype)으로 볼 수 있으며 이들을 결합했을 경우 반드시 존재해야한다거나 유일해야 한다는 원칙이 있을 수 있다. 이런경우 함수기반 인덱스를 생성하여 처리의 단순화 및 시스템을 통한 제약조건의 검증까지 보장받을 수 있다. | ||
+ | |||
+ | * 배타적 관계에 있는 주민등록번호와 사업자등록번호를 결합하여 유일성을 체크할 수 있을 뿐 아니라 검색의 단순화를 얻을수 있는 예 | ||
+ | |||
+ | | ||
+ | on customers(case when cust_type =1 then resident_id else business_id end); | ||
+ | |||
+ | | ||
+ | where (case when cust_type =1 then resident_id else business_id end) = :b1; | ||
+ | |||
+ | * 특정한 범위의 데이터에 대해서만 유일성을 체크할도록 할 수 있는 사례. | ||
+ | * 예를들어 ' | ||
+ | |||
+ | | ||
+ | case when then ins_type = ' | ||
+ | case when then ins_type = ' | ||
+ | ); | ||
+ | |||
+ | * 이 인덱스는 INS_TYPE =' | ||
+ | * 다음과 같은 입력이 발생하면 에러가 나타단다. | ||
+ | |||
+ | | ||
+ | | ||
+ | |||
+ | 1row created. | ||
+ | |||
+ | | ||
+ | | ||
+ | |||
+ | ERROR at line 1; | ||
+ | | ||
+ | |||
+ | == 배타적관계의 결합인덱스 == | ||
+ | * 어떤 구분값을 기준으로 결합인덱스의 구성이 달라진다면 함수기반인덱스로 해결할 수 있다. | ||
+ | |||
+ | | ||
+ | | ||
+ | case when ord_type=1 then delivery_date else shipping_date end), // 중간 컬럼인덱스 | ||
+ | | ||
+ | ) | ||
+ | |||
+ | |||
+ | |||