이펙티브 오라클 (2008년)
1-1 인덱스 분리형
- 1) 테이블과 인덱스가 별도로 구분되어 있는 관계형 데이타베이스의 가장 일반적인 저장형태이다.
- 2) 데이터 저장시 테이터 값에 무관하게 임의의 위치에 저장하여 저장에 따른 부담이 최소화 된다. 대신 인덱스의 ROWID에 데이터의 물리적 위치를 저장하여 언제라도 엑세스가 가능하다.
- 3) ROWID는 ObjectNo+DatafileNo+BlockNo+SlotNo로 구성되어 있으며 슬롯번호에는 데이타가 들어 있는 물리적 위치가 저장되어 있다.
- 데이터 Migration,Condensing등으로 인해 데이터의 물리적 위치가 변경되면 슬롯에 있는 위치정보만 바꾸어 주면 되므로 ROWID는 변함이 없다.
1-2 인덱스 일체형
1. 일체형 테이블 생성구문
CREATE TABLE DOCUMENTS (
DOC_ID VARCHAR2(5),
TITLE VARCHAR2(50),
AUTHOR VARCHAR2(20),
CONTENTS VARCHAR2(2000),
STATUS VARCHAR2(2),
CONSTRAINT PK_DOC PRIMARYKEY (DOC_ID)
)
ORGANIZATION INDEX ---(A)
TABLESPACE DATA01 ---(B)
PCTTHRESHOLD 20 ---(C)
INCLUDING CONTENTS ---(D)
OVERFLOW TABLESPACE IDX01 ---(E)
;
- (A) : 일체형 테이블로 생성
- (B) : 데이터가 저장될 테이블 스페이스 지정
- © : 단일 로우의 크기가 (PCTTHRESHOLD/100)*DB_BLOCK_SIZE 보다 크면 INCLUDING 이후의 칼럼들은 오버플로우 영역으로 저장된다. 만일 오버플로우 영역이 지정되어 있지 않으면 저장이 거부된다.
- (D) : 오버플로우 영역에 저장될 칼럼을 지정한다. 만일 INCLUDING 지정하지 않았는데 로우가 크다면 기본키를 제외한 모든 칼럼이 오버플로우 영역에 저장된다. 만일 INCLUDING 이후의 칼럼에 데이터가 입력되지 않으면 모든 칼럼은 인덱스 영역에 저장된다.
- (E) : 로우가 클 경우 INCLUDING 이후의 칼럼이 저장될 테이블 스페이스. 이를 지정하지 않으면 로우가 클 경우 저장이 거부된다.
2. 장점
- 1) 기본키와 기본키가 아닌 데이타가 함께 저장되어 있으며 기본키로 정렬되어 있으며 이는 엄청난 잇점을 준다. (?)이펙티브 540페이지 상단 : 그러나 키 자체는 정렬되지 않은 상태로 저장된다.
- 2) (?)이펙티브 540페이지 상단 : IOT는 두세가지 구현 영역에 있어서 매우 유용하다. 그중 하나가 연관 테이블, 즉 다대다 관계에서 사용되는 테이블이다.
- 3) 인덱스가 없기 때문에 한번의 논리적인 엑세스가 줄어들었을뿐 아니라 인덱스에서 테이블로의 랜덤 엑세스가 없기 때문에 넓은 범위의 검색에 탁월한 효과를 지닌다.
- 4) 리프노드에 기본키를 포함한 모든 데이타가 저장되는 형태이므로 데이터의 수정에 따른 크기 증가시 노드가 분할하게 되어 어떤 키값이 동일한 노드에 지속적으로 저장되어 있을 수 없다. 이러한 한계때문에 영구적인 물리적 주소를 가질 수 없으며 따라서 ROWID가 없다. 이러한 단점을 보완하기 위해 논리적 ROWID라는 것을 지원하지만 이것은 처음 로우가 생성될 때 만들어진 것으로 반드시 그 위치에 로우가 있다는 걸 보장하지 못한다. 그러므로 데이터를 엑세스할 때 논리적 ROWID가 부정확할 확률이 높다고 판단되면 기본키로의 엑세스로 환원하게 된다.
3. 단점
- 1) 추가적인 인덱스가 생성이 불가능하여 오직 기본키로만 엑세스해야 한다는 단점은 인덱스의 추가허용으로 어느정도 극복되었지만 아직도 다양한 엑세스를 해야 하는 경우는 매우 불리하다.
- 2) 저장시 반드시 정해진 위치에 들어가야 하기 때문에 입력 속도가 느리다.
- 3) 입력되는 데이터의 크기가 클 경우 일부 칼럼이 오버플로우 영역에 저장되거나 아니면 입력 자체가 거부될 수 있다는 점을 항상 고려해야 한다. 그러나 입력되는 로우가 항상 일정한 크기로 들어오는 경우는 기대하기는 힘들다.
4. 일체형 구조를 적용하기 좋은 테이블
- 1) 검색용 테이블
- 2) 코드성 테이블
- 3) 대부분 기본키로 검색되는 테이블
- 4) OLAP의 디멘젼 테이블
- 5) 로우의 길이가 짧고 트랜잭션이 빈번하게 발생되지 않는 테이블
2. IOT로 공간절약하기
1) 인덱스 일체형 테이블 생성
CREATE TABLE T (
A INT
,B INT
,PRIMARY KEY (A,B)
)
ORGANIZATION INDEX
2) 보조인덱스 생성
CREATE INDEX T_IDX ON T(B)
3)쿼리문 실행
SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL> SELECT A,B FROM T WHERE B=55;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=26)
1 0 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=1Bytes=26)
4) 해설
B칼럼에 설정된 T_IDX 인덱스가 사용되었고 SELECT절에 A칼럼에 대한 요청이 있슴에도 불구하고
[TABLE ACCESS BY ROWID]에 의한 테이블 엑세스가 나타나지 않고 있다.
이는 인덱스구조의 논리적인 ROWID로 부터 칼럼 A의 값을 얻을 수 있다는 사실을 알고 있기에 테이블 엑세스를 하지 않은 것이다.
이처럼 보조 인덱스를 생성하여 디스크 공간을 절약할 수 있다.
5) 만일 분리형 테이블이었다면 다음과 같이 테이블을 생성해야 테이블 엑세스가 발생하지 않을 것이다.
CREATE TABLE T (
A INT
,B INT
,PRIMARY KEY (A,B)
)
/*T_IDX 인덱스를 B,A 두개의 칼럼에 만든다*/
CREATE INDEX T_IDX ON T(B,A)
SQL> SELECT A,B FROM T WHERE B=55;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)
3. IOT로 입력 데이타 한데 모으기
1.테이블 생성
CREATE TABLE IOT (
USERNAME VARCHAR2(30),
DOCUMENT_NAME VARCHAR2(30),
OTHER_DATA CHAR(100),
CONSTRAINT IOT_PK PRIMARY KEY (USERNAME,DOCUMENT_NAME)
)
ORGANIZATION INDEX
CREATE TABLE HEAP (
USERNAME VARCHAR2(30),
DOCUMENT_NAME VARCHAR2(30),
OTHER_DATA CHAR(100),
CONSTRAINT HEAP_PK PRIMARY KEY (USERNAME,DOCUMENT_NAME)
)
2.데이터입력
BEGIN
FOR I IN 1..100
LOOP
FOR X IN (SELECT USERNAME FROM ALL_USERS)
LOOP
INSERT INTO HEAP (USERNAME,DOCUMENT_NAME,OTHER_DATA)
VALUES
(X.USERNAME,X.USERNAME||'_'||I,'X');
INSERT INTO IOT (USERNAME,DOCUMENT_NAME,OTHER_DATA)
VALUES
(X.USERNAME,X.USERNAME||'_'||I,'X');
END LOOP;
END LOOP;
COMMIT;
END;
/
3.데이터출력
DECLARE
TYPE T_ARRAY IS TABLE OF VARCHAR2(100);
ARRAY1 T_ARRAY;
ARRAY2 T_ARRAY;
ARRAY3 T_ARRAY;
BEGIN
FOR I IN 1..10
LOOP
FOR X IN (SELECT USERNAME FROM ALL_USERS)
LOOP
FOR Y IN (SELECT * FROM HEAP SINGLE_ROW WHERE USERNAME=X.USERNAME)
LOOP
NULL;
END LOOP;
FOR Y IN (SELECT * FROM IOT SINGLE_ROW WHERE USERNAME=X.USERNAME)
LOOP
NULL;
END LOOP;
SELECT * BULK COLLECT INTO ARRAY1,ARRAY2,ARRAY3
FROM HEAP BULK_COLLECT
WHERE USERNAME=X.USERNAME;
SELECT * BULK COLLECT INTO ARRAY1,ARRAY2,ARRAY3
FROM IOT BULK_COLLECT
WHERE USERNAME=X.USERNAME;
END LOOP;
END LOOP;
END;
/
4.TRACE 분석
1) HEAP SINGLE_ROW
SELECT * FROM HEAP SINGLE_ROW WHERE USERNAME=:b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 310 0.01 0.00 0 0 0 0
Fetch 31310 0.32 0.29 0 62610 0 31000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 31621 0.34 0.30 0 62610 0 31000
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS (BY INDEX ROWID) OF 'HEAP'
0 INDEX (RANGE SCAN) OF 'HEAP_PK' (UNIQUE)
2) IOT SINGLE_ROW
SELECT * FROM IOT SINGLE_ROW WHERE USERNAME=:b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 310 0.00 0.00 0 0 0 0
Fetch 31310 0.26 0.26 0 31610 0 31000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 31621 0.26 0.26 0 31610 0 31000
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 INDEX (RANGE SCAN) OF 'IOT_PK' (UNIQUE)
3) HEAP BULK_COLLECT
SELECT * FROM HEAP BULK_COLLECT WHERE USERNAME=:b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 310 0.00 0.00 0 0 0 0
Fetch 310 0.15 0.13 0 19620 0 31000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 621 0.15 0.14 0 19620 0 31000
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS (BY INDEX ROWID) OF 'HEAP'
0 INDEX (RANGE SCAN) OF 'HEAP_PK' (UNIQUE)
4) IOT BULK_COLLECT
SELECT * FROM IOT BULK_COLLECT WHERE USERNAME=:b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 310 0.00 0.00 0 0 0 0
Fetch 310 0.07 0.07 0 1500 0 31000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 621 0.07 0.07 0 1500 0 31000
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 INDEX (RANGE SCAN) OF 'IOT_PK' (UNIQUE)
"구루비 데이터베이스 스터디모임" 에서 2008년에 "이펙티브 오라클" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3559
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.