매주 각 스터디 팀원들이 담당 분량을 정리해서 올리고 토론식으로 진행합니다.
스터디 포스팅은 팀원들에의해 이루어지지만 스터디 참여는 사이트 회원 모두가 가능합니다.
진행기간: 2009.07 ~ 2009.10. 종료
1.2 인덱스 일체형 테이블(Inde-Organized Table)
2009.07.06 11:11
테이블과 인덱스가 일체형으로 되어 있다는 것은 인덱스와 다른 일반 컬럼들이 모두 같은 위치에 저장된는 형태를 말한다.
따라서 인덱스만 액세스하면 따로 테이블을 액세스할 필요 없다
일체형 테이블은 처리범위가 넗어지더라도 분리형 테이블보다 부담이 적어진다.
즉, 인덱스 스캔 후 테이블 스캔 시 랜덤액세스가 발생하기 때문이다.
Secondary 인덱스의 생성 | ||
ROWID 로 액세스 | ||
CLuster에 저장 가능 | Cluster에 저장이 불가능 | |
LONG 은 불가능 | ||
- 테이블과 인덱스를 모두 가지고 있는 구조이다. 즉 분리형 테이블 처럼 인덱스를 찾고 rowid를 이용 테이블 액세스가 필요 없다.
- 인덱스를 경유하지 않기 때문에 분리형 테이블에 비해 한번의 논리적 액세스가 줄어든다.
- 인덱스에 데이블데이터도 포함되어 인덱스만 스캔하는 경우 더 많은 블럭을 스캔해야 한다.
- 저장형태는 b-tree와 같은 Leafnode에 데이터를 같이 저장한다.
- 초장기(8i NewFeature) 에는 추가적인 인덱스(Secondary index)를 생성할 수 없었으나 9i 부터 추가적인 인덱스(Secondary index) 생성 가능하다.
- 추가적인 인덱스(Secondary index)의 경우 rowid 대신 기본키(primary key)를 사용해야 한다.
1) 장점
- 인덱스와 데이터가 같이 저장 되는 구조이기 때문에 저장 및 엑세스 효율이 증가한다.
- 랜덤 액세스가 없기 때문에 넘은 범위의 처리에 효과적이다
2) 단점
- 유연하지 못하고 배타적이다.
- 데이터 갱신시 오버플로우 발생
1) 논리적 rowid의 개념
분리형 테이블에서는 인덱스의 분할이 발생하더라도 테이블의 rowid는 변하지 않는데 반해 일체형은 인덱스자신이 테이블이므로 인덱스의 분할에 따라 rowid가 변할 수 있으므로 어떤 키값이 동일한 노드에 지속적으로 저장될 수 없다.
- 키분할후 이 값으로 해당 로우를 찾아가면 원래의 로우는 다른 블록으로 이동하고, 거기에 다른 로우가 존재할 수 있다.
- 그 로우가 존재할 가능성이 높은 주소를 나타내기 때문에 이것을 Physical Guess혹은 Guess라고 한다.
- 데이터를 액세스할 때 값(ROWID)이 부정확한 값이라고 판명되면 그 때는 기본키를 이용한 액세스를 하게 된다(부정확한 값이 높다면 아예 사용되지 않는다.)
- 물리적 위치정보와 기본키가 상호보완작용을 하여 논리적으로 완벽한 ROWID역할을 수행
- Physical Guess가 완벽한 ROWID는 아니지만 극히 일부를 제외하고는 유효하다면 언제나 기본키로 액세스하는 것보다는 훨씬 유리하다.
2) 일체형 테이블을 적용할 수 있는 경우
- 전자 카탈로그나 키워드 검색용 테이블
- 코드성 테이블
- 색인 테이블
- 공간 정보 관리용 테이블
- 대부분 기본키로 검색되는 테이블
- OLAP의 디멘젼 테이블
- 로우의 길이가 비교적 짧고, 트랜젝션이 빈번하게 발생되지 않는 테이블
3) 물리적 위치정보를 사용하지 않는 경우
① Secondery Index를 액세스하여 기본키정보를 얻는다
② 기본키로 데이터블록을 액세스
4) 물리적 위치정보를 사용하는 경우
① 추가적인 인덱스를 액세스하여 물리적 위치정보를 참조한다
② 참조한 물리적 위치정보를 이용하여 데이터블록을 액세스한 후에 비교한다. 이때 기본키 값이 같으면 물리적 위치정보가 유효한 것으로 간주하여 액세스를 종료한다.
③ 만약 유효하지 않다면 다시 기본키로 액세스하여 데이터 블록을 가져온다
1) 일체형 테이블의 부담요소
인덱스와 모든 컬럼이 같은 장소에 저장된다는 것은 저장공간의 분할이 발생한다든지 저장 밀도가 나빠지는 등의 부담이 증가한다
2) 일체형 테이블의 부담을 줄이는 방법
① 같이 적재할 컬럼을 줄인다.
② 오버플로우 영역에 상대적으로 빈번하게 액세스되지 않는 컬럼을 옮겨 둔다.
③ 테이블 생성 시 컬럼의 순서를 잘 결정 해야 한다.(INCLUDING 절 이후의 컬럼
3) 일체형 테이블의 Migration
① 테이블 생성시 include와 pctthreshold를 이용하여 결정된다.
② 일체형 구조의 본체는 인덱스 세그먼트에 저장되며 오버플로우 영역은 테이블세그먼트에 저장된다.
CREATE TABLE documents
(doc_id VARCHAR2(5),
title_name VARCHAR2(150),
author VARCHAR2(20),
contents VARCHAR2(2048),
status VARCHAR2(2),
CONSTRAINT pk_dociot PRIMARY KEY (doc_id) )
ORGANIZATION INDEX ---- ①
TABLESPACE data01 ---- ②
PCTTHRESHOLD 20 ---- ③
INCLUDING contents ---- ④
OVERFLOW TABLESPACE idx01; ---- ⑤
① ORGANIZATION INDEX : 일체형 테이블 생성을 정의하는 키워드
② TABLESPACE : 인덱스영역이 저장될 테이블 스페이스를 지정한다.
STORAGE 파라미터 사용도 가능하다
③ PCTTHRESHOLD : 인덱스 블록 내의 예약된 공간의 비율을 백분율로 지정한다.
단일 로우 크기가 (PCTTHRESHOLD/100)/*DB_BLOCK_SIZE보다 크게 되면 이 범위 이내의 크기 까지 인덱스 영역에 남겨두고 나머지 컬럼 들은 모두 OVERFLOW영역으로 이동된다.
OVERFLOW를 지정하지 않았을 경우 임계값(Threshold)를 초과한 데이터는 입력이 실패한다.
④ INCLUDING : INCLUDING 이후에 선언된 컬럼 들은 오버플로우 영역에 저장된다
만약 INCLUDING이 지정되지 않았을 때 로우의 크기가 PCTTHRESHOLD를 초과하면 오버플로우 영역에 저장된다.
(특이한 점 : INCLUDING절이 적용되는 시점은 처음 로우가 저장될 때만이다. 로우 인서트 시 인클루드에 선언된 컬럼을 NULL로 넣고 이후 UPDATE시 선언된 컬럼에 값을 입력하면 PCTTHRESHOLD를 초과하지 않는 한도내에서 인덱스영역에 저장된다.)
⑤ OVERFLOW TABLESPACE : 지정한 PCTTHRESHOLD값을 초과한 로우의 일부가 저장될 테이블스페이스를 지정
※ 액세스의 효율성 향상
- 일반 테이블에서는 사용자가 체인을 결정할 수 없으며 그 결과를 전략적으로 사용할 수 없다. 그러나 일체형에서는 사용자가 체인을 결정할 수 있으며, 그 결과에 따라 액세스 성능이 달라질 수 있다.
- 그러므로 일체형에서는 단순히 로우가 체인이 되었다라는 의미보다 어떤 모양으로 체인이 되었는지가 중용하다. 이체인은 leafnode 의 저장밀도를 높이기 위한 전략이다.
- 전략적으로 인덱스영역과 오버플로우 영역의 나누어야 하며 가장 좋은 방안은 현재 및 향후의 액세스 패턴을 분석 하는 것이다. 즉 사용되는 SQL을 분석 하는 것이다.
댓글 3
-
tofriend
2009.07.06 20:35
-
wikanet
2009.07.06 21:44
수고 하셨습니다~ ^^
=> 일체형 테이블은 랜덤액세스가 발생하지 않는다는 말씀이시죠?? ^^;
실무만 하다가 이론을 공부하려니 헷갈리네요..ㅎㅎ; -
goldlight111
2009.07.06 22:45
'ㅁ' 아 질문 내일 올려야 겠어요 밤 10시 30 ..
가서 자야지
내일 오징..
'ㅁ' 내일은 'ㅁ' 꼭
저녁밥 먹고
db올인!!
-그냥 끄적이는 글이에요 지금부터 ....공부하다가 나온 정리된 의견 -
블록에 기존 row에 update가 되었는데, 저장할 공간이 없으면 마이그레이션을 하게 되고,
그 빈자리에 데이터가 안들어오면, fragmentation(분열)이 되고, 빈공간에 들어갈만한
데이터가 없고 큰 데이터가 들어와 pctfree의 능선에 도착하게 되면 freelist에 의해
그 블록은 더 이상 데이터가 저장될수 없는 상태는 그로기 상태가 되고. 빈공간에 의해
그블록은 비효율적으로 사용하게 된다..캬..그런데 우리의 dbms가 알아서 응축작업이라는
보일러회사에서 많이들볼법한 Condensing 응축작업을 하여 마치 윈도우의 조각모음으로
row들을 빼곡하게 저장하니..빈공간은 활용할수 있는공간으로 변한다. ㅋㅋ 대단..
그런데 여기서 또 걱정이 생긱는것은 Condensing할수록 부하가 많이 걸리니 이거 또한 문제
그런데 솔직히 저는 얼마나 많은 부하가 생기는지 겪지를 못하니 그냥 그렇구나라고 만 아니 좀아쉽네요.
그런데 또 문제가 있으니 pctfree를 제대로 충분히 안잡는 상태에서 update가 나버리면 큰일,
fragmentation의 증가와 pctfree의 만남은 최악, update가 되는 상황에 어쩔수 없이
이주를해야되는데 이미 pctfree공간이 다 활용되었다면 다른 블록으로 이주해야가는 상황
하나의 row를 찾기 위해 본 slot에 갔지만 다른 블록에 위치해 있는 row를 가리키면.
두번의 block을 읽어야 하는 상황..덜덜 이런걸 방지하기 위해서 pctfree를 충분히 줘야겠구나 생각이 드네요..
row가 엄청 길면 한블록이상을 써야하는 경우 체인..더덜..이건 어쩔수 없죠?
참..한개의 product안에 정말 심오한 것들이 많군요..
고민해본결과 이런 추론이 나왔습니다. 사실이 아닌것에 과감한 지적 부탁드립니다.
그리고 추가적인 설명도 부탁드리면 좋겠습니다.
도대체 부하가 어떻게 얼마나 걸리는지 알수가 없네요 'ㅁ'..
데이타가 얼마에서 부터 버벅거리는지 알고 싶습니다.
올랩은 좀 개념이 아직~~^^